Bug: Deferred FKey Check Happening on Double Update, Not Single
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
Hi
Which one is expected?
Both are expected. This is foreign key check optimisation: we can safely skip FK trigger check if row was inserted not in this transaction and update does not touch FK fields: https://github.com/postgres/postgres/blob/REL_11_STABLE/src/backend/utils/adt/ri_triggers.c#L1769
And is this really a bug?
so no, this is not bug.
regards, Sergei
Hey Sergei,
Neither transaction touches FK fields - so shouldn't they both skip FK
trigger check?
Best,
Doug
On Thu, Feb 14, 2019 at 11:42 PM Sergei Kornilov <sk@zsrv.org> wrote:
Show quoted text
Hi
Which one is expected?
Both are expected. This is foreign key check optimisation: we can safely
skip FK trigger check if row was inserted not in this transaction and
update does not touch FK fields:
https://github.com/postgres/postgres/blob/REL_11_STABLE/src/backend/utils/adt/ri_triggers.c#L1769And is this really a bug?
so no, this is not bug.
regards, Sergei
"Doug" == Doug Safreno <doug@avinetworks.com> writes:
Doug> Hey Sergei,
Doug> Neither transaction touches FK fields - so shouldn't they both
Doug> skip FK trigger check?
The optimization that lets the check be skipped only applies on the
_first_ modification of the row within the transaction. On the second or
subsequent modifications, the code can't easily tell whether the row was
inserted in the current transaction (in which case the optimization must
be skipped) or just modified, so it assumes the worst.
--
Andrew (irc:RhodiumToad)
Gotcha, thanks for explanation.
Best,
Doug
On Fri, Feb 15, 2019 at 3:15 PM Andrew Gierth <andrew@tao11.riddles.org.uk>
wrote:
Show quoted text
"Doug" == Doug Safreno <doug@avinetworks.com> writes:
Doug> Hey Sergei,
Doug> Neither transaction touches FK fields - so shouldn't they both
Doug> skip FK trigger check?The optimization that lets the check be skipped only applies on the
_first_ modification of the row within the transaction. On the second or
subsequent modifications, the code can't easily tell whether the row was
inserted in the current transaction (in which case the optimization must
be skipped) or just modified, so it assumes the worst.--
Andrew (irc:RhodiumToad)