Re: savepoint improvements

On 1/22/07, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> Could you post an example, just so we're all clear what the problems
> are? I thought I understood what you are requesting; I may not.

ok,

The short version is I would like the ability to run some sql commands
and recover the transaction if an error occurs.

We have the ability to do this with savepoint...rollback to
savepoint...but these are not useful without introducing an external
language (c,perl) that can catch the errors and do a rollback to a
savepoint conditionally on the sql error state.

How would this be useful?
Well when I update production systems I often do this from a master
script that loads smaller scripts from another place:

-- update_production.sql
begin;
\i update_foo.sql
\i update_bar.sql
commit;

any error updating foo or bar will blow up the whole thing. Maybe
this is desirable, but it is often nice to be able to do some error
handling here. In the pre-savepoint NT implementation I could:

-- update_production.sql
begin;

begin;
insert into log values ('foo');
\i update_foo.sql
commit;

begin;
insert into log values ('bar');
\i update_bar.sql
commit;

commit;

In between the inner transactions I could check 'log' to see if
everything went through and take appropriate action. Now client
applications have the luxury of being able to check the return code of
the query execution call, but SQL only scripts can't.

This would be perfectly acceptable:

-- update_production.sql
begin;

savepoint foo;
\i update_foo.sql
rollback to savepoint foo [if I failed only];

savepoint bar;
\i update_bar.sql
rollback to savepoint foo [if I failed only];

commit;

This would be just great for scripts but would also help client side
programming a bit by introducing more flexible error handling
behaviors without having to handle things via the returned sql error
code. The on errors bit I was talking about earlier is just syntax
sugar but the critical part is being able to recover transactions
partially without external handler...

merlin