Hung determining statistics

["Stephen Denne" <Stephen(dot)Denne(at)datamail(dot)co(dot)nz>]

While running a rather large VACUUM FULL VERBOSE ANALYZE [table]

I managed to hang pgAdmin (v1.8.1 on WinXP) by attempting to view the statistics tab of the primary key on the table I was vacuuming. That was an hour ago. The vacuum is still proceeding, so pgAdmin may become responsive again when the vacuum completes.

The PostgreSQL log reported:

2008-01-28 11:22:51.894 NZDT [2348]: [20-1] LOG:  process 2348 still waiting for AccessShareLock on relation 17079 of database 16466 after 1077.999 ms
2008-01-28 11:22:51.894 NZDT [2348]: [21-1] STATEMENT:  SELECT pg_size_pretty(pg_relation_size(17079::oid)) AS "Index Size"

pgAdmin will now only show me the main window, none of the open query windows (including the one from which I started the vacuum). The main window is unresponsive, mouse is an hour glass, and there is no menubar shown.
The status bar says "Retrieving Primary Key details... Done." "0.02 secs"

The pgadmin.log ends with:


2008-01-28 11:21:21 STATUS : Retrieving Table details...
2008-01-28 11:21:21 QUERY  : Set query (DM5376:15432): SELECT DISTINCT deptype, classid, cl.relkind,
       CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind || COALESCE(dep.objsubid::text, '')
            WHEN tg.oid IS NOT NULL THEN 'T'::text
            WHEN ty.oid IS NOT NULL THEN 'y'::text
            WHEN ns.oid IS NOT NULL THEN 'n'::text
            WHEN pr.oid IS NOT NULL THEN 'p'::text
            WHEN la.oid IS NOT NULL THEN 'l'::text
            WHEN rw.oid IS NOT NULL THEN 'R'::text
            WHEN co.oid IS NOT NULL THEN 'C'::text || contype
            ELSE '' END AS type,
       COALESCE(coc.relname, clrw.relname) AS ownertable,
       COALESCE(cl.relname || '.' || att.attname, cl.relname, conname, proname, tgname, typname, lanname, rulename, ns.nspname) AS refname,
       COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname
  FROM pg_depend dep
  LEFT JOIN pg_class cl ON dep.objid=cl.oid
  LEFT JOIN pg_attribute att ON dep.objid=att.attrelid AND dep.objsubid=att.attnum  LEFT JOIN pg_names
2008-01-28 11:21:22 STATUS : Retrieving Table details... (0.50 secs)
2008-01-28 11:21:22 QUERY  : Set query (DM5376:15432): SELECT DISTINCT deptype, classid, cl.relkind,
       CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind || COALESCE(dep.objsubid::text, '')
            WHEN tg.oid IS NOT NULL THEN 'T'::text
            WHEN ty.oid IS NOT NULL THEN 'y'::text
            WHEN ns.oid IS NOT NULL THEN 'n'::text
            WHEN pr.oid IS NOT NULL THEN 'p'::text
            WHEN la.oid IS NOT NULL THEN 'l'::text
            WHEN rw.oid IS NOT NULL THEN 'R'::text
            WHEN co.oid IS NOT NULL THEN 'C'::text || contype
            ELSE '' END AS type,
       COALESCE(coc.relname, clrw.relname) AS ownertable,
       COALESCE(cl.relname || '.' || att.attname, cl.relname, conname, proname, tgname, typname, lanname, rulename, ns.nspname) AS refname,
       COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname
  FROM pg_depend dep
  LEFT JOIN pg_class cl ON dep.objid=cl.oid
  LEFT JOIN pg_attribute att ON dep.objid=att.attrelid AND dep.objsubid=att.attnum  LEFT JOIN pg_names
2008-01-28 11:21:26 STATUS : Retrieving Primary Key details...
2008-01-28 11:21:26 STATUS : Retrieving Primary Key details... (0.02 secs)
2008-01-28 11:22:51 QUERY  : Set query (DM5376:15432): SELECT pg_size_pretty(pg_relation_size(17079::oid)) AS "Index Size"



I would prefer pgAdmin to timeout the gathering of this statistic, and continue being a responsive application.


I ran a second pgAdmin program, and cancelled the query, at which time the first program reported the error "canceling statement due to user request", and became responsive again (allowing me to switch back to the properties tab after repeating my mistake with the table statistics).


Stephen Denne

Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege.  If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way.

__________________________________________________________________
  This email has been scanned by the DMZGlobal Business Quality 
              Electronic Messaging Suite.
Please see http://www.dmzglobal.com/services/bqem.htm for details.
__________________________________________________________________