Solution: 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 <mo...@neadwerx.com> wrote: > On Tue, May 21, 2013 at 2:39 PM, Moshe Jacobson <mo...@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 > 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 > mo...@neadwerx.com | www.neadwerx.com > > "Quality is not an act, it is a habit." -- Aristotle > -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it is a habit." -- Aristotle
