From: | Doug Safreno <doug(at)avinetworks(dot)com> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | Gregory Cox <gcox(at)avinetworks(dot)com>, Sambit Das <sambit(dot)das(at)avinetworks(dot)com>, Vivek Kalyanaraman <vivek(at)avinetworks(dot)com>, Anand Parthasarathy <anpartha(at)avinetworks(dot)com> |
Subject: | Bug: Deferred FKey Check Happening on Double Update, Not Single |
Date: | 2019-02-15 00:32:08 |
Message-ID: | CAA=+ort6kOENg7asqzq9OQ6kH0x_S5NLzWD7C=rawYgKWb+YAw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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?
Best,
Doug
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2019-02-15 02:43:02 | BUG #15637: Problem insert data 【Japanese】 |
Previous Message | Tom Lane | 2019-02-14 23:05:00 | Re: Segmentation Fault in logical decoding get/peek API |