Solution:
The inserts of the foreign key to tb_entity were blocking the updates to those rows of tb_entity.
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@xxxxxxxxxxxx> wrote:
On Tue, May 21, 2013 at 2:39 PM, Moshe Jacobson <moshe@xxxxxxxxxxxx> wrote:
Update: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.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 Engineering2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@xxxxxxxxxxxx | www.neadwerx.com"Quality is not an act, it is a habit." -- Aristotle
Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@xxxxxxxxxxxx | www.neadwerx.com
moshe@xxxxxxxxxxxx | www.neadwerx.com
"Quality is not an act, it is a habit." -- Aristotle
