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

Am Dienstag, 19. Juli 2005 15:40 schrieb Tom Lane:
> Janning Vygen <vygen(at)gmx(dot)de> writes:
> > On more related question:
> > I updated pg_trigger and pg_constraint and changed all my FK:
> >
> > UPDATE pg_trigger
> > SET
> > tgdeferrable = true,
> > tginitdeferred = true
> > WHERE tgconstrname LIKE 'fk_%'
> > ;
> >
> > UPDATE pg_constraint
> > SET
> > condeferrable = true,
> > condeferred = true
> > WHERE conname LIKE 'fk_%'
> > ;
>
> No, only the triggers that are for checks should be marked
> deferrable/deferred. These are the ones using functions
> RI_FKey_check_ins
> RI_FKey_check_upd
> RI_FKey_noaction_del
> RI_FKey_noaction_upd
> You want the others nondeferrable because (a) that's the standard
> behavior and (b) it'll ensure that the actions happen before the
> checks are made.

ok thanks. i do it now like this:

UPDATE pg_trigger
SET
tgdeferrable = true,
tginitdeferred = true
WHERE tgconstrname LIKE 'fk_%'
AND tgfoid IN (
SELECT oid FROM pg_proc
WHERE proname IN (
'RI_FKey_check_ins', 'RI_FKey_check_upd',
'RI_FKey_noaction_del', 'RI_FKey_noaction_upd')
)
;

UPDATE pg_constraint
SET
condeferrable = true,
condeferred = true
WHERE conname LIKE 'fk_%'
;

COMMIT;

This should work i hope, but i feel a little bit unsure if hacking the
pg_catalog is a good way to do it. Maybe I should have take the long, but
secure way by modifying the schema with ddl statements.

kind regards,
janning