Re: Performance question

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