Re: Performance question

Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)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