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