Re: Transaction Questions - Mailing list pgsql-novice

Hi Sean!

    I tried it, but it did not help. Here is the output:

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=>   RELEASE SAVEPOINT p2;
ERROR:  current transaction is aborted, commands ignored until end of
transaction block
xyz=> END;
ROLLBACK
xyz=> SELECT * FROM t1;
 c1 | n1
----+----
(0 rows)

xyz=>


On Friday 24 February 2006 15:08, Sean Davis wrote:
> On 2/24/06 2:47 PM, "Richard Kut" <rkut@intelerad.com> wrote:
> > 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"
>
> You need another RELEASE SAVEPOINT here, I think.
>
> > 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.