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

I'm forwarding again this email to list, because Me and Scoot unfortunately
was talking alone. (thanks Scott)

>So what do:
>select * from pg_stat_activity where current_query ilike '%transaction%';
>and
>select * from pg_stat_activity where now()-current_query > '1
minute'::interval;
>say?
>You should really avoid vacuum full, and stick to vacuum (plain). At
>least until you can get the tuples to be freed up. Each time you run
>it you bloat your indexes.

To clarify:

This is a production server with lots of connection and the commands above
returns a lot of rows, but nothing related with this table (see bellow).
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.
Single VACUUM cant recover this dead tuples too.

I see an opened transaction (this is a tomcat servlet webpage), but killing
this transaction does not help the VACUUM:

<webpa 192.168.1.1 2010-08-17 18:36:40.459 BRT 243345>LOG: execute S_1:
BEGIN
<webpa 192.168.1.1 2010-08-17 18:36:40.459 BRT 243346>LOG: duration: 0.010
ms
<webpa 192.168.1.1 2010-08-17 18:36:40.459 BRT 243347>LOG: duration: 0.362
ms
<webpa 192.168.1.1 2010-08-17 18:36:40.460 BRT 243348>LOG: duration: 0.703
ms
<webpa 192.168.1.1 2010-08-17 18:36:40.460 BRT 243349>LOG: execute
<unnamed>: SELECT TP93usuari, TP93Objeto, TP93Ca251, TP93Nm0805, TP93Nm0804,
TP93Ca501, TP93Ca2001, TP93Nm1521, TP93Nm0803, TP93Ca253, TP93Nm1522,
TP93Nm0801, TP93Nm0802, TP93Chave FROM TP93T WHERE (TP93usuari = $1) AND
(TP93Objeto = 'PC0658PP') AND (TP93Ca251 >= $2) ORDER BY TP93Chave
<webpa 192.168.1.1 2010-08-17 18:36:40.460 BRT 243350>DETAIL: parameters:
$1 = 'WEBCLIENTE ', $2 = ' '
<webpa 192.168.1.1 2010-08-17 18:36:40.469 BRT 243351>LOG: duration: 9.302
ms

[postgres(at)servernew logs]$ psql carmen
psql (8.4.4)
Type "help" for help.

carmen=# select * from vlocks where relname='tp93t'; select * from
pg_stat_activity where usename='webpa';

datname | relname | virtualtransaction | mode | granted |
usename | substr | query_start |
age | procpid
---------+---------+--------------------+-----------------+---------+---------+-----------------------+-------------------------------+-----------------+---------
carmen | tp93t | 25/4319 | AccessShareLock | t |
webpa | <IDLE> in transaction | 2010-08-17 18:36:40.460657-03 |
00:01:09.455456 | 1917
(1 row)

datid | datname | procpid | usesysid | usename | current_query |
waiting | xact_start | query_start
| backend_start | client_addr | client_port
-------+---------+---------+-----------+---------+-----------------------+---------+-------------------------------+-------------------------------+-------------------------------+-------------+-------------
16745 | carmen | 1917 | 750377993 | webpa | <IDLE> in transaction |
f | 2010-08-17 18:36:40.459531-03 | 2010-08-17 18:36:40.460657-03 |
2010-08-17 18:36:09.917687-03 | 192.168.1.1 | 39027
(1 row)

carmen=# select * from vlocks where usename='webpa';

datname | relname | virtualtransaction | mode | granted |
usename | substr | query_start |
age | procpid
---------+------------+--------------------+-----------------+---------+---------+-----------------------+-------------------------------+-----------------+---------
carmen | tp93t_pkey | 25/4319 | AccessShareLock | t |
webpa | <IDLE> in transaction | 2010-08-17 18:36:40.460657-03 |
00:01:16.618563 | 1917
carmen | tp93t | 25/4319 | AccessShareLock | t |
webpa | <IDLE> in transaction | 2010-08-17 18:36:40.460657-03 |
00:01:16.618563 | 1917
carmen | | 25/4319 | ExclusiveLock | t |
webpa | <IDLE> in transaction | 2010-08-17 18:36:40.460657-03 |
00:01:16.618563 | 1917
(3 rows)

-----------------------------------------------------------------------------------------------

OK, I will kill the backend and run vacuum:

carmen=# select pg_terminate_backend(1917);
pg_terminate_backend
----------------------
t
(1 row)

carmen=# select * from vlocks where relname='tp93t'; select * from
pg_stat_activity where usename='webpa';

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
-------+---------+---------+--
---------+---------+-----------------------+---------+-------------------------------+-------------------------------+-------------------------------+-------------+-------------
(0 rows)

carmen=# VACUUM verbose tp93t;
INFO: vacuuming "public.tp93t"
INFO: index "tp93t_pkey" now contains 5592 row versions in 103 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": found 0 removable, 19336 nonremovable row versions in 4887
out of 4887 pages
DETAIL: 19126 dead row versions cannot be removed yet.

carmen=# VACUUM FULL verbose tp93t;
INFO: vacuuming "public.tp93t"
INFO: "tp93t": found 0 removable, 19336 nonremovable row versions in 4887
pages
DETAIL: 19126 dead row versions cannot be removed yet.
Nonremovable row versions range from 1853 to 2029 bytes long.
There were 210 unused item pointers.
(...)

2010/8/17 Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>

> On Tue, Aug 17, 2010 at 2:28 PM, Alexandre de Arruda Paes
>
> <adaldeia(at)gmail(dot)com> wrote:
>
> So what do:
> select * from pg_stat_activity where current_query ilike '%transaction%';
> and
> select * from pg_stat_activity where now()-current_query > '1
> minute'::interval;
> say?
>
> > And its the dead rows is growing:
> >
> > carmen=# VACUUM FULL verbose tp93t;
>
> You should really avoid vacuum full, and stick to vacuum (plain). At
> least until you can get the tuples to be freed up. Each time you run
> it you bloat your indexes.
>
> > INFO: vacuuming "public.tp93t"
> > INFO: "tp93t": found 1309 removable, 313890 nonremovable row versions in
> > 78800 pages
> > DETAIL: 312581 dead row versions cannot be removed yet.
> > Nonremovable row versions range from 1845 to 2032 bytes long.
> > There were 3014 unused item pointers.
>
>
> --
> To understand recursion, one must first understand recursion.
>