On 1/22/07, Dawid Kuroczko <[EMAIL PROTECTED]> wrote:
On 1/22/07, Tom Lane <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
