Curt Sampson <cjs@cynic.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
