Skip site navigation (1) Skip section navigation (2)

deferred foreign keys

From: Vivek Khera <khera(at)kcilink(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: deferred foreign keys
Date: 2003-12-29 20:33:57
Message-ID: x7brpruzwq.fsf@yertle.int.kciLink.com (view raw or flat)
Thread:
Lists: pgsql-performance
I'm observing that when I have many processes doing some work on my
system that the transactions run along almost in lockstep.  It appears
from messages posted here that the foreign keys are acquiring and
holding locks during the transactions, which seems like it would cause
this behavior.

I'd like to experiment with deferred foreign key checks so that the
lock is only held during the commit when the checks are done.

My questions are:

1) can I, and if so, how do I convert my existing FK's to deferrable
   without drop/create of the keys.  Some of the keys take a long time
   to create and I'd like to avoid the hit.

2) do I increase the liklihood of deadlocks when the FK locks are
   being acquired or is it just as likeley as with the current
   non-deferred checking?

I'm running 7.4 (soon to be 7.4.1)


-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
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

pgsql-performance by date

Next:From: Nicholas ShannyDate: 2003-12-30 15:00:30
Subject: Re: Question about difference in performance of 2 queries
Previous:From: Sean ShannyDate: 2003-12-29 19:52:38
Subject: Re: Question about difference in performance of 2 queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group