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
