Re: Continue transactions after errors in psql

Michael Paesold wrote:
> Greg Sabino Mullane wrote:
>
> > Finally had a chance to sit down at look at this afresh, and I'm
> > pretty sure I've got all the kinks worked out this time. Apologies
> > for not attaching, but my mail system is not working well enough
> > at the moment. So, please try to break this patch:
> >
> > http://www.gtsm.com/pg/psql_error_recovery.diff
>
>
> Some suggestions in random order:
>
> * I think you should use PSQLexec instead of using PQexec directly. PSQLexec
> is used by all \-commands and prints out queries with -E, which is very
> helpful for debugging.
>
> -E display queries that internal commands generate

It is now \set ON_ERROR_ROLLBACK, and PQexec seems right for that.
Also, this isn't something like \d where anyone would want to see the
queries, I think.

>
> * You do not check for the server version before activating \reseterror.
> -> use PQserverVersion() to check for >= 80000

Added. Patch just posted.

> * Perhaps the name should be \reseterrors (plural)? Just my personal opinion
> though.

Changed, as you see above.

> * If I read the code correctly, you now don't destroy user savepoints
> anymore, but on the other hand, you do not release the psql savepoint after
> a user-defined savepoint is released. In other words, each time a user
> creates a savepoint, one psql savepoint is left on the subxact stack. I
> don't know if this is a real problem, though.

Interesting. I thought this would fail, but it doesn't:

test=> \set ON_ERROR_ROLLBACK on
test=> begin;
BEGIN
test=> savepoint user1;
SAVEPOINT
test=> lkjasdfsadf;
ERROR: syntax error at or near "lkjasdfsadf" at character 1
LINE 1: lkjasdfsadf;
^
test=> rollback to savepoint user1;
ROLLBACK

and I think this is the reason:

test=> BEGIN;
BEGIN
test=> SAVEPOINT psql1;
SAVEPOINT
test=> SAVEPOINT user1;
SAVEPOINT
test=> SAVEPOINT psql1;
SAVEPOINT
test=> lkjasd;
ERROR: syntax error at or near "lkjasd" at character 1
LINE 1: lkjasd;
^
test=> ROLLBACK TO psql1;
ROLLBACK
test=> ROLLBACK TO user1;
ROLLBACK

What Greg's code does, effectively, is to move the savepoint down below
the SAVEPOINt/RELEASE/ROLLBACK so it doesn't discard the user command.
Nice trick:

+ /*
+ * Do nothing if they are messing with savepoints themselves:
+ * doing otherwise would cause us to remove their savepoint,
+ * or have us rollback our savepoint they have just removed
+ */
+ if (strcmp(PQcmdStatus(results), "SAVEPOINT") == 0 ||
+ strcmp(PQcmdStatus(results), "RELEASE") == 0 ||
+ strcmp(PQcmdStatus(results), "ROLLBACK") ==0)
+ results = NULL;

> * You have not yet implemented a way to savely put \reseterror in .psqlrc. I
> previously suggested an AUTO setting (additional to ON/OFF) that disables
> \reseterror when reading from a non-tty. So putting \reseterror AUTO in
> .psqlrc would be save.

Good question, or rather, should ON_ERROR_ROLLBACK have an effect when
commands come from a file? There is no way to test for the error in
psql so it seems you would never want the transaction to continue after
an error. I am inclined to make ON_ERROR_ROLLBACK work only for
interactive sessions, just like ON_ERROR_STOP works only for
non-interactive sessions.

Comments?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073