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

Am Montag, 18. Juli 2005 16:28 schrieb Stephan Szabo:
> 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?)

ok, i understand that circular references are checked in any order and it
worked by luck in 7.4.

But why doesn't it work if i make alle FK deferrable initially deferred?

IMHO the check should occur at the end of the transaction, right? So at this
time alle PK and FK should be updated and everything should work fine. But it
doesn't. Or did i just get the pg_catalog update statment wrong making all my
fk "deferrable inititally deferred"?

i am kind of helpless.

kind regards,
janning