3 ms·
I'm a bit confused - can anyone clarify what is the problem in TFA example begin; delete from my_table where key='key'; insert into my_table (key, value)
by PeterisP 13y ago
I'm a bit confused - can anyone clarify what is the problem in TFA example
begin;
delete from my_table where key='key';
insert into my_table (key, value) values ('key', 'value');
commit;
? As far as I understand any outside transaction would either see the old value (assuming one existed) or the new value, not the middle result where the row doesn't exist - am I mistaken and this doesn't work that way in Postgresql?
- nuclear_eclipse 13y agoI think his point is quite exactly that a concurrent write transaction (starts after the first transaction starts but before it commits) that relies on the existence of that key/value pair will fail because of how Postgres works.
- PeterisP 13y agoOK, I tried this on a random PG server I had and replicated the issue with a concurrent update; and running two such transactions in 'overlapping' way inserted two duplicate entries for the same key. That is interesting - is the current behavior really considered WAD? I.e., shouldn't then the 'correct' solution be in fixing this behavior instead of extending syntax for 'upserts'?
- jeltz 13y agoYes, but he is wrong about isolation levels. At serializable you will get a serilization conflict.