On Tuesday 25 January 2005 22:07, Greg Sabino Mullane wrote: > Attached is a patch that takes advantage of savepoints to enable > transactions to continue even after errors in psql. The name of it > is \reseterror, and it is off by default. It's backwards compatible, > and allows things like this to work on 8.0 and up servers: > > \reseterror > BEGIN; > DELETE FROM foobar; > INSERT INTO foobar(a) VALUES(1); > ISNER INTO foobar(a) VALUES(2); > INSERT INTO foobar(a) VALUES(3); > COMMIT; > > Doing a SELECT(a) FROM foobar will show two values, 1 and 3. This > is a great help for those of us that tend to type typos into our > psql session, and end up cursing as we have to restart our current > transaction. :)
I've been testing this patch and found the following bug: test=# \reseterror Reset error is on. test=# begin; BEGIN test=# select * from t; c --- 1 (1 row) test=# delete from t; DELETE 1 test=# select * from tt; ERROR: relation "tt" does not exist ERROR: relation "tt" does not exist test=# select * from t; c --- (0 rows) test=# commit; COMMIT ERROR: RELEASE SAVEPOINT may only be used in transaction blocks ERROR: RELEASE SAVEPOINT may only be used in transaction blocks I've attached a revised patch which fixes the problem, however I'm sure there is a better way. Thanks to Neil for putting up with me on irc :-) -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Index: command.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/bin/psql/command.c,v retrieving revision 1.139 diff -c -r1.139 command.c *** command.c 1 Jan 2005 05:43:08 -0000 1.139 --- command.c 28 Jan 2005 06:42:03 -0000 *************** *** 646,651 **** --- 646,672 ---- puts(gettext("Query buffer reset (cleared).")); } + /* \reseterror -- use savepoints to make transaction errors recoverable */ + else if (strcmp(cmd, "reseterror") == 0) + { + if (pset.sversion < 80000) + { + printf(gettext("The server version (%d) does not support savepoints.\n"), + pset.sversion); + } + else + { + pset.reseterror = !pset.reseterror; + if (!quiet) + { + if (pset.reseterror) + puts(gettext("Reset error is on.")); + else + puts(gettext("Reset error is off.")); + } + } + } + /* \s save history in a file or show it on the screen */ else if (strcmp(cmd, "s") == 0) { Index: common.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/bin/psql/common.c,v retrieving revision 1.95 diff -c -r1.95 common.c *** common.c 1 Jan 2005 05:43:08 -0000 1.95 --- common.c 28 Jan 2005 06:42:03 -0000 *************** *** 941,950 **** bool SendQuery(const char *query) { ! PGresult *results; TimevalStruct before, after; bool OK; if (!pset.db) { --- 941,951 ---- bool SendQuery(const char *query) { ! PGresult *results, *res; TimevalStruct before, after; bool OK; + PGTransactionStatusType tstatus; if (!pset.db) { *************** *** 973,979 **** SetCancelConn(); ! if (PQtransactionStatus(pset.db) == PQTRANS_IDLE && !GetVariableBool(pset.vars, "AUTOCOMMIT") && !command_no_begin(query)) { --- 974,982 ---- SetCancelConn(); ! tstatus = PQtransactionStatus(pset.db); ! ! if (PQTRANS_IDLE == tstatus && !GetVariableBool(pset.vars, "AUTOCOMMIT") && !command_no_begin(query)) { *************** *** 987,992 **** --- 990,1010 ---- } PQclear(results); } + else { + /* If we are in error recovery mode and inside a transaction, + possibly issue a temporary savepoint */ + if (PQTRANS_INTRANS==tstatus && pset.reseterror) { + res = PQexec(pset.db, "SAVEPOINT psql_savepoint"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) + { + psql_error("%s", PQerrorMessage(pset.db)); + PQclear(res); + ResetCancelConn(); + return false; + } + PQclear(res); + } + } if (pset.timing) GETTIMEOFDAY(&before); *************** *** 1001,1008 **** /* but printing results isn't: */ if (OK) ! OK = PrintQueryResults(results); ! PQclear(results); /* Possible microtiming output */ --- 1019,1049 ---- /* but printing results isn't: */ if (OK) ! OK = PrintQueryResults(results); ! ! /* If in error recovery mode, release the savepoint */ ! ! if (PQTRANS_INTRANS==tstatus && pset.reseterror) { ! tstatus = PQtransactionStatus(pset.db); ! ! if (PQTRANS_INERROR==tstatus) ! res = PQexec(pset.db, "ROLLBACK TO psql_savepoint"); ! else if (PQTRANS_IDLE==tstatus) ! /* COMMITing leaves us in PQTRANS_IDLE so we can't release the save point here */ ! res = PQexec(pset.db, "SELECT 1"); ! else ! res = PQexec(pset.db, "RELEASE psql_savepoint"); ! ! if (PQresultStatus(res) != PGRES_COMMAND_OK) ! { ! psql_error("%s", PQerrorMessage(pset.db)); ! PQclear(res); ! ResetCancelConn(); ! return false; ! } ! PQclear(res); ! } ! PQclear(results); /* Possible microtiming output */ Index: help.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/bin/psql/help.c,v retrieving revision 1.100 diff -c -r1.100 help.c *** help.c 8 Jan 2005 22:51:13 -0000 1.100 --- help.c 28 Jan 2005 06:42:04 -0000 *************** *** 182,187 **** --- 182,189 ---- " show or set client encoding\n")); fprintf(output, _(" \\h [NAME] help on syntax of SQL commands, * for all commands\n")); fprintf(output, _(" \\q quit psql\n")); + fprintf(output, _(" \\reseterror toggle resetting of errors within transactions (currently %s)\n"), + ON(pset.reseterror)); fprintf(output, _(" \\set [NAME [VALUE]]\n" " set internal variable, or list all if no parameters\n")); fprintf(output, _(" \\timing toggle timing of commands (currently %s)\n"), Index: settings.h =================================================================== RCS file: /projects/cvsroot/pgsql/src/bin/psql/settings.h,v retrieving revision 1.23 diff -c -r1.23 settings.h *** settings.h 1 Jan 2005 05:43:08 -0000 1.23 --- settings.h 28 Jan 2005 06:42:05 -0000 *************** *** 52,57 **** --- 52,58 ---- unsigned lineno; /* also for error reporting */ bool timing; /* enable timing of all queries */ + bool reseterror; /* for error recovery mode */ PGVerbosity verbosity; /* current error verbosity level */ } PsqlSettings; Index: tab-complete.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/bin/psql/tab-complete.c,v retrieving revision 1.121 diff -c -r1.121 tab-complete.c *** tab-complete.c 23 Jan 2005 15:58:50 -0000 1.121 --- tab-complete.c 28 Jan 2005 06:42:07 -0000 *************** *** 615,622 **** "\\e", "\\echo", "\\encoding", "\\f", "\\g", "\\h", "\\help", "\\H", "\\i", "\\l", "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink", ! "\\o", "\\p", "\\pset", "\\q", "\\qecho", "\\r", "\\set", "\\t", "\\T", ! "\\timing", "\\unset", "\\x", "\\w", "\\z", "\\!", NULL }; (void) end; /* not used */ --- 615,622 ---- "\\e", "\\echo", "\\encoding", "\\f", "\\g", "\\h", "\\help", "\\H", "\\i", "\\l", "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink", ! "\\o", "\\p", "\\pset", "\\q", "\\qecho", "\\r", "\\reseterror", "\\set", ! "\\t", "\\T", "\\timing", "\\unset", "\\x", "\\w", "\\z", "\\!", NULL }; (void) end; /* not used */
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match