Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows

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

2010/8/18 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

> Alexandre de Arruda Paes <adaldeia(at)gmail(dot)com> writes:
> > I know the problem with VACUUM FULL and bloated Indexes, but I don't
> > understand why the table that is not in use by nobody, cant be vacuumed
> or
> > clustered to avoid dead tuples.
>
> There's an open transaction somewhere that VACUUM is preserving the
> tuples for. This transaction need not ever have touched the table,
> or ever intend to touch the table --- but VACUUM cannot know that,
> so it saves any tuples that the transaction might be entitled to see
> if it looked.
>
> > carmen=# select * from vlocks where relname='tp93t'; select * from
> > pg_stat_activity where usename='webpa';
>
> You keep on showing us only subsets of pg_stat_activity :-(
>
> Also, if you don't see anything in pg_stat_activity, try pg_prepared_xacts.
>
> regards, tom lane
>