Re: n_ins_since_vacuum stats for aborted transactions

>
> Yes, there is a bug. Accounting rows inserted as part of an aborted
> transaction in
> n_ins_since_vacuum is not correct, since the same rows are being
> accounted for with n_dead_tup.
>

If I create a table with autovacuum_enabled=false, insert rows (some of
which abort), and check the stats, surely the n_ins_tup and the
n_ins_since_vacuum should be the same, because all the insertions (however
we count them) have happened since the nonexistent last vacuum:

CREATE TABLE n_insert_test (
i INTEGER NOT NULL PRIMARY KEY
) WITH (autovacuum_enabled = false);
INSERT INTO n_insert_test (i) VALUES (1);
INSERT INTO n_insert_test
(SELECT 1 FROM generate_series(1,100000))
ON CONFLICT
DO NOTHING;
SELECT pg_sleep(1);
pg_sleep
----------

(1 row)

SELECT n_live_tup, n_dead_tup, n_tup_ins, n_ins_since_vacuum
FROM pg_stat_all_tables
WHERE relname = 'n_insert_test';
n_live_tup | n_dead_tup | n_tup_ins | n_ins_since_vacuum
------------+------------+-----------+--------------------
1 | 0 | 1 | 1
(1 row)

INSERT INTO n_insert_test
(SELECT 2 FROM generate_series(1,100000))
ON CONFLICT
DO NOTHING;
SELECT pg_sleep(1);
pg_sleep
----------

(1 row)

SELECT n_live_tup, n_dead_tup, n_tup_ins, n_ins_since_vacuum
FROM pg_stat_all_tables
WHERE relname = 'n_insert_test';
n_live_tup | n_dead_tup | n_tup_ins | n_ins_since_vacuum
------------+------------+-----------+--------------------
2 | 0 | 2 | 2
(1 row)

BEGIN;
INSERT INTO n_insert_test
(SELECT * FROM generate_series(3,100000));
ROLLBACK;
SELECT pg_sleep(1);
pg_sleep
----------

(1 row)

SELECT n_live_tup, n_dead_tup, n_tup_ins, n_ins_since_vacuum
FROM pg_stat_all_tables
WHERE relname = 'n_insert_test';
n_live_tup | n_dead_tup | n_tup_ins | n_ins_since_vacuum
------------+------------+-----------+--------------------
2 | 99998 | 100000 | 100000
(1 row)

If we went with your suggestion, I think the final n_ins_since_vacuum
column would be 2. Do you think the n_tup_ins should also be 2? Should
those two columns differ? If so, why?

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company