I have a table
CREATE TABLE table (
id SERIAL PRIMARY KEY
foo INT
)
and I run the inserts at the same time:
INSERT INTO table (foo) VALUES (?)
and removes
DELETE FROM table WHERE ctid = (
SELECT ctid FROM table
WHERE foo = ?
ORDER BY id LIMIT 1)
RETURNING *
(i.e. delete the row with the smallest id
that meets some criteria)
When the isolation level is set to Serializable, I sometimes get ERROR: could not serialize access due to read/write dependencies among transactions
from delete. This makes sense: if an insert occurs at the time the query “a” is made, and when the delete wants to commit, the result of the query “a” is no longer consistent.
, . , , , 'a' ctid
, , ctid
. , ; , . ( , id
ctid
.)
?