pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
deferred foreign keys
11+ messages / 5 participants
[nested] [flat]

* deferred foreign keys
@ 2003-12-29 20:33  Vivek Khera <khera@kcilink.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Vivek Khera @ 2003-12-29 20:33 UTC (permalink / raw)
  To: 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@kciLink.com       Rockville, MD  +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: deferred foreign keys
@ 2003-12-31 17:17  Vivek Khera <khera@kcilink.com>
  parent: Vivek Khera <khera@kcilink.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Vivek Khera @ 2003-12-31 17:17 UTC (permalink / raw)
  To: pgsql-performance

One more question: does the FK checker know to skip checking a
constraint if the column in question did not change during an update?

That is, if I have a user table that references an owner_id in an
owners table as a foreign key, but I update fields other than owner_id
in the user table, will it still try to verify that owner_id is a
valid value even though it did not change?

I'm using PG 7.4.

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/



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: deferred foreign keys
@ 2004-01-02 04:57  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Vivek Khera <khera@kcilink.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Christopher Kings-Lynne @ 2004-01-02 04:57 UTC (permalink / raw)
  To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance

> One more question: does the FK checker know to skip checking a
> constraint if the column in question did not change during an update?
> 
> That is, if I have a user table that references an owner_id in an
> owners table as a foreign key, but I update fields other than owner_id
> in the user table, will it still try to verify that owner_id is a
> valid value even though it did not change?
> 
> I'm using PG 7.4.

As of 7.4, yes the check is skipped.

Chris



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: deferred foreign keys
@ 2004-01-05 16:33  Vivek Khera <khera@kcilink.com>
  parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 1 reply; 11+ messages in thread

From: Vivek Khera @ 2004-01-05 16:33 UTC (permalink / raw)
  To: pgsql-performance

>>>>> "CK" == Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

>> One more question: does the FK checker know to skip checking a
>> constraint if the column in question did not change during an update?

CK> As of 7.4, yes the check is skipped.


Thanks.  Then it sorta makes it moot for me to try deferred checks,
since the Pimary and Foreign keys never change once set.  I wonder
what is making the transactions appear to run lockstep, then...


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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: deferred foreign keys
@ 2004-01-05 18:38  Bruno Wolff III <bruno@wolff.to>
  parent: Vivek Khera <khera@kcilink.com>
  0 siblings, 2 replies; 11+ messages in thread

From: Bruno Wolff III @ 2004-01-05 18:38 UTC (permalink / raw)
  To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance

On Mon, Jan 05, 2004 at 11:33:40 -0500,
  Vivek Khera <khera@kcilink.com> wrote:
> 
> Thanks.  Then it sorta makes it moot for me to try deferred checks,
> since the Pimary and Foreign keys never change once set.  I wonder
> what is making the transactions appear to run lockstep, then...

I think this is probably the issue with foreign key checks needing an
exclusive lock, since there is no shared lock that will prevent deletes.
This problem has been discussed a number of times on the lists and you
should be able to find out more information from the archives if you
want to confirm that this is the root cause of your problems.



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: deferred foreign keys
@ 2004-01-05 18:57  Stephan Szabo <sszabo@megazone.bigpanda.com>
  parent: Bruno Wolff III <bruno@wolff.to>
  1 sibling, 1 reply; 11+ messages in thread

From: Stephan Szabo @ 2004-01-05 18:57 UTC (permalink / raw)
  To: Bruno Wolff III <bruno@wolff.to>; +Cc: Vivek Khera <khera@kcilink.com>; pgsql-performance

On Mon, 5 Jan 2004, Bruno Wolff III wrote:

> On Mon, Jan 05, 2004 at 11:33:40 -0500,
>   Vivek Khera <khera@kcilink.com> wrote:
> >
> > Thanks.  Then it sorta makes it moot for me to try deferred checks,
> > since the Pimary and Foreign keys never change once set.  I wonder
> > what is making the transactions appear to run lockstep, then...
>
> I think this is probably the issue with foreign key checks needing an
> exclusive lock, since there is no shared lock that will prevent deletes.

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.



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: deferred foreign keys
@ 2004-01-05 18:57  Vivek Khera <khera@kcilink.com>
  parent: Bruno Wolff III <bruno@wolff.to>
  1 sibling, 0 replies; 11+ messages in thread

From: Vivek Khera @ 2004-01-05 18:57 UTC (permalink / raw)
  To: pgsql-performance


On Jan 5, 2004, at 1:38 PM, Bruno Wolff III wrote:

> I think this is probably the issue with foreign key checks needing an
> exclusive lock, since there is no shared lock that will prevent 
> deletes.
>

That was my original thought upon reading all the discussion of late 
regarding the FK checking locks.  I figured if I deferred the checks to 
commit, I could save some contention time.  However, if FK checks are 
skipped if the field in question is not updated, what locks would there 
be?  Are they taken even if the checks are not performed on some sort 
of "be prepared" principle?

Vivek Khera, Ph.D.
+1-301-869-4449 x806




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: deferred foreign keys
@ 2004-01-05 19:02  Vivek Khera <khera@kcilink.com>
  parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Vivek Khera @ 2004-01-05 19:02 UTC (permalink / raw)
  To: pgsql-performance


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.

Vivek Khera, Ph.D.
+1-301-869-4449 x806




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: deferred foreign keys
@ 2004-01-05 19:48  Stephan Szabo <sszabo@megazone.bigpanda.com>
  parent: Vivek Khera <khera@kcilink.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Stephan Szabo @ 2004-01-05 19:48 UTC (permalink / raw)
  To: Vivek Khera <khera@kcilink.com>; +Cc: 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.




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: deferred foreign keys
@ 2004-01-05 20:14  Rod Taylor <pg@rbt.ca>
  parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Rod Taylor @ 2004-01-05 20:14 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Vivek Khera <khera@kcilink.com>; pgsql-performance

On Mon, 2004-01-05 at 14:48, Stephan Szabo wrote:
> 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.

When a locker runs into a row lock held by another transaction, the
locker will show a pending lock on the transaction id in pg_locks.





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: deferred foreign keys
@ 2004-01-05 22:33  Stephan Szabo <sszabo@megazone.bigpanda.com>
  parent: Rod Taylor <pg@rbt.ca>
  0 siblings, 0 replies; 11+ messages in thread

From: Stephan Szabo @ 2004-01-05 22:33 UTC (permalink / raw)
  To: Rod Taylor <pg@rbt.ca>; +Cc: Vivek Khera <khera@kcilink.com>; pgsql-performance

On Mon, 5 Jan 2004, Rod Taylor wrote:

> On Mon, 2004-01-05 at 14:48, Stephan Szabo wrote:
> > 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.
>
> When a locker runs into a row lock held by another transaction, the
> locker will show a pending lock on the transaction id in pg_locks.

Yeah, but AFAIR that won't let you know if it's blocking on the particular
row lock you're expecting.



^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2004-01-05 22:33 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-29 20:33 deferred foreign keys Vivek Khera <khera@kcilink.com>
2003-12-31 17:17 ` Vivek Khera <khera@kcilink.com>
2004-01-02 04:57   ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-01-05 16:33     ` Vivek Khera <khera@kcilink.com>
2004-01-05 18:38       ` Bruno Wolff III <bruno@wolff.to>
2004-01-05 18:57         ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-05 19:02           ` Vivek Khera <khera@kcilink.com>
2004-01-05 19:48             ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-05 20:14               ` Rod Taylor <pg@rbt.ca>
2004-01-05 22:33                 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-05 18:57         ` Vivek Khera <khera@kcilink.com>

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