pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feeddeferred foreign keys
11+ messages / 5 participants
[nested] [flat]
* deferred foreign keys
@ 2003-12-29 20:33 Vivek Khera <khera@kcilink.com>
2003-12-31 17:17 ` Re: deferred foreign keys 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-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 ` Re: deferred foreign keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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
2003-12-29 20:33 deferred foreign keys Vivek Khera <khera@kcilink.com>
2003-12-31 17:17 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
@ 2004-01-02 04:57 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-01-05 16:33 ` Re: deferred foreign keys 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
2003-12-29 20:33 deferred foreign keys Vivek Khera <khera@kcilink.com>
2003-12-31 17:17 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
2004-01-02 04:57 ` Re: deferred foreign keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2004-01-05 16:33 ` Vivek Khera <khera@kcilink.com>
2004-01-05 18:38 ` Re: deferred foreign keys Bruno Wolff III <bruno@wolff.to>
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
2003-12-29 20:33 deferred foreign keys Vivek Khera <khera@kcilink.com>
2003-12-31 17:17 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
2004-01-02 04:57 ` Re: deferred foreign keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-01-05 16:33 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
@ 2004-01-05 18:38 ` Bruno Wolff III <bruno@wolff.to>
2004-01-05 18:57 ` Re: deferred foreign keys Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-05 18:57 ` Re: deferred foreign keys 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
2003-12-29 20:33 deferred foreign keys Vivek Khera <khera@kcilink.com>
2003-12-31 17:17 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
2004-01-02 04:57 ` Re: deferred foreign keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-01-05 16:33 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
2004-01-05 18:38 ` Re: deferred foreign keys Bruno Wolff III <bruno@wolff.to>
@ 2004-01-05 18:57 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-05 19:02 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
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
2003-12-29 20:33 deferred foreign keys Vivek Khera <khera@kcilink.com>
2003-12-31 17:17 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
2004-01-02 04:57 ` Re: deferred foreign keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-01-05 16:33 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
2004-01-05 18:38 ` Re: deferred foreign keys Bruno Wolff III <bruno@wolff.to>
2004-01-05 18:57 ` Re: deferred foreign keys Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-01-05 19:02 ` Vivek Khera <khera@kcilink.com>
2004-01-05 19:48 ` Re: deferred foreign keys 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
2003-12-29 20:33 deferred foreign keys Vivek Khera <khera@kcilink.com>
2003-12-31 17:17 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
2004-01-02 04:57 ` Re: deferred foreign keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-01-05 16:33 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
2004-01-05 18:38 ` Re: deferred foreign keys Bruno Wolff III <bruno@wolff.to>
2004-01-05 18:57 ` Re: deferred foreign keys Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-05 19:02 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
@ 2004-01-05 19:48 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-05 20:14 ` Re: deferred foreign keys Rod Taylor <pg@rbt.ca>
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
2003-12-29 20:33 deferred foreign keys Vivek Khera <khera@kcilink.com>
2003-12-31 17:17 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
2004-01-02 04:57 ` Re: deferred foreign keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-01-05 16:33 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
2004-01-05 18:38 ` Re: deferred foreign keys Bruno Wolff III <bruno@wolff.to>
2004-01-05 18:57 ` Re: deferred foreign keys Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-05 19:02 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
2004-01-05 19:48 ` Re: deferred foreign keys Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-01-05 20:14 ` Rod Taylor <pg@rbt.ca>
2004-01-05 22:33 ` Re: deferred foreign keys 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
2003-12-29 20:33 deferred foreign keys Vivek Khera <khera@kcilink.com>
2003-12-31 17:17 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
2004-01-02 04:57 ` Re: deferred foreign keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-01-05 16:33 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
2004-01-05 18:38 ` Re: deferred foreign keys Bruno Wolff III <bruno@wolff.to>
2004-01-05 18:57 ` Re: deferred foreign keys Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-05 19:02 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
2004-01-05 19:48 ` Re: deferred foreign keys Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-05 20:14 ` Re: deferred foreign keys Rod Taylor <pg@rbt.ca>
@ 2004-01-05 22:33 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
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
* Re: deferred foreign keys
2003-12-29 20:33 deferred foreign keys Vivek Khera <khera@kcilink.com>
2003-12-31 17:17 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
2004-01-02 04:57 ` Re: deferred foreign keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-01-05 16:33 ` Re: deferred foreign keys Vivek Khera <khera@kcilink.com>
2004-01-05 18:38 ` Re: deferred foreign keys Bruno Wolff III <bruno@wolff.to>
@ 2004-01-05 18:57 ` Vivek Khera <khera@kcilink.com>
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
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