serialization errors

Hello,

Let us suppose I have a table like this:
    create sequence seq_foo;
    create table foo (
        bar  int primary key default nextval('seq_foo'),
        name varchar(32),
        unique(name)
    );

I need to have multiple clients update this table concurrently.

Below are two "scripts", one for client A, one for client B:

Time:  Client A
1.     BEGIN;
2.     SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3.     SELECT * FROM foo;  -- each get their own db 'copy'/version
4.     INSERT INTO foo(name) VALUES('test');
5.     [no-op]
6.     COMMIT;
7.     [no-op]

Time:  Client B
1.     BEGIN;
2.     SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3.     SELECT * FROM foo;  -- each get their own db 'copy'/version
4.     [no-op]
5.     INSERT INTO foo(name) VALUES('test');
6.     [paused waitting for insert to complete]
7.     ERROR:  Cannot insert a duplicate key into unique index foo_name_key

The documentation about concurrency control / serializable isolation level
indicates that if there is a concurrent conflicting update, then I would
receive the following error:
    ERROR:  Can't serialize access due to concurrent update
( The documentation is found here:
    http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/mvcc.html )

However, as the scripts above demonstrate, one can receive other errors.

So, assuming I mis-understand the documentation, and that mutliple errors can
legally occur, how can I detect if an error received is because of a concurrent
update?

There are many reasons that the INSERT statement could fail (e.g. syntax,
database connection dropped, conflicting concurrent update, unique constraint).

In serialiable mode, I am supposed to put the database update code inside of
a loop.  There are two break conditions for this loop:
   the update code succeeded  or
   a database error not caused by a concurrent update occurred

When the error is 'Can't serialize' it is trivial to know to retry the loop.

If I receive the legal error "duplicate key" error, how am I supposed to
detect if that error is due to a concurrent update, or because of some
other error elsewhere?

If I receive a different error, how can I tell if I should retry or
pass the error onto the user?

Thanks for any help!

Ryan
--
Ryan VanderBijl                              rvbijl@vanderbijlfamily.com