Richard Kut <rkut@intelerad.com> writes:
> BEGIN
> INSERT
> OR UPDATE
> INSERT
> OR UPDATE
> COMMIT
> Suppose the second INSERT fails with a duplicate key, we cannot do the
> update (or get the previous INSERT) because the ROLLBACK is mandatory.
No it isn't. You say SAVEPOINT, then do the INSERT, then say either
RELEASE SAVEPOINT if the insert succeeded, or ROLLBACK TO SAVEPOINT
if the insert failed. (RELEASE is actually optional here, but might
make things a bit more transparent.) Then you go on with your
transaction. The problem is that you are using transaction-ending
commands where you should be using savepoint-ending commands.
regards, tom lane