Re: Finding out on exactly what I am stuck - Mailing list pgsql-admin

Thank you, Kevin, for the tip.

It appears nobody is waiting for a lock (all current locks are granted):

chemaxon=# select
pg_class.relname,pg_locks.pid,pg_locks.mode,pg_locks.granted from
pg_class,pg_locks where pg_class.relfilenode=pg_locks.relation order
by pg_locks.pid;
                relname                 |  pid  |       mode       | granted
----------------------------------------+-------+------------------+---------
 pg_class                               | 14735 | AccessShareLock  | t
 pg_class_oid_index                     | 14735 | AccessShareLock  | t
 prop_table_speedtest                   | 14735 | AccessShareLock  | t
 struc_table_speedtest_pkey             | 14735 | AccessShareLock  | t
 struc_table_speedtest_pkey             | 14735 | RowExclusiveLock | t
 struc_table_speedtest_cd_id_seq        | 14735 | AccessShareLock  | t
 struc_table_speedtest_fx               | 14735 | AccessShareLock  | t
 pg_namespace_oid_index                 | 14735 | AccessShareLock  | t
 struc_table_speedtest_ul               | 14735 | RowExclusiveLock | t
 pg_namespace_nspname_index             | 14735 | AccessShareLock  | t
 pg_class_relname_nsp_index             | 14735 | AccessShareLock  | t
 struc_table_speedtest                  | 14735 | AccessShareLock  | t
 struc_table_speedtest                  | 14735 | RowExclusiveLock | t
 struc_table_speedtest_fx               | 14735 | RowExclusiveLock | t
 struc_table_speedtest_hx               | 14735 | AccessShareLock  | t
 struc_table_speedtest_hx               | 14735 | RowExclusiveLock | t
 prop_table_speedtest_pkey              | 14735 | AccessShareLock  | t
 struc_table_speedtest_ul_update_id_seq | 14735 | AccessShareLock  | t
 pg_namespace                           | 14735 | AccessShareLock  | t
 struc_table_speedtest                  | 14737 | RowExclusiveLock | t
 struc_table_speedtest                  | 14737 | ExclusiveLock    | t
 struc_table_speedtest_fx               | 14737 | RowExclusiveLock | t
 struc_table_speedtest_pkey             | 14737 | RowExclusiveLock | t
 struc_table_speedtest_hx               | 14737 | RowExclusiveLock | t
 pg_class_relname_nsp_index             | 15488 | AccessShareLock  | t
 pg_class                               | 15488 | AccessShareLock  | t
 pg_class_oid_index                     | 15488 | AccessShareLock  | t
 pg_locks                               | 15488 | AccessShareLock  | t
(28 rows)

Any more ideas?

Thanks
Peter

2009/4/9 Kevin Grittner <Kevin.Grittner@wicourts.gov>:
> Péter Kovács <maxottovonstirlitz@gmail.com> wrote:
>> postgres 14727  9868  0 22:34 ?        00:00:00 postgres: pkovacs
>> chemaxon 127.0.0.1(12496) idle in transaction
>> postgres 14735  9868  0 22:34 ?        00:00:00 postgres: pkovacs
>> chemaxon 127.0.0.1(12497) idle in transaction
>> postgres 14737  9868  0 22:34 ?        00:00:00 postgres: pkovacs
>> chemaxon 127.0.0.1(12498) UPDATE waiting
>
> I would start by looking at pg_locks to see if your update is blocked
> by one of the connections which is idle in a transaction.  Any locks
> won't be released until the idle transaction commits or rolls back.
> Then look at pg_stat_activity to check timestamps.  Be sure your
> software doesn't have any code path which leaves things dangling.
> (Since you seem to be using Java, I recommend proper use of
> try/finally blocks to ensure resources are released.)
>
> -Kevi
>