BUG #14560: FK not valid are ignored during transactions
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
On Tue, Feb 21, 2017 at 5:35 AM, <amos_operation@swiss-as.com> wrote:
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)
"""
https://www.postgresql.org/docs/9.6/static/sql-altertable.html
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