Re: Random Deadlock on DROP CONSTRAINT

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