Identifying duplicate key inserts

I have a mechanism which stores objects inside the database. The primary
key is supplied by the object, so when a new object is created I need to
identify duplicate primary keys.

Originally I ran a SELECT against the table to identify if a record with
the same key exists, followed by an UPDATE. This approach can fail in a
race condition with some other thread getting the INSERT done first.

For Sybase I have restructed it to perform the INSERT first, and based
on the returned X/Open error code identify if a duplicate key exists.
Postgresql does not return an X/Open error code, so in the event of a
duplicate key I need to perform the INSERT to determine whether a
duplicate key was there, or it was another INSERT error.

When I perform an INSERT (which fails) followed by a SELECT on the same
table row, the SELECT operation ends with an error, reporting 'No
results were returned by the query.'

Is this a know bug and is a fixed planned for it in 0.7?

arkin

--
----------------------------------------------------------------------
Assaf Arkin www.exoffice.com
CTO, Exoffice Technologies, Inc. www.exolab.org