Re: possible vacuum improvement?

On Tuesday 03 September 2002 16:24, Tom Lane wrote:
> "Mario Weilguni" <mario(dot)weilguni(at)icomedias(dot)com> writes:
> > That brings me to another point, can't the
> > statistics collector used for that?
>
> Hmm, that would be a different way of attacking the problem. Not sure
> offhand which is better, but it'd surely be worth considering both.
>
> Note that collecting of dead-tuple counts requires input from aborted
> transactions as well as successful ones. I don't recall whether the
> stats collector currently collects anything from aborted xacts; that
> might or might not be a sticky point.

I have been doing some poking around with this item, and I was planning on
using the stats collector to do "intelligent" auto-vacuuming. I was planning
on adding some new columns that account for activity that has taken place
since the last vacuum. The current stats collector shows n_tup_ins,
n_tup_upd and n_tup_del for any given rel, but those numbers have nothing to
do with what has happened since the last vacuum, hence nothing to do with
current status or need for vacuum.

I hope to have something worth showing soon (a week or two). I know that is a
bit slow, but I am new at pg internals and since we are in beta I know this
is a 7.4 item.

FYI, the current stats collector does keep track of inserts, updates and
deletes that are part of a rolled back transaction, as shown in the example
below:

matthew=# create TABLE foo (id serial, name text);
NOTICE: CREATE TABLE will create implicit sequence 'foo_id_seq' for SERIAL
column 'foo.id'
CREATE TABLE
matthew=# select relname,n_tup_ins, n_tup_upd, n_tup_del from
pg_stat_all_tables where relname = 'foo';
relname | n_tup_ins | n_tup_upd | n_tup_del
---------+-----------+-----------+-----------
foo | 0 | 0 | 0
(1 row)

matthew=# INSERT INTO foo (name) VALUES ('asdf');
INSERT 17075 1
matthew=# UPDATE foo SET name='qwert';
UPDATE 1
matthew=# DELETE FROM foo;
DELETE 1
matthew=# select relname,n_tup_ins, n_tup_upd, n_tup_del from
pg_stat_all_tables where relname = 'foo';
relname | n_tup_ins | n_tup_upd | n_tup_del
---------+-----------+-----------+-----------
foo | 1 | 1 | 1
(1 row)

matthew=# begin;
BEGIN
matthew=# INSERT INTO foo (name) VALUES ('asdf');
INSERT 17076 1
matthew=# UPDATE foo SET name='qwert';
UPDATE 1
matthew=# DELETE FROM foo;
DELETE 1
matthew=# rollback;
ROLLBACK
matthew=# select relname,n_tup_ins, n_tup_upd, n_tup_del from
pg_stat_all_tables where relname = 'foo';
relname | n_tup_ins | n_tup_upd | n_tup_del
---------+-----------+-----------+-----------
foo | 2 | 2 | 2
(1 row)