pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Vivek Khera <khera@kcilink.com>
To: pgsql-performance@postgresql.org
Subject: deferred foreign keys
Date: Mon, 29 Dec 2003 15:33:57 -0500
Message-ID: <x7brpruzwq.fsf@yertle.int.kciLink.com> (raw)

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



view thread (11+ messages)  latest in thread

Message-ID: <x7brpruzwq.fsf@yertle.int.kciLink.com>
Permalink:  ../x7brpruzwq.fsf@yertle.int.kciLink.com/
Also on:    postgresql.org/message-id/x7brpruzwq.fsf@yertle.int.kciLink.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: khera@kcilink.com
  Subject: Re: deferred foreign keys
  In-Reply-To: <x7brpruzwq.fsf@yertle.int.kciLink.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox