Re: converting FK's to "DEFERRABLE"

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

> Vivek Khera <khera@kcilink.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