converting FK's to "DEFERRABLE"

Поиск
Список
Период
Сортировка
От Vivek Khera
Тема converting FK's to "DEFERRABLE"
Дата
Msg-id x7mzzoogyf.fsf@yertle.int.kciLink.com
обсуждение исходный текст
Ответы Re: converting FK's to "DEFERRABLE"  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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@kciLink.com       Rockville, MD  +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

В списке pgsql-general по дате отправления:

Предыдущее
От: Mike Nolan
Дата:
Сообщение: Re: Import an Excel table to a Postgresql one
Следующее
От: Tom Lane
Дата:
Сообщение: Re: converting FK's to "DEFERRABLE"