Hi Tom!
> > 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.
I tried what you suggested, and here are the results:
xyz=> TRUNCATE TABLE t1;
TRUNCATE TABLE
xyz=> BEGIN;
BEGIN
xyz=> SAVEPOINT p1;
SAVEPOINT
xyz=> INSERT INTO t1 VALUES ('w', 1);
INSERT 0 1
xyz=> RELEASE SAVEPOINT p1;
RELEASE
xyz=> SELECT * FROM t1;
c1 | n1
----+----
w | 1
(1 row)
xyz=> SAVEPOINT p2;
SAVEPOINT
xyz=> INSERT INTO t1 VALUES ('w', 1);
ERROR: duplicate key violates unique constraint "t1_c1_idx"
xyz=> END;
ROLLBACK
xyz=> SELECT * FROM t1;
c1 | n1
----+----
(0 rows)
xyz=>
> The problem is that you are using transaction-ending
> commands where you should be using savepoint-ending commands.
As far as I know, transaction-ending commands are ROLLBACK, ABORT, and
COMMIT. I do not know of any others, and I have no idea what you mean about
savepoint-ending. Please explain.
--
Regards,
Richard Kut
Database Administrator
Research & Development
Intelerad Medical Systems Inc.
460 Ste-Catherine West, Suite 210
Montreal, Quebec, Canada H3B 1A7
Tel: 514.931.6222 x7733
Fax: 514.931.4653
rkut@intelerad.com
www.intelerad.com
This email or any attachments may contain confidential or legally
privileged information intended for the sole use of the addressees. Any
use, redistribution, disclosure, or reproduction of this information,
except as intended, is prohibited. If you received this
email in error, please notify the sender and remove all copies of the
message, including any attachments.