pg_listener does not have any indexes, so no re-indexing to be done.

I cannot find the function you use pg_relation_size, but I can see the
rel_pages in pg_class stays at 1 for 'pg_listener' even though every
vacuum claims to be reducing the pages from some large number to 1. Is
the vacuum output lying to me? The utput below says it is reducing pages
from 97 to 1 but the data in pg_class shows it was only ever 1 page of
data.




sss=# select pg_relation_size('pg_listener');
ERROR:  function pg_relation_size("unknown") does not exist
HINT:  No function matches the given name and argument types. You may
need to add explicit type casts.

sss=# select * from pg_class where relname='pg_listener';
   relname   | relnamespace | reltype | relowner | relam | relfilenode |
relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex |
relisshared | relkind | relnatts | relchecks | reltriggers | relukeys |
relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |
relhassubclass |    relacl
-------------+--------------+---------+----------+-------+-------------+
----------+-----------+---------------+---------------+-------------+---
----------+---------+----------+-----------+-------------+----------+---
-------+---------+------------+------------+-------------+--------------
--+---------------
 pg_listener |           11 |   16415 |        1 |     0 |       16414 |
1 |        26 |             0 |             0 | f           | f
| r       |        3 |         0 |           0 |        0 |        0 |
0 | f          | f          | t           | f              |
{=r/postgres}
(1 row)

sss=# select * from pg_listener order by listenerpid;
         relname         | listenerpid | notification
-------------------------+-------------+--------------
 _sss_repcluster_Node_3  |        5637 |            0
 _sss_repcluster_Confirm |        5637 |            0
 _sss_repcluster_Event   |        5637 |            0
 _sss_repcluster_Node_3  |        5644 |            0
 _sss_repcluster_Node_2  |        7730 |            0
 _sss_repcluster_Confirm |        7730 |            0
 _sss_repcluster_Event   |        7730 |            0
 _sss_repcluster_Node_2  |        7732 |            0
 _sss_repcluster_Restart |        7866 |            0
 _sss_repcluster_Event   |        7866 |            0
 _sss_repcluster_Node_1  |        7873 |            0
 _sss_repcluster_Node_1  |        7874 |            0
(12 rows)

sss=# vacuum full verbose pg_listener;
INFO:  vacuuming "pg_catalog.pg_listener"
INFO:  "pg_listener": found 7630 removable, 12 nonremovable row versions
in 97 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 96 to 96 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 760964 bytes.
91 pages are or will become empty, including 0 at the end of the table.
97 pages containing 760964 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_listener": moved 5 row versions, truncated 97 to 1 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.16 sec.
VACUUM

sss=# select * from pg_class where relname='pg_listener';
   relname   | relnamespace | reltype | relowner | relam | relfilenode |
relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex |
relisshared | relkind | relnatts | relchecks | reltriggers | relukeys |
relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |
relhassubclass |    relacl
-------------+--------------+---------+----------+-------+-------------+
----------+-----------+---------------+---------------+-------------+---
----------+---------+----------+-----------+-------------+----------+---
-------+---------+------------+------------+-------------+--------------
--+---------------
 pg_listener |           11 |   16415 |        1 |     0 |       16414 |
1 |        12 |             0 |             0 | f           | f
| r       |        3 |         0 |           0 |        0 |        0 |
0 | f          | f          | t           | f              |
{=r/postgres}
(1 row)





-----Original Message-----
From: Joseph Shraibman [mailto:jks at selectacast.net] 
Sent: 22 March 2007 17:14
To: Victoria Parsons
Cc: slony
Subject: Re: [Slony1-general] Vacuum full required?

You need to run select pg_relation_size('tablename') to see how much 
space vacuum full is reclaiming.

Also remember that vacuum full does not reclaim space in the indexes. 
If you don't REINDEX you might end up with indexes that are larger than 
the table itself.

Victoria Parsons wrote:
> The problem is on pg_listener table. I found the same problem on my
test
> 3 node set up.
> 
> I printed the table contents, then did a vacuum full. I then waited 5
> minutes, print the table contents and do a vacuum full again.
> 
> The table details have not changed, all the same slony pids. We have
no
> listens on public tables on this particular database so its easy to
see
> what is going on. To the very best of my knowledge there have been no
> inserts and deletes to this table since the last print, 5 minutes ago.
> 
> I paste the vacuum results below. I've never read the vacuum verbose
> reports before so any guidance on what to look for would be
appreciated.
> The lines that appear to be of interest are
> 
> INFO:  "pg_listener": found 1036 removable, 12 nonremovable row
versions
> in 16 pages
> INFO:  "pg_listener": moved 5 row versions, truncated 16 to 1 pages
> 
> I can wait 5 minutes and repeat and another 16-18 pages of data will
> again be truncated into 1 page. Where is al this extra data coming
from.
> I don't want to jump slony but it seems much worse on the 11 node
> system, which is where the problem first came to light.
> 
> Vicki
> 
> 
> sss=# vacuum full verbose pg_listener;
> INFO:  vacuuming "pg_catalog.pg_listener"
> INFO:  "pg_listener": found 1036 removable, 12 nonremovable row
versions
> in 16 pages
> DETAIL:  0 dead row versions cannot be removed yet.
> Nonremovable row versions range from 96 to 96 bytes long.
> There were 0 unused item pointers.
> Total free space (including removable row versions) is 125408 bytes.
> 10 pages are or will become empty, including 0 at the end of the
table.
> 16 pages containing 125408 free bytes are potential move destinations.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  "pg_listener": moved 5 row versions, truncated 16 to 1 pages
> DETAIL:  CPU 0.00s/0.00u sec elapsed 0.07 sec.
> VACUUM
> 
> 
> 
> 
> -----Original Message-----
> From: Christopher Browne [mailto:cbbrowne at ca.afilias.info] 
> Sent: 22 March 2007 16:05
> To: Victoria Parsons
> Cc: slony
> Subject: Re: [Slony1-general] Vacuum full required?
> 
> "Victoria Parsons" <victoria.parsons at streamshield.com> writes:
>> Has anyone else found that they need a vacuum full to keep their
>> replication, and/or general postgres use up to speed? Have I
>> mis-understood the use of vacuum full, and does it do more than just
>> recover disk space? Next time it goes wrong I will do vacuum full on
>> a table at a time to see if I can narrow down the culprit.
> 
> We *never* run a VACUUM FULL on a whole database; just occasionally on
> some very carefully selected table.
> 
> Definitely you should narrow it down, and run VACUUM FULL ANALYZE on
> each table to see where you get a huge reclaiming of space.  By all
> means, report back on your findings, that may help us help you, and
> help others, as well...


This message should be regarded as confidential. If you have received this 
email in error please notify the sender and destroy it immediately.
Statements of intent shall only become binding when confirmed in hard copy 
by an authorized signatory.