Thread

1. converting FK's to "DEFERRABLE"

   Vivek Khera <khera@kcilink.com> — 2004-09-17T19:04:08Z

   In order to try to reduce lock contention on my FK's, I need to
   convert them to DEFERRABLE.  The straightforward way is to drop and
   recreate the modified FK.  However, on a table with 65M rows, this is
   taking quite some time.  I'm afraid how long it will take to update
   both FK's on my 170M+ row table...
   
   Anyhow, is there some trickier way to make an FK deferrable?  Mucking
   with the system tables, perhaps?
   
   I see that pg_restore has a way to turn off triggers during the data
   load.  If I can guarantee no updates to the table in question, can I
   use that same code to disable triggers, drop+add the FK, then
   re-enable triggers?  Or will that not avoid the check when I create
   the new FK?
   
   I'd like to avoid a few hours of downtime while updating these
   triggers.
   
   Thanks.
   
   -- 
   =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
   Vivek Khera, Ph.D.                Khera Communications, Inc.
   Internet: khera@kciLink.com       Rockville, MD  +1-301-869-4449 x806
   AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/
   
   

2. Re: converting FK's to "DEFERRABLE"

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-09-17T19:27:10Z

   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.
   
   AFAIK the most reliable way to find the triggers is to follow the
   linking entries in pg_depend.
   
   			regards, tom lane
   
   

3. Re: converting FK's to "DEFERRABLE"

   Vivek Khera <khera@kcilink.com> — 2004-09-24T17:27:35Z

   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