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

From: Janning Vygen <vygen(at)planwerk6(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Janning Vygen <vygen(at)gmx(dot)de>, Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Changes to not deferred FK in 8.0.3 to 7.4?
Date: 2005-07-19 18:28:48
Message-ID: 200507192028.48563.vygen@planwerk6.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Janning Vygen 2005-07-19 18:30:34 Re: Changes to not deferred FK in 8.0.3 to 7.4?
Previous Message Jim C. Nasby 2005-07-19 18:28:11 Re: Converting MySQL tinyint to PostgreSQL