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

Re: deferred foreign keys

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Vivek Khera <khera(at)kcilink(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: deferred foreign keys
Date: 2004-01-05 19:48:26
Message-ID: 20040105114730.U73612@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, 5 Jan 2004, Vivek Khera wrote:

>
> On Jan 5, 2004, at 1:57 PM, Stephan Szabo wrote:
>
> > But, if he's updating the fk table but not the keyed column, it should
> > no
> > longer be doing the check and grabbing the locks.  If he's seeing it
> > grab
> > the row locks still a full test case would be handy because it'd
> > probably
> > mean we missed something.
> >
>
> I'm not *sure* it is taking any locks.  The transactions appear to be
> running lock step (operating on different parts of the same pair of
> tables) and I was going to see if deferring the locks made the
> difference.  It is my feeling now that it will not.  However, if there
> is a way to detect if locks are being taken, I'll do that.  I'd like to
> avoid dropping and recreating the foreign keys if I can since it takes
> up some bit of time on the table with 20+ million rows.

The only way I can think of to see the locks is to do just one of the
operations and then manually attempting to select for update the
associated pk row.


In response to

Responses

pgsql-performance by date

Next:From: Rod TaylorDate: 2004-01-05 19:52:06
Subject: Re: Select max(foo) and select count(*) optimization
Previous:From: Tom LaneDate: 2004-01-05 19:23:45
Subject: Re: optimizing Postgres queries

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