Solution to UPDATE...INSERT problem
От | Christopher Kings-Lynne |
---|---|
Тема | Solution to UPDATE...INSERT problem |
Дата | |
Msg-id | 0b7601c2f403$f49a0910$6500a8c0@fhp.internal обсуждение исходный текст |
Ответы |
Re: [HACKERS] Solution to UPDATE...INSERT problem
Re: Solution to UPDATE...INSERT problem |
Список | pgsql-general |
Hi Guys, I just thought I'd share with you guys a very clever solution to the old 'update row. if no rows affected, then insert the row' race condition problem. A guy at my work came up with it. We were discussing this earlier on -hackers, but no-one could find a solution that didn't involve locking the entire table around the update...insert commands. The problem is that sometimes the row will be inserted by another process between your update and insert, causing your insert to fail with a unique constraint violation. So, say this is the insert: INSERT INTO table VALUES (1, 'foo'); // 1 is in the primary key column Rewrite it like this: INSERT INTO table SELECT 1, 'foo' EXCEPT SELECT 1, 'foo' FROM table WHERE pkcol=1; See? So now that INSERT statement will insert the row if it doesn't exist, or insert zero rows if it does. You are then guaranteed that your transaction will not fail and rollback, so you can repeat your update, or do the insert first and then the update, etc. Hope that's handy for people, Chris
В списке pgsql-general по дате отправления: