Re: converting FK's to "DEFERRABLE"

From: Vivek Khera <khera(at)kcilink(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: converting FK's to "DEFERRABLE"
Date: 2004-09-24 17:27:35
Message-ID: 066E39B5-0E4F-11D9-965C-000A9578CFCC@kcilink.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


On Sep 17, 2004, at 3:27 PM, Tom Lane wrote:

> Vivek Khera <khera(at)kcilink(dot)com> writes:
>> Anyhow, is there some trickier way to make an FK deferrable?
>
> Hack its pg_constraint.condeferrable and pg_constraint.condeferred
> fields (the latter is the INITIALLY DEFERRED flag). You will also
> need to find the triggers that implement the constraint and update
> their pg_trigger.tgdeferrable and pg_trigger.tginitdeferred copies
> of these values. Then start fresh backend sessions and I think
> you're there.

Thanks a bunch. This worked flawlessly. Basically I did this:

begin;
select pg_constraint.oid from pg_constraint,pg_class where
pg_constraint.conrelid=pg_class.oid and relname='mytable' and
conname='$1';
X=oid number
update pg_constraint set condeferrable='t' where oid=X;
update pg_trigger set tgdeferrable='t' where oid in (select objid from
pg_depend where refobjid=X);
commit;

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. MailerMailer, LLC Rockville, MD
http://www.MailerMailer.com/ +1-301-869-4449 x806

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2004-09-24 17:40:14 Re: pg_dump: what is advantage with schema and data dumps?
Previous Message pwbyrne 2004-09-24 17:23:50 porting a full Ms Sql Server to postgres