Re: Transaction Questions

On 2/24/06 2:47 PM, "Richard Kut" <rkut(at)intelerad(dot)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.