Обсуждение: 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 inthis 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:
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
>>>>> "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:
>>>>> "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)