Re: Continue transactions after errors in psql

Greg Sabino Mullane wrote:
> > * 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.
>
> Correct. More detail: we release our own temporary savepoint, unless the user
> has successfully implemented their own savepoint. We need to do this so that we
> do not clobber the user's savepoint. The larger problem is that "our" savepoints
> and the user's savepoints tend to clobber each other. The normal flow of things
> is to issue our savepoint, then the user's command, and then check to see if the
> command succcessfully completed, and if we are still in a transaction. If we are
> no longer in a transaction, we do nothing, as it means that our savepoint has been
> destroyed, so we don't need to worry about it. Otherwise, if the command failed,
> we issue a rollback of our savepoint, which is guaranteed to be there because the
> user cannot have removed it, because their command did not succeed. Now the tricky
> part: If the transaction is still active, and the command succeeded, and the command
> was not SAVEPOINT, ROLLBACK TO, or RELEASE, we issue a release of our savepoint,
> which is not strictly necessary, but is a good idea so we don't build up a large
> chunk of old savepoints. Aside: we check if the command they issued was a savepoint-
> manipulating one by not parsing the SQL (yuck) but by simply checking the cmdResult
> string. Although there is no way to tell "RELEASE" from "RELEASE TO" from this check,
> we know it cannot be the former because we are still in a transaction. :) If it was
> one of those three commands, we do not issue a release. If they issued a successful
> release or rollback, then it just clobbered our savepoint, which now no longer exists.
> If it was a savepoint, we cannot release, or we will clobber their savepoint, which
> was created after ours. We could theoretically try and figure out beforehand if
> they are issuing a savepoint command, but we must wrap it anyway in case it fails so
> we can rollback and not have it end the outer transaction. Thus, we create one extra
> savepoint every time the user issues a savepoint. Until they rollback or release, of
> course, in which case they also remove an equal number of our savepoints as their
> savepoints. So it doubles the number of savepoints a user currently has, but this
> is the price we pay for having the feature.

Oh, here's his description.  I updated the patch comments:

+      /*
+       *  Do nothing if they are messing with savepoints themselves:
+       *  If the user did RELEASE or ROLLBACK, our savepoint is gone.
+       *  If they issued a SAVEPOINT, releasing ours would remove theirs.
+       */

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