Re: savepoint improvements

On 1/22/07, Dawid Kuroczko <qnex42@gmail.com> wrote:
> On 1/22/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > The short version is I would like the ability to run some sql commands
> > > and recover the transaction if an error occurs.
> >
> > I'm getting tired of repeating this, but: neither of you have said
> > anything that doesn't appear to me to be handled by ON_ERROR_ROLLBACK.
> > What exactly is lacking in that feature?
>
> I think the problem is with doing something like this:
>
> BEGIN;
> INSERT INTO foo VALUES ('1'); UPDATE status SET updated=now() WHERE tab='foo';
> INSERT INTO bar VALUES ('2'); UPDATE status SET updated=now() WHERE tab='bar';
> INSERT INTO baz VALUES ('3'); UPDATE status SET updated=now() WHERE tab='baz';
> COMMIT;
>
> This will issue three savepoints (if I understand how things wok correctly),

yes

> one for each INSERT+UPDATE block.  This way eiher both of them succeed
> or fail, within one transaction.

i think so...Martijn said it best: you can 'rollback' to, but you
can't 'commit' to.  The 'commit to' would be the arguably much more
useful way of disposing of a savepoint.  But that should be taken up
with sql standards committee :(.

> One solution would be a psql command which
> would fire given command on error condition, like:

yes, psql can handle this. while (IMO) a hack, it addresses the
shortcoming (scripting) specifically not handled by savepoints..

merlin