Hi,
in the release docs it says:
"Non-deferred AFTER triggers are now fired immediately after completion of
the triggering query, rather than upon finishing the current interactive
command. This makes a difference when the triggering query occurred within a
function: the trigger is invoked before the function proceeds to its next
operation. For example, if a function inserts a new row into a table, any
non-deferred foreign key checks occur before proceeding with the function."
I don't know if it relates to my problem:
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.
It makes sense to me when i reread the release notes. Not-deferred FK are
checked immediatley not at the end of the statement so circular references
cant' be handeled with not-deferrable FK !?
Then i tried to make all my FK constraint to be deferrable and initially
deferred like this:
$ UPDATE pg_constraint set condeferrable= 't', condeferred='t' where conname
LIKE 'fk_%';
Is it all what needs to be done to pg_catalog? Or did i miss something. But to
me it looks ok as a table description with '\d' actually states "deferrable
initially deferred" for all my FK.
But with all FK deferred i still get the error above. If i drop a few FK
completely to avoid a circular roundtrip everything works fine (but of course
this is not an option as i need these FKs)
Any help is very appreciated.
kind regards,
janning
