Thanks folks for the quick replies.
1. There is one transaction, connected from the JVM, that is showing
"IDLE in transaction" .... this appears to be a leftover from
Hibernate looking at the schema metadata. It's Apache Jackrabbit, not
our own code:
hyper9test_1_6=# select c.relname, l.* from pg_class c, pg_locks l
where c.relfilenode=l.relation and l.pid in (select procpid from
pg_stat_activity where current_query='<IDLE> in transaction');
relname | locktype | database | relation | page |
tuple | virtualxid | transactionid | classid | objid | objsubid |
virtualtransaction | pid | mode | granted
----------------------------+----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+-----------------+---------
pg_class_oid_index | relation | 280066 | 2662 | |
| | | | | | 3/18
| 8069 | AccessShareLock | t
pg_class_relname_nsp_index | relation | 280066 | 2663 | |
| | | | | | 3/18
| 8069 | AccessShareLock | t
pg_description_o_c_o_index | relation | 280066 | 2675 | |
| | | | | | 3/18
| 8069 | AccessShareLock | t
pg_namespace_nspname_index | relation | 280066 | 2684 | |
| | | | | | 3/18
| 8069 | AccessShareLock | t
pg_namespace_oid_index | relation | 280066 | 2685 | |
| | | | | | 3/18
| 8069 | AccessShareLock | t
pg_class | relation | 280066 | 1259 | |
| | | | | | 3/18
| 8069 | AccessShareLock | t
pg_description | relation | 280066 | 2609 | |
| | | | | | 3/18
| 8069 | AccessShareLock | t
pg_namespace | relation | 280066 | 2615 | |
| | | | | | 3/18
| 8069 | AccessShareLock | t
version_node | relation | 280066 | 493309 | |
| | | | | | 3/18
| 8069 | AccessShareLock | t
version_node_idx | relation | 280066 | 493315 | |
| | | | | | 3/18
| 8069 | AccessShareLock | t
(10 rows)
Since the Jackrabbit tables are in the same namespace / user / schema
as ours, am I right in thinking that this is effectively blocking the
entire auto-vaccum system from doing anything at all?
Cheers
Dave