converting FK's to "DEFERRABLE"

From: Vivek Khera <khera(at)kcilink(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: converting FK's to "DEFERRABLE"
Date: 2004-09-17 19:04:08
Message-ID: x7mzzoogyf.fsf@yertle.int.kciLink.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)kciLink(dot)com Rockville, MD +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2004-09-17 19:27:10 Re: converting FK's to "DEFERRABLE"
Previous Message Mike Nolan 2004-09-17 18:10:46 Re: Import an Excel table to a Postgresql one