Thread: deferred foreign keys

deferred foreign keys

From
Vivek Khera
Date:
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/

Re: deferred foreign keys

From
Vivek Khera
Date:
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/

Re: deferred foreign keys

From
Christopher Kings-Lynne
Date:
> 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

Re: deferred foreign keys

From
Vivek Khera
Date:
>>>>> "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/

Re: deferred foreign keys

From
Bruno Wolff III
Date:
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.

Re: deferred foreign keys

From
Vivek Khera
Date:
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


Re: deferred foreign keys

From
Stephan Szabo
Date:
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.

Re: deferred foreign keys

From
Vivek Khera
Date:
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


Re: deferred foreign keys

From
Stephan Szabo
Date:
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.


Re: deferred foreign keys

From
Rod Taylor
Date:
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.



Re: deferred foreign keys

From
Stephan Szabo
Date:
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.