-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
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. :)
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200501252203
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFB9wlpvJuQZxSWSsgRAsAzAKCxQ/JtR6/RXgV39uDTm9FIxCIp8QCeKC6T
2l10ef5DHkmFC2dSMQLNHjg=
=HKv9
-----END PGP SIGNATURE-----
Index: doc/src/sgml/ref/psql-ref.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.130
diff -c -r1.130 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml 14 Jan 2005 00:24:23 -0000 1.130
--- doc/src/sgml/ref/psql-ref.sgml 26 Jan 2005 02:57:05 -0000
***************
*** 1604,1609 ****
--- 1604,1619 ----
<varlistentry>
+ <term><literal>\reseterror</literal></term>
+ <listitem>
+ <para>
+ Toggles allowing transactions to continue after errors.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
+ <varlistentry>
<term><literal>\s [ <replaceable class="parameter">filename</replaceable> ]</literal></term>
<listitem>
<para>
Index: src/bin/psql/command.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.139
diff -c -r1.139 command.c
*** src/bin/psql/command.c 1 Jan 2005 05:43:08 -0000 1.139
--- src/bin/psql/command.c 26 Jan 2005 02:57:05 -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: src/bin/psql/common.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/common.c,v
retrieving revision 1.95
diff -c -r1.95 common.c
*** src/bin/psql/common.c 1 Jan 2005 05:43:08 -0000 1.95
--- src/bin/psql/common.c 26 Jan 2005 02:57:05 -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,1041 ----
/* 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);
! res = PQexec(pset.db, PQTRANS_INERROR==tstatus ?
! "ROLLBACK TO psql_savepoint" : "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: src/bin/psql/help.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/help.c,v
retrieving revision 1.100
diff -c -r1.100 help.c
*** src/bin/psql/help.c 8 Jan 2005 22:51:13 -0000 1.100
--- src/bin/psql/help.c 26 Jan 2005 02:57:05 -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: src/bin/psql/settings.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/settings.h,v
retrieving revision 1.23
diff -c -r1.23 settings.h
*** src/bin/psql/settings.h 1 Jan 2005 05:43:08 -0000 1.23
--- src/bin/psql/settings.h 26 Jan 2005 02:57: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: src/bin/psql/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
*** src/bin/psql/tab-complete.c 23 Jan 2005 15:58:50 -0000 1.121
--- src/bin/psql/tab-complete.c 26 Jan 2005 02:57:05 -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
