Hello all,
I have what appears to be a bug to report. Basically, foreign key deferred locking behavior on update is inconsistent between single updates and double updates in transactions.
Imagine I have done the following setup:
DROP TABLE B;
DROP TABLE A;
CREATE TABLE A (
ID int NOT NULL PRIMARY KEY,
Payload int
);
CREATE TABLE B (
ID int NOT NULL PRIMARY KEY,
AID int REFERENCES A(ID) DEFERRABLE INITIALLY DEFERRED,
Payload int
);
INSERT INTO A (ID, Payload) VALUES (1, 100);
INSERT INTO B (ID, AID, Payload) VALUES (1, 1, 200);
Now, in process 1, I do:
BEGIN;
SELECT * FROM A WHERE ID = 1 FOR UPDATE;
Now consider the versions A and B of process 2.
Version A:
BEGIN;
UPDATE B SET "payload" = 1000 WHERE ID = 1;
COMMIT;
Version B:
BEGIN;
UPDATE B SET "payload" = 1000 WHERE ID = 1;
UPDATE B SET "payload" = 1000 WHERE ID = 1;
COMMIT;
In process 2, if I do version A, it goes through immediately. If I do version B, it hangs, waiting for process 1 to release its lock. I would expect consistent behavior.
I was unsure from documentation which of two behaviors was the "expected" behavior.
Version: PostgreSQL 10.6 (Ubuntu 10.6-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit
Which one is expected? And is this really a bug?