Re: Changes to not deferred FK in 8.0.3 to 7.4?

Janning Vygen <vygen(at)gmx(dot)de> writes:
> I have lots of tables with mutli-column PK and multi-column FK. All FK are
> cascading, so updating a PK should trigger through the whole database.

> This worked earlier in 7.4:

> UPDATE tipprunden SET tr_kurzname = 'schwarze2' where tr_kurzname =
> 'schwarze';

> it should cacsade through lots of tables and other primary key as each table
> has at least a column of "tr_kurzname".

> With 8.0.3 it get error messages like:

> ERROR: insert or update on table "spieletipps" violates foreign key
> constraint "fk_tippspieltage2spiele"
> DETAIL: Key (tr_kurzname,sp_id)=(schwarze2,197619) is not present in table
> "tippspieltage2spiele".
> CONTEXT: SQL statement "UPDATE ONLY "public"."spieletipps" SET "tr_kurzname"
> = $1, "mg_name" = $2 WHERE "tr_kurzname" = $3 AND "mg_name" = $4"
> SQL statement "UPDATE ONLY "public"."mitglieder" SET "tr_kurzname" = $1 WHERE
> "tr_kurzname" = $2

> What happens here to me is, that it cascades first from "tipprunden" to
> "mitglieder" to "spieletipps". But "tippspieltage2spiele" relates to
> "tipprunden" as well, so updating "spieletipps" fails because the FK
> fk_tippspieltage2spiele fails as the table "tippspieltage2spiele" is not up
> to date at this moment.

AFAICS, if it worked for you in 7.4 it was only by pure chance. There
was not then, and is not now, any logic that would prevent the FK checks
from being applied in an order you don't want.

My guess is that the reason for the change in behavior is that the 7.4
FK checks happened to fire in a "safe" order and the same checks in 8.0
are being fired in a different order. This could be pg_dump's fault
--- it seems to feel that it can dump FK constraints in any order it
wants to, rather than trying to preserve the order-of-creation which is
(I believe) what determines the order of trigger firing.

Note that the order I'm thinking of is the firing order of multiple
triggers for the same event on the same table; this is quite unrelated
to the question of deferred-ness for the whole trigger set.

regards, tom lane