Re: A little help with transactions, please

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