The inserts of the foreign key to tb_entity were blocking the updates to those rows of tb_entity.
I solved the problem by making the foreign key constraints deferrable and deferring checking on them till the end of the transaction.
On Tue, May 21, 2013 at 3:24 PM, Moshe Jacobson <moshe@neadwerx.com> wrote:
On Tue, May 21, 2013 at 2:39 PM, Moshe Jacobson <moshe@neadwerx.com> wrote:
What could be causing this ROW SHARE lock to be in place for some cloning operations and not others? Perhaps there is a way to see which specific statement is causing the lock to occur, but I'm not sure how to look this up. Any help would be appreciated.
Update:
Apparently the hanging updates to tb_entity are not only related to fn_clone_location_map(), because a hung update to tb_entity did not correspond to a running instance of fn_clone_location_map().
I looked at pg_locks for one hung update to tb_entity, and it said it had an un-granted lock of type transactionid, for another transaction ID.
I wanted to see what was happening in that transaction ID that might be causing this lock to be held, but I was unsure how. I know I can look in pg_locks for the other transaction, but that will not tell me what statement is executing in that transaction. pg_stat_activity does not have a transaction ID column.
How can I see what statement is executing in a transaction?
--
Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering