Re: Strange locking problem

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 <moshe(at)neadwerx(dot)com> wrote:

> On Tue, May 21, 2013 at 2:39 PM, Moshe Jacobson <moshe(at)neadwerx(dot)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
> moshe(at)neadwerx(dot)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
moshe(at)neadwerx(dot)com | www.neadwerx.com

"Quality is not an act, it is a habit." -- Aristotle