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.marlowe@xxxxxxxxx> wrote:
> On Sun, Mar 8, 2009 at 2:54 PM, Mark Mandel <mark.mandel@xxxxxxxxx> 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.mandel@xxxxxxxxx
W: www.compoundtheory.com
| datname |
relname |
transactionid |
mode |
granted |
usename |
substr |
query_start |
age |
procpid |
| tissupath |
doctor_pkey |
|
AccessShareLock |
t |
postgres |
<IDLE> in transaction |
2009-03-09 09:46:13.471743+11 |
00:01:03.46 |
8450 |
| tissupath |
doctor_internalid_key |
|
AccessShareLock |
t |
postgres |
<IDLE> in transaction |
2009-03-09 09:46:13.471743+11 |
00:01:03.46 |
8450 |
| tissupath |
|
|
ExclusiveLock |
t |
postgres |
<IDLE> in transaction |
2009-03-09 09:46:13.471743+11 |
00:01:03.46 |
8450 |
| tissupath |
title_pkey |
|
AccessShareLock |
t |
postgres |
<IDLE> in transaction |
2009-03-09 09:46:13.471743+11 |
00:01:03.46 |
8450 |
| tissupath |
doctor_hl7id_key |
|
AccessShareLock |
t |
postgres |
<IDLE> in transaction |
2009-03-09 09:46:13.471743+11 |
00:01:03.46 |
8450 |
| tissupath |
users_doctor |
|
AccessShareLock |
t |
postgres |
<IDLE> in transaction |
2009-03-09 09:46:13.471743+11 |
00:01:03.46 |
8450 |
| tissupath |
doctor |
|
AccessShareLock |
t |
postgres |
<IDLE> in transaction |
2009-03-09 09:46:13.471743+11 |
00:01:03.46 |
8450 |
| tissupath |
title |
|
AccessShareLock |
t |
postgres |
<IDLE> in transaction |
2009-03-09 09:46:13.471743+11 |
00:01:03.46 |
8450 |
| tissupath |
|
|
ExclusiveLock |
t |
postgres |
alter table Doctor drop constr |
2009-03-09 09:46:49.063751+11 |
00:00:27.87 |
8456 |
| tissupath |
doctor |
|
AccessExclusiveLock |
f |
postgres |
alter table Doctor drop constr |
2009-03-09 09:46:49.063751+11 |
00:00:27.87 |
8456 |
| tissupath |
pg_class_oid_index |
|
AccessShareLock |
t |
postgres |
select pg_stat_activity.datnam |
2009-03-09 09:47:16.93356+11 |
00:00:00 |
7517 |
| tissupath |
pg_database_oid_index |
|
AccessShareLock |
t |
postgres |
select pg_stat_activity.datnam |
2009-03-09 09:47:16.93356+11 |
00:00:00 |
7517 |
| tissupath |
pg_authid_oid_index |
|
AccessShareLock |
t |
postgres |
select pg_stat_activity.datnam |
2009-03-09 09:47:16.93356+11 |
00:00:00 |
7517 |
| tissupath |
|
|
ExclusiveLock |
t |
postgres |
select pg_stat_activity.datnam |
2009-03-09 09:47:16.93356+11 |
00:00:00 |
7517 |
| tissupath |
pg_locks |
|
AccessShareLock |
t |
postgres |
select pg_stat_activity.datnam |
2009-03-09 09:47:16.93356+11 |
00:00:00 |
7517 |
| tissupath |
pg_stat_activity |
|
AccessShareLock |
t |
postgres |
select pg_stat_activity.datnam |
2009-03-09 09:47:16.93356+11 |
00:00:00 |
7517 |
| tissupath |
pg_authid |
|
AccessShareLock |
t |
postgres |
select pg_stat_activity.datnam |
2009-03-09 09:47:16.93356+11 |
00:00:00 |
7517 |
| tissupath |
pg_class_relname_nsp_index |
|
AccessShareLock |
t |
postgres |
select pg_stat_activity.datnam |
2009-03-09 09:47:16.93356+11 |
00:00:00 |
7517 |
| tissupath |
pg_database |
|
AccessShareLock |
t |
postgres |
select pg_stat_activity.datnam |
2009-03-09 09:47:16.93356+11 |
00:00:00 |
7517 |
| tissupath |
pg_database_datname_index |
|
AccessShareLock |
t |
postgres |
select pg_stat_activity.datnam |
2009-03-09 09:47:16.93356+11 |
00:00:00 |
7517 |
| tissupath |
pg_class |
|
AccessShareLock |
t |
postgres |
select pg_stat_activity.datnam |
2009-03-09 09:47:16.93356+11 |
00:00:00 |
7517 |
| tissupath |
pg_authid_rolname_index |
|
AccessShareLock |
t |
postgres |
select pg_stat_activity.datnam |
2009-03-09 09:47:16.93356+11 |
00:00:00 |
7517 |
|
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
