Thread: A little help with transactions, please

Hi all,

This is my first attempt at transactions, and I seem to be missing
something. Briefly, the transaction is this

BEGIN;
    UPDATE table A;
    UPDATE table B;
COMMIT;

I find that the update to table A attempts to produce a duplicate
primary primary key, and synchronisation with the server is lost.

The update to table B then proceeds without the benefit of the
option to rollback the transaction. This seems wrong to me.
I need both updates completed or neither. Have I missed
something?

TIA

Richard A Lough

On Wed, Dec 03, 2003 at 12:16:44 +0000,
  Richard Lough <ralough.ced@dnet.co.uk> wrote:
> Hi all,
>
> This is my first attempt at transactions, and I seem to be missing
> something. Briefly, the transaction is this
>
> BEGIN;
>     UPDATE table A;
>     UPDATE table B;
> COMMIT;
>
> I find that the update to table A attempts to produce a duplicate
> primary primary key, and synchronisation with the server is lost.
>
> The update to table B then proceeds without the benefit of the
> option to rollback the transaction. This seems wrong to me.
> I need both updates completed or neither. Have I missed
> something?

I don't think it is normal for failed update statements to cause loss
of synchronization with the server. That can happen with copy statements
(though I think the new protocol available with 7.4 prevents this).
The second update statement should fail since you will be in an aborted
transaction. (Currently there isn't a way for an application to handle
errors and allow the transaction to continue after a failure.)

Bruno Wolff III <bruno@wolff.to> writes:
>   Richard Lough <ralough.ced@dnet.co.uk> wrote:
>> I find that the update to table A attempts to produce a duplicate
>> primary primary key, and synchronisation with the server is lost.

> I don't think it is normal for failed update statements to cause loss
> of synchronization with the server.

It is not.  And if that is what is happening, how does the second update
"proceed" at all?  I suspect client-side programming error, but there's
not enough info here to diagnose it.

            regards, tom lane