Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> I have suggested that their current INSERT INTO t VALUES() be changed to:
> INSERT INTO
> T
> SELECT 'v1', 'v2'
> WHERE
> NOT EXISTS (
> SELECT NULL FROM t WHERE pk='v1'
> )
That doesn't really buy anything in safety terms: if two backends
execute this sort of command concurrently, it's perfectly likely
that both sub-SELECTS will find no row matching 'v1', and so they'll
both try the INSERT anyway.
IMO the best way to do this (assuming that you have a unique index
defined on the primary key column) is to just go ahead and try the
INSERT, but be prepared to roll back your transaction and retry
if you get a failure.
You might find it useful to read the slides from my talk at last
year's O'Reilly conference about this and related concurrency
problems:
http://conferences.oreillynet.com/cs/os2002/view/e_sess/2681
regards, tom lane
