Re: converting FK's to "DEFERRABLE"

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