Thread: deferred foreign keys
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/
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/
> 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
>>>>> "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/
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.
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
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.
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
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.
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.
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.