Обсуждение: Transaction Questions

Hello!

    I have observed that there is no way to keep a SAVEPOINT inside a BEGIN; ...
END; block after an error condition. I created the following table for
testing:

xyz=> \d t1
           Table "public.t1"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 c1     | character varying |
 n1     | integer           |
Indexes:
    "t1_c1_idx" UNIQUE, btree (c1)

xyz=>

I then tried the following:

BEGIN;
  INSERT INTO t1 VALUES ('w', 1);
  SAVEPOINT p1;
  INSERT INTO t1 VALUES ('x', 2);
  ROLLBACK TO SAVEPOINT p1;
END;

and that works as expected. However, if I get an error within the transaction
anywhere after the SAVEPOINT command, then the entire transaction fails, and
any changes made prior to the SAVEPOINT are rolled back. For example:

xyz=> TRUNCATE TABLE t1;
TRUNCATE TABLE
xyz=> BEGIN;
BEGIN
xyz=> INSERT INTO t1 VALUES ('w', 1);
INSERT 0 1
xyz=> SAVEPOINT p1;
SAVEPOINT
xyz=> SELECT * FROM t1;
 c1 | n1
----+----
 w  |  1
(1 row)

xyz=> INSERT INTO t1 VALUES ('x', 2);
INSERT 0 1
xyz=> SELECT * FROM t1;
 c1 | n1
----+----
 w  |  1
 x  |  2
(2 rows)

xyz=> SELECT 1 / 0;
ERROR:  division by zero
xyz=> SELECT * FROM t1;
ERROR:  current transaction is aborted, commands ignored until end of
transaction block
xyz=> END;
ROLLBACK
xyz=> SELECT * FROM t1;
 c1 | n1
----+----
(0 rows)

xyz=>

    Is this the way that it is meant to work, or is there something wrong in my
approach? Is it possible to have some statements within a transaction succeed
and be committed to the database while others fail and are rolled back?

--
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.

Richard Kut <rkut@intelerad.com> writes:
> and that works as expected. However, if I get an error within the transaction
> anywhere after the SAVEPOINT command, then the entire transaction fails, and
> any changes made prior to the SAVEPOINT are rolled back.

Only because you deliberately told the system to roll back the entire
transaction.  You want to use ROLLBACK TO SAVEPOINT instead.

            regards, tom lane

Hi Tom!

    Thanks for the quick response. However, the ROLLBACK that you see occurred
because I ended the transaction using END; and not because I explicitly asked
for a ROLLBACK.

On Friday 24 February 2006 11:14, Tom Lane wrote:
> Richard Kut <rkut@intelerad.com> writes:
> > and that works as expected. However, if I get an error within the
> > transaction anywhere after the SAVEPOINT command, then the entire
> > transaction fails, and any changes made prior to the SAVEPOINT are rolled
> > back.
>
> Only because you deliberately told the system to roll back the entire
> transaction.  You want to use ROLLBACK TO SAVEPOINT instead.
>
>             regards, tom lane

--
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.

Richard Kut <rkut@intelerad.com> writes:
>     Thanks for the quick response. However, the ROLLBACK that you see occurred
> because I ended the transaction using END; and not because I explicitly asked
> for a ROLLBACK.

That is a ROLLBACK.  END means "COMMIT if transaction is OK, else ROLLBACK".

            regards, tom lane

Hi Tom!

    Thanks for the clarification. Maybe I am trying to use the wrong mechanism
for what I need to do. Let me explain.

    Our application will try to insert a record into a table, and if the row
already exists, then that row will be updated instead. The application will
do this for more than one record at a time. Here is some pseudo-code to show
you what I mean:

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.With
our previous database server (Sybase), the rollback is not mandatory but the
choice is left to the application programmer.  We have considered using an
existence test before the INSERT but that gives different semantics, as would
some sort of UPSERT (insert/update combo statement)

So,
 - Is this mandatory rollback behavior configurable?
 - If not,  what other programming paradigm is recommended instead?



On Friday 24 February 2006 11:54, Tom Lane wrote:
> Richard Kut <rkut@intelerad.com> writes:
> >     Thanks for the quick response. However, the ROLLBACK that you see
> > occurred because I ended the transaction using END; and not because I
> > explicitly asked for a ROLLBACK.
>
> That is a ROLLBACK.  END means "COMMIT if transaction is OK, else
> ROLLBACK".
>
>             regards, tom lane

--
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.

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

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.

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.

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.

On 2/24/06 3:14 PM, "Richard Kut" <rkut@intelerad.com> wrote:

> 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

Sorry.  Spoke too quickly.  Try "rollback to savepoint"

create table table1 (
    c1 varchar primary key,
    n1 int
);
psql: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"table1_pkey" for table "table1"
CREATE TABLE
BEGIN;
BEGIN
SAVEPOINT p1;
SAVEPOINT
INSERT INTO TABLE1 VALUES ('w',1);
INSERT 0 1
RELEASE SAVEPOINT p1;
RELEASE
SAVEPOINT p2;
SAVEPOINT
INSERT INTO TABLE1 VALUES ('w',2);
psql:13: ERROR:  duplicate key violates unique constraint "table1_pkey"
ROLLBACK TO SAVEPOINT p2;
ROLLBACK
END;
COMMIT
select * from table1;
 c1 | n1
----+----
 w  |  1

Hi Sean,

    Thank you very much! This had the potential to become a major issue until you
volunteered your help. Thank you again!


On Friday 24 February 2006 15:24, Sean Davis wrote:
> Sorry.  Spoke too quickly.  Try "rollback to savepoint"

--
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.