Re: Random Deadlock on DROP CONSTRAINT

Omg... i think this one was all on me....

What I thought was closing the connection at the end of my
processing... doesn't look like it was.

Pretty sure this has fixed it. Will let you know.

Mark

On Mon, Mar 9, 2009 at 9:52 AM, Mark Mandel <mark(dot)mandel(at)gmail(dot)com> wrote:
> Thanks for this -
>
> I've attached the results of the following query -
> select pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid,
> pg_locks.mode, pg_locks.granted,pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,30),
> pg_stat_activity.query_start, age(now(),pg_stat_activity.query_start)
> as "age", pg_stat_activity.procpid from pg_stat_activity,pg_locks left
> outer join pg_class on (pg_locks.relation = pg_class.oid)  where
> pg_locks.pid=pg_stat_activity.procpid order by query_start;
>
> It looks to me like I have to many items that are 'idle in transaction'...
>
> I am using JDBC connection pooling, but I was sure I was closing them
> when I was done.
>
> Mark
>
> On Mon, Mar 9, 2009 at 8:29 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>> On Sun, Mar 8, 2009 at 2:54 PM, Mark Mandel <mark(dot)mandel(at)gmail(dot)com> wrote:
>>> Just a note on this -
>>>
>>> I have tried it on both 8.2 and 8.3
>>>
>>> Maybe 'deadlock' isn't the right word, so much as 'hang'.. in that
>>> once it decided to stop at that point, it goes no further, unless I
>>> restart the app server that has the connecton to the database.
>>>
>>> It seems to be completely random, and I'm got no idea what is going on.
>>
>> Well, pg_locks can tell you, and us, a lot.  Next time it's happening
>> grab the contents of pg_locks and attach it to an email here.  Most
>> likely it's some long running transaction or something blocking the
>> access you need.
>>
>
>
>
> --
> E: mark(dot)mandel(at)gmail(dot)com
> W: www.compoundtheory.com
>

--
E: mark(dot)mandel(at)gmail(dot)com
W: www.compoundtheory.com