Bug: Deferred FKey Check Happening on Double Update, Not Single

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

Responses

Browse pgsql-bugs by date

  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