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(at)vanderbijlfamily(dot)com