Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows - Mailing list pgsql-performance

Hi Tom,

Below, the pg_prepared_xacts result.
The only way to restore the table is with TRUNCATE.
Vacuum, Vacuum full,  cluster not help and subsequent updates will become slow and slow.


carmen=# select * from vlocks where relname='tp93t'; select * from pg_stat_activity where usename='webpa'; select * from pg_prepared_xacts;
 datname | relname | virtualtransaction | mode | granted | usename | substr | query_start | age | procpid
---------+---------+--------------------+------+---------+---------+--------+-------------+-----+---------
(0 rows)

 datid | datname | procpid | usesysid  | usename | current_query | waiting | xact_start |          query_start          |         backend_start         | client_addr | client_port
-------+---------+---------+-----------+---------+---------------+---------+------------+-------------------------------+-------------------------------+-------------+-------------
 16745 | carmen  |   19345 | 750377993 | webpa   | <IDLE>        | f       |            | 2010-08-19 09:40:44.295753-03 | 2010-08-19 09:38:45.637543-03 | 192.168.1.1 |       59867
(1 row)

 transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)

carmen=# VACUUM full verbose tp93t;
INFO:  vacuuming "public.tp93t"
INFO:  "tp93t": found 0 removable, 38588 nonremovable row versions in 9700 pages
DETAIL:  38378 dead row versions cannot be removed yet.
Nonremovable row versions range from 1853 to 2029 bytes long.
There were 317 unused item pointers.
Total free space (including removable row versions) is 1178860 bytes.
0 pages are or will become empty, including 0 at the end of the table.
190 pages containing 442568 free bytes are potential move destinations.
CPU 0.00s/0.02u sec elapsed 0.02 sec.
INFO:  index "tp93t_pkey" now contains 11597 row versions in 195 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "tp93t": moved 0 row versions, truncated 9700 to 9700 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_24274"
INFO:  "pg_toast_24274": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_toast_24274_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM