Thread: [BUGS] BUG #14560: FK not valid are ignored during transactions
[BUGS] BUG #14560: FK not valid are ignored during transactions
From
amos_operation@swiss-as.com
Date:
The following bug has been logged on the website: Bug reference: 14560 Logged by: AMOS Operation Email address: amos_operation@swiss-as.com PostgreSQL version: 9.5.5 Operating system: Linux Description: Hello, With a "not valid" FK, I can run single updates without error. But running the same updates inside a transaction does not work. Example: create table source (id int, fk int, name character varying(10)); create table target (id int, name character varying(10)); alter table source add constraint pk_source primary key (id); alter table target add constraint pk_target primary key (id); insert into source values (1,99,'test'); alter table source add constraint fk_test foreign key (fk) references target(id) not valid; update source set name = 'twice' where id = 1; => works update source set name = 'other' where id = 1; => works begin; update source set name = 'unknow2' where id = 4; update source set name = 'unknow3' where id = 4; commit; => does not work: ERROR: insert or update on table "source" violates foreign key constraint "fk_test" DETAIL: Key (fk)=(99) is not present in table "target". Thanks ! Olivier -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
The following bug has been logged on the website:
Bug reference: 14560
Logged by: AMOS Operation
Email address: amos_operation@swiss-as.com
PostgreSQL version: 9.5.5
Operating system: Linux
Description:
alter table source add constraint fk_test foreign key (fk) references
target(id) not valid;
Working as documented.
"""
[...] NOT VALID, which is currently only allowed for foreign key and CHECK constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table, in the case of foreign keys; and they'll fail unless the new row matches the specified check constraints)
"""
David J.
amos_operation@swiss-as.com writes: > With a "not valid" FK, I can run single updates without error. But running > the same updates inside a transaction does not work. The ALTER TABLE manual page says: ADD table_constraint [ NOT VALID ] This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table, in the case of foreign keys; and they'll fail unless the new row matches the specified check constraints). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option. So the behavior in the multiple-inserts-in-one-transaction case is as documented. I think that the fact that you don't see an error when you do only one row update per transaction is because we optimize away the FK check entirely in that case (if the FK columns aren't changed, obviously). I don't recall exactly why that optimization can't be used for repeat updates in a single transaction. In any case, I don't see a bug here; I think you are misunderstanding what NOT VALID is supposed to do. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Tom Lane wrote: > ADD table_constraint [ NOT VALID ] > > This form adds a new constraint to a table using the same syntax as > CREATE TABLE, plus the option NOT VALID, which is currently only > allowed for foreign key and CHECK constraints. If the constraint is > marked NOT VALID, the potentially-lengthy initial check to verify that > all rows in the table satisfy the constraint is skipped. The > constraint will still be enforced against subsequent inserts or > updates (that is, they'll fail unless there is a matching row in the > referenced table, in the case of foreign keys; and they'll fail unless > the new row matches the specified check constraints). But the database > will not assume that the constraint holds for all rows in the table, > until it is validated by using the VALIDATE CONSTRAINT option. > > So the behavior in the multiple-inserts-in-one-transaction case is as > documented. I think that the fact that you don't see an error when you > do only one row update per transaction is because we optimize away the > FK check entirely in that case (if the FK columns aren't changed, > obviously). I don't recall exactly why that optimization can't be used > for repeat updates in a single transaction. In any case, I don't see a > bug here; I think you are misunderstanding what NOT VALID is supposed > to do. I agree that this is working as intended and documented. However, note that the value in the referencing column does not change. I would expect a failure if the value changes from a matching value to a mismatching value; or from a mismatching value to another mismatching value. But if the value does not change, why are we checking it at all? Now, this is outside the initial intent for which NOT VALID was created, which is to separate the long verification table scan from the access exclusive lock. But I think there is room to say that, as a new feature, the proposed behavior ought to at least appear in the TODO list, if someone wants to have a stab at implementing it (not me). -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Alvaro Herrera <alvherre@2ndquadrant.com> writes: > Tom Lane wrote: >> I think that the fact that you don't see an error when you >> do only one row update per transaction is because we optimize away the >> FK check entirely in that case (if the FK columns aren't changed, >> obviously). I don't recall exactly why that optimization can't be used >> for repeat updates in a single transaction. > I would expect a failure if the value changes from a matching value to a > mismatching value; or from a mismatching value to another mismatching > value. But if the value does not change, why are we checking it at all? Well, if you're going to make me go look it up ... RI_FKey_fk_upd_check_required explains: /* * If the original row was inserted by our own transaction, we * must fire the trigger whether or not the keys are equal. This * is because our UPDATE will invalidate the INSERT so that the * INSERT RI trigger will not do anything; so we had better do the * UPDATE check. (We could skip this if we knew the INSERT * trigger already fired, but there is no easy way to know that.) */ So the point is basically that we don't know if the previous row version was a fresh INSERT or an UPDATE, and in the former case there might not have been any previous check that the existing FK values satisfy the constraint. I don't think we can weaken this logic for a NOT VALID constraint, because the contract is still that transactions aren't allowed to create any new violations of the constraint. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs