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

On Mon, 18 Jul 2005, Tom Lane wrote:

> 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.

True, although I think in 7.4 it was more likely to work since the check
triggers would be put on the trigger queue after the first level of
referential action triggers rather than be run immediately between, right?
I'm not sure when the triggered update's constraint checks are supposed to
fire (is it as part of the referential action's updating action or the
original query's constraint checks at end of statement?)