Re: savepoint improvements

On 1/22/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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),
one for each INSERT+UPDATE block. This way eiher both of them succeed
or fail, within one transaction.

Now, I think the problem the OP wanted to solve was that keeping command
on one line just to have them "inside" one savepoint, and depending on psql(1)
to issue rollbacks for us. I think OPs idea was to be able to rollback if error
occured:
BEGIN;
SAVEPOINT s1;
INSERT...
UPDATE...
ROLLBACK TO s1 ON ERROR;
INSERT..
UPDATE...
ROLLBACK TO s2 ON ERROR;
UPDATE job SET ts = now(); -- OK
COMMIT; -- notice lack of rollback -- whole transaction will fail on error

One solution would be a psql command which
would fire given command on error condition, like:
BEGIN;
SAVEPOINT s1;
INSERT...
UPDATE...
\on_error ROLLBACK TO s1; INSERT INTO errors ....
SAVEPOINT s2;
....
COMMIT;

Regards,
Dawid