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

Started by Doug Safrenoabout 7 years ago5 messagesbugs
Jump to latest
#1Doug Safreno
doug@avinetworks.com

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

In reply to: Doug Safreno (#1)
Re: Bug: Deferred FKey Check Happening on Double Update, Not Single

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

#3Doug Safreno
doug@avinetworks.com
In reply to: Sergei Kornilov (#2)
Re: Bug: Deferred FKey Check Happening on Double Update, Not Single

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#L1769

And is this really a bug?

so no, this is not bug.

regards, Sergei

#4Andrew Gierth
andrew@tao11.riddles.org.uk
In reply to: Doug Safreno (#3)
Re: Bug: Deferred FKey Check Happening on Double Update, Not Single

"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)

#5Doug Safreno
doug@avinetworks.com
In reply to: Andrew Gierth (#4)
Re: Bug: Deferred FKey Check Happening on Double Update, Not Single

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)