Re: [HACKERS] Continue transactions after errors in psql
От | Bruce Momjian |
---|---|
Тема | Re: [HACKERS] Continue transactions after errors in psql |
Дата | |
Msg-id | 200504252128.j3PLSQs23644@candle.pha.pa.us обсуждение исходный текст |
Список | pgsql-patches |
Bruce Momjian wrote: > Greg Sabino Mullane wrote: > > > The SQL-Standard itself says that errors inside transactions should only > > > rollback the last statement, if possible. So why is that not implemented in > > > PostgreSQL? What I read from past discussions here, is because it's just > > > unsave and will lead to data-garbage if you aren't very careful. > > > > That's a good point: if that is indeed what the standard says, we should > > probably see about following it. Rolling back to the last savepoint seems > > a reasonable behavior to me. > > The question is what to make the default: > > o disable it by default for all sessions (current patch) > o enable it by default only for interactive sessions, like AUTOCOMMIT > o enable it by default for all sessions (breaks too many apps) > o add a third mode called 'ttyonly' and figure out a default Based on the comments I received, and the mention that ignoring errors is part of the SQL standard, I chose the second option, patch attached: $ psql test Welcome to psql 8.1devel, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit test=> BEGIN; BEGIN test=> asdf; ERROR: syntax error at or near "asdf" at character 1 LINE 1: asdf; ^ test=> SELECT 1; ?column? ---------- 1 (1 row) test=> COMMIT; COMMIT Can someone confirm that this is the way Oracle works as well? I checked on IRC and isql does it. I am uncertain how applications behave. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: doc/src/sgml/ref/psql-ref.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v retrieving revision 1.134 diff -c -c -r1.134 psql-ref.sgml *** doc/src/sgml/ref/psql-ref.sgml 14 Mar 2005 06:19:01 -0000 1.134 --- doc/src/sgml/ref/psql-ref.sgml 25 Apr 2005 20:01:05 -0000 *************** *** 2050,2055 **** --- 2050,2075 ---- </varlistentry> <varlistentry> + <indexterm> + <primary>rollback</primary> + <secondary>psql</secondary> + </indexterm> + <term><varname>ON_ERROR_ROLLBACK</varname></term> + <listitem> + <para> + When <literal>on</> (the default), in interactive mode, + ignore errors generated by commands in a transaction block, + rather than aborting the transaction. Ignoring errors never + happens in non-interactive mode or if the value is + <literal>off</>. The on_error_rollback-on mode works by issuing + an implicit <command>SAVEPONT</> for you, just before each + command that is in a transaction block, and rolls back to the + savepoint on error. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><varname>ON_ERROR_STOP</varname></term> <listitem> <para> Index: src/bin/psql/common.c =================================================================== RCS file: /cvsroot/pgsql/src/bin/psql/common.c,v retrieving revision 1.96 diff -c -c -r1.96 common.c *** src/bin/psql/common.c 22 Feb 2005 04:40:52 -0000 1.96 --- src/bin/psql/common.c 25 Apr 2005 20:01:08 -0000 *************** *** 941,951 **** bool SendQuery(const char *query) { ! PGresult *results; ! TimevalStruct before, ! after; ! bool OK; ! if (!pset.db) { psql_error("You are currently not connected to a database.\n"); --- 941,952 ---- bool SendQuery(const char *query) { ! PGresult *results; ! TimevalStruct before, after; ! bool OK, on_error_rollback_savepoint = false; ! PGTransactionStatusType transaction_status; ! static bool on_error_rollback_warning = false; ! if (!pset.db) { psql_error("You are currently not connected to a database.\n"); *************** *** 973,979 **** SetCancelConn(); ! if (PQtransactionStatus(pset.db) == PQTRANS_IDLE && !GetVariableBool(pset.vars, "AUTOCOMMIT") && !command_no_begin(query)) { --- 974,982 ---- SetCancelConn(); ! transaction_status = PQtransactionStatus(pset.db); ! ! if (transaction_status == PQTRANS_IDLE && !GetVariableBool(pset.vars, "AUTOCOMMIT") && !command_no_begin(query)) { *************** *** 987,992 **** --- 990,1019 ---- } PQclear(results); } + else if (transaction_status == PQTRANS_INTRANS && + pset.cur_cmd_interactive && + GetVariableBool(pset.vars, "ON_ERROR_ROLLBACK")) + { + if (on_error_rollback_warning == false && pset.sversion < 80000) + { + fprintf(stderr, _("The server version (%d) does not support savepoints for ON_ERROR_ROLLBACK.\n"), + pset.sversion); + on_error_rollback_warning = true; + } + else + { + results = PQexec(pset.db, "SAVEPOINT pg_psql_temporary_savepoint"); + if (PQresultStatus(results) != PGRES_COMMAND_OK) + { + psql_error("%s", PQerrorMessage(pset.db)); + PQclear(results); + ResetCancelConn(); + return false; + } + PQclear(results); + on_error_rollback_savepoint = true; + } + } if (pset.timing) GETTIMEOFDAY(&before); *************** *** 1005,1010 **** --- 1032,1072 ---- PQclear(results); + /* If we made a temporary savepoint, possibly release/rollback */ + if (on_error_rollback_savepoint) + { + transaction_status = PQtransactionStatus(pset.db); + + /* We always rollback on an error */ + if (transaction_status == PQTRANS_INERROR) + results = PQexec(pset.db, "ROLLBACK TO pg_psql_temporary_savepoint"); + /* If they are no longer in a transaction, then do nothing */ + else if (transaction_status != PQTRANS_INTRANS) + results = NULL; + else + { + /* + * Do nothing if they are messing with savepoints themselves: + * If the user did RELEASE or ROLLBACK, our savepoint is gone. + * If they issued a SAVEPOINT, releasing ours would remove theirs. + */ + if (strcmp(PQcmdStatus(results), "SAVEPOINT") == 0 || + strcmp(PQcmdStatus(results), "RELEASE") == 0 || + strcmp(PQcmdStatus(results), "ROLLBACK") ==0) + results = NULL; + else + results = PQexec(pset.db, "RELEASE pg_psql_temporary_savepoint"); + } + if (PQresultStatus(results) != PGRES_COMMAND_OK) + { + psql_error("%s", PQerrorMessage(pset.db)); + PQclear(results); + ResetCancelConn(); + return false; + } + PQclear(results); + } + /* Possible microtiming output */ if (OK && pset.timing) printf(_("Time: %.3f ms\n"), DIFF_MSEC(&after, &before)); Index: src/bin/psql/startup.c =================================================================== RCS file: /cvsroot/pgsql/src/bin/psql/startup.c,v retrieving revision 1.113 diff -c -c -r1.113 startup.c *** src/bin/psql/startup.c 22 Feb 2005 04:40:58 -0000 1.113 --- src/bin/psql/startup.c 25 Apr 2005 20:01:11 -0000 *************** *** 149,154 **** --- 149,155 ---- /* Default values for variables */ SetVariableBool(pset.vars, "AUTOCOMMIT"); + SetVariableBool(pset.vars, "ON_ERROR_ROLLBACK"); SetVariable(pset.vars, "VERBOSITY", "default"); SetVariable(pset.vars, "PROMPT1", DEFAULT_PROMPT1); SetVariable(pset.vars, "PROMPT2", DEFAULT_PROMPT2);
В списке pgsql-patches по дате отправления:
Следующее
От: Bruce MomjianДата:
Сообщение: Re: [HACKERS] Continue transactions after errors in psql