Re: Solution to UPDATE or INSERT Problem

Curt Sampson <cjs(at)cynic(dot)net> writes:
> I've seen a couple of questions here in the past about how to update an
> existing row or insert a row if it doesn't exist without race conditions
> that could cause you to have to retry a transaction. I didn't find any
> answers to this question in the archives however, so I thought I'd post
> my solution here for the edification of others.

> INSERT INTO my_table (key, value) SELECT 1, 'a value'
> WHERE NOT EXISTS (SELECT key FROM my_table WHERE key = 1);
> UPDATE my_table SET value = 'a value' WHERE key = 1;

> This, as far as I can tell, will never fail,

You're quite mistaken. Have you made any effort to test it?

<< session 1 >>

regression=# create table my_table (key int unique, value text);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "my_table_key_key" for table "my_table"
CREATE TABLE
regression=# begin;
BEGIN
regression=# INSERT INTO my_table (key, value) SELECT 1, 'a value'
regression-# WHERE NOT EXISTS (SELECT key FROM my_table WHERE key = 1);
INSERT 429665 1

<< session 2 >>

regression=# begin;
BEGIN
regression=# INSERT INTO my_table (key, value) SELECT 1, 'a value'
regression-# WHERE NOT EXISTS (SELECT key FROM my_table WHERE key = 1);

<< session 2 hangs >>

<< back to session 1 >>

regression=# UPDATE my_table SET value = 'a value' WHERE key = 1;
UPDATE 1
regression=# commit;
COMMIT
regression=#

<< now session 2 fails: >>

ERROR: duplicate key violates unique constraint "my_table_key_key"
regression=#

regards, tom lane