Re: Continue transactions after errors in psql

Robert Treat wrote:

> 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 :-)

In September 2004 I had already sent a patch to implement this behaviour,
the patch, still in the archives, is here:
http://archives.postgresql.org/pgsql-patches/2004-09/bin00040.bin
(savepoints.patch)

There are some issues it addressed:

Assuming you put this option in your .psqlrc file, you will still probably
not want this to be active when you execute commands from a file
(non-interactive). So pset.notty must be checked.
Again, when using \i, resetting errors seems dangerous. Using \i should also
temporarily disable those savepoints.

The real problem with my patch was, that it did not release the savepoints.
Why? Look at this example (with the current patch reseterrors patch):

template1=# \reseterror
Reset error is on.
template1=# BEGIN;
BEGIN
template1=# SAVEPOINT a;
SAVEPOINT
template1=# CREATE TABLE TEST ( a integer);
CREATE TABLE
template1=# ROLLBACK TO a;
ERROR:  no such savepoint

So to get this right, you have to track savepoints created by the user and
only release psql savepoints when there is no user savepoint "sitting on top
of" your savepoint.

Two ways come to my mind:
1) Parse SQL for savepoint and rollback to and create a stack of all
savepoints. Then you can always release all savepoints as long as they are
your own.
2) Implement a server-side function to get the savepoints from the server
and query that before every release.

What do you think?

Best Regards,
Michael Paesold