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