Re: VACUUM and transactions in different databases - Mailing list pgsql-general

Hi Ragnar

> could not duplicate this.

I also cannot reproduce the hanging VACUUM FULL.
The problem remains thet the dead tuples cannot be vemoved.

dummy1=# vacuum full;
VACUUM
dummy1=# SELECT relpages, reltuples FROM pg_class WHERE relname ='dummy1';
-[ RECORD 1 ]-----
relpages  | 997
reltuples | 100000

dummy1=# analyze verbose;
...
INFO:  analyzing "public.dummy1"
INFO:  "dummy1": scanned 997 of 997 pages, containing 5000 live rows and 95000 dead rows; 3000 rows in sample, 5000
estimatedtotal rows 
...


dummy1=# select * from pg_stat_activity;
-[ RECORD 1 ]-+------------------------------
datid         | 21529
datname       | dummy1
procpid       | 2065
usesysid      | 10
usename       | postgres
current_query | <command string not enabled>
query_start   |
backend_start | 2006-12-07 21:03:54.877779+01
client_addr   |
client_port   | -1
-[ RECORD 2 ]-+------------------------------
datid         | 21530
datname       | dummy2
procpid       | 2152
usesysid      | 10
usename       | postgres
current_query | <command string not enabled>
query_start   |
backend_start | 2006-12-07 21:07:59.973477+01
client_addr   |
client_port   | -1

the transaction in db dummy2 performed an update and select count(*) and is still running.


dummy1=# select * from pg_locks;
-[ RECORD 1 ]-+-----------------
locktype      | relation
database      | 21530
relation      | 21540
page          |
tuple         |
transactionid |
classid       |
objid         |
objsubid      |
transaction   | 85385
pid           | 2152
mode          | AccessShareLock
granted       | t
-[ RECORD 2 ]-+-----------------
locktype      | relation
database      | 21530
relation      | 21540
page          |
tuple         |
transactionid |
classid       |
objid         |
objsubid      |
transaction   | 85385
pid           | 2152
mode          | RowExclusiveLock
granted       | t
-[ RECORD 3 ]-+-----------------
locktype      | relation
database      | 21529
relation      | 10342
page          |
tuple         |
transactionid |
classid       |
objid         |
objsubid      |
transaction   | 85925
pid           | 2065
mode          | AccessShareLock
granted       | t
-[ RECORD 4 ]-+-----------------
locktype      | transactionid
database      |
relation      |
page          |
tuple         |
transactionid | 85925
classid       |
objid         |
objsubid      |
transaction   | 85925
pid           | 2065
mode          | ExclusiveLock
granted       | t
-[ RECORD 5 ]-+-----------------
locktype      | transactionid
database      |
relation      |
page          |
tuple         |
transactionid | 85385
classid       |
objid         |
objsubid      |
transaction   | 85385
pid           | 2152
mode          | ExclusiveLock
granted       | t

Thanks
Conni