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