Have TRUNCATE update pgstat tuple counters

This works by keeping a per-subtransaction record of the ins/upd/del
counters before the truncate, and then resetting them; this record is
useful to return to the previous state in case the truncate is rolled
back, either in a subtransaction or whole transaction.  The state is
propagated upwards as subtransactions commit.

When the per-table data is sent to the stats collector, a flag indicates
to reset the live/dead counters to zero as well.

Catalog version bumped due to the change in pgstat format.

Author: Alexander Shulgin
Discussion: 1007.1207238291@sss.pgh.pa.us
Discussion: 548F7D38.2000401@BlueTreble.com
Reviewed-by: Álvaro Herrera, Jim Nasby

5 files changed, 258 insertions, 0 deletions

diff --git a/src/test/regress/expected/prepared_xacts.out b/src/test/regress/expected/prepared_xacts.out
index c0b08649e8d..9dbf874b63e 100644
--- a/src/test/regress/expected/prepared_xacts.out
+++ b/src/test/regress/expected/prepared_xacts.out
@@ -247,8 +247,58 @@ SELECT gid FROM pg_prepared_xacts;
 -----
 (0 rows)
 
+CREATE TABLE pxtest5 (a SERIAL);
+INSERT INTO pxtest5 DEFAULT VALUES;
+SELECT * FROM pxtest5;
+ a 
+---
+ 1
+(1 row)
+
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+  INSERT INTO pxtest5 DEFAULT VALUES;
+  INSERT INTO pxtest5 DEFAULT VALUES;
+  TRUNCATE pxtest5;
+  INSERT INTO pxtest5 DEFAULT VALUES;
+PREPARE TRANSACTION 'trunc-and-pgstat';
+SELECT pg_sleep(0.5);
+ pg_sleep 
+----------
+ 
+(1 row)
+
+SELECT n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
+  FROM pg_stat_user_tables
+ WHERE relname='pxtest5';
+ n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup 
+-----------+-----------+-----------+------------+------------
+         1 |         0 |         0 |          1 |          0
+(1 row)
+
+COMMIT PREPARED 'trunc-and-pgstat';
+SELECT pg_sleep(0.5);
+ pg_sleep 
+----------
+ 
+(1 row)
+
+SELECT n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
+  FROM pg_stat_user_tables
+ WHERE relname='pxtest5';
+ n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup 
+-----------+-----------+-----------+------------+------------
+         2 |         0 |         0 |          1 |          0
+(1 row)
+
+SELECT * FROM pxtest5;
+ a 
+---
+ 4
+(1 row)
+
 -- Clean up
 DROP TABLE pxtest2;
 DROP TABLE pxtest3;  -- will still be there if prepared xacts are disabled
 ERROR:  table "pxtest3" does not exist
 DROP TABLE pxtest4;
+DROP TABLE pxtest5;
diff --git a/src/test/regress/expected/prepared_xacts_1.out b/src/test/regress/expected/prepared_xacts_1.out
index 898f278c11e..dfdd5eb0dc1 100644
--- a/src/test/regress/expected/prepared_xacts_1.out
+++ b/src/test/regress/expected/prepared_xacts_1.out
@@ -249,9 +249,62 @@ SELECT gid FROM pg_prepared_xacts;
 -----
 (0 rows)
 
+CREATE TABLE pxtest5 (a SERIAL);
+INSERT INTO pxtest5 DEFAULT VALUES;
+SELECT * FROM pxtest5;
+ a 
+---
+ 1
+(1 row)
+
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+  INSERT INTO pxtest5 DEFAULT VALUES;
+  INSERT INTO pxtest5 DEFAULT VALUES;
+  TRUNCATE pxtest5;
+  INSERT INTO pxtest5 DEFAULT VALUES;
+PREPARE TRANSACTION 'trunc-and-pgstat';
+ERROR:  prepared transactions are disabled
+HINT:  Set max_prepared_transactions to a nonzero value.
+SELECT pg_sleep(0.5);
+ pg_sleep 
+----------
+ 
+(1 row)
+
+SELECT n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
+  FROM pg_stat_user_tables
+ WHERE relname='pxtest5';
+ n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup 
+-----------+-----------+-----------+------------+------------
+         3 |         0 |         0 |          1 |          2
+(1 row)
+
+COMMIT PREPARED 'trunc-and-pgstat';
+ERROR:  prepared transaction with identifier "trunc-and-pgstat" does not exist
+SELECT pg_sleep(0.5);
+ pg_sleep 
+----------
+ 
+(1 row)
+
+SELECT n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
+  FROM pg_stat_user_tables
+ WHERE relname='pxtest5';
+ n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup 
+-----------+-----------+-----------+------------+------------
+         3 |         0 |         0 |          1 |          2
+(1 row)
+
+SELECT * FROM pxtest5;
+ a 
+---
+ 1
+(1 row)
+
 -- Clean up
 DROP TABLE pxtest2;
 ERROR:  table "pxtest2" does not exist
 DROP TABLE pxtest3;  -- will still be there if prepared xacts are disabled
 DROP TABLE pxtest4;
 ERROR:  table "pxtest4" does not exist
+DROP TABLE pxtest5;
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 86319718591..f5be70fe7c4 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -62,6 +62,57 @@ begin
     extract(epoch from clock_timestamp() - start_time);
 end
 $$ language plpgsql;
+-- test effects of TRUNCATE on n_live_tup/n_dead_tup counters
+CREATE TABLE trunc_stats_test(id serial);
+CREATE TABLE trunc_stats_test1(id serial);
+CREATE TABLE trunc_stats_test2(id serial);
+CREATE TABLE trunc_stats_test3(id serial);
+CREATE TABLE trunc_stats_test4(id serial);
+-- check that n_live_tup is reset to 0 after truncate
+INSERT INTO trunc_stats_test DEFAULT VALUES;
+INSERT INTO trunc_stats_test DEFAULT VALUES;
+INSERT INTO trunc_stats_test DEFAULT VALUES;
+TRUNCATE trunc_stats_test;
+-- test involving a truncate in a transaction; 4 ins but only 1 live
+INSERT INTO trunc_stats_test1 DEFAULT VALUES;
+INSERT INTO trunc_stats_test1 DEFAULT VALUES;
+INSERT INTO trunc_stats_test1 DEFAULT VALUES;
+UPDATE trunc_stats_test1 SET id = id + 10 WHERE id IN (1, 2);
+DELETE FROM trunc_stats_test1 WHERE id = 3;
+BEGIN;
+UPDATE trunc_stats_test1 SET id = id + 100;
+TRUNCATE trunc_stats_test1;
+INSERT INTO trunc_stats_test1 DEFAULT VALUES;
+COMMIT;
+-- use a savepoint: 1 insert, 1 live
+BEGIN;
+INSERT INTO trunc_stats_test2 DEFAULT VALUES;
+INSERT INTO trunc_stats_test2 DEFAULT VALUES;
+SAVEPOINT p1;
+INSERT INTO trunc_stats_test2 DEFAULT VALUES;
+TRUNCATE trunc_stats_test2;
+INSERT INTO trunc_stats_test2 DEFAULT VALUES;
+RELEASE SAVEPOINT p1;
+COMMIT;
+-- rollback a savepoint: this should count 4 inserts and have 2
+-- live tuples after commit (and 2 dead ones due to aborted subxact)
+BEGIN;
+INSERT INTO trunc_stats_test3 DEFAULT VALUES;
+INSERT INTO trunc_stats_test3 DEFAULT VALUES;
+SAVEPOINT p1;
+INSERT INTO trunc_stats_test3 DEFAULT VALUES;
+INSERT INTO trunc_stats_test3 DEFAULT VALUES;
+TRUNCATE trunc_stats_test3;
+INSERT INTO trunc_stats_test3 DEFAULT VALUES;
+ROLLBACK TO SAVEPOINT p1;
+COMMIT;
+-- rollback a truncate: this should count 2 inserts and produce 2 dead tuples
+BEGIN;
+INSERT INTO trunc_stats_test4 DEFAULT VALUES;
+INSERT INTO trunc_stats_test4 DEFAULT VALUES;
+TRUNCATE trunc_stats_test4;
+INSERT INTO trunc_stats_test4 DEFAULT VALUES;
+ROLLBACK;
 -- do a seqscan
 SELECT count(*) FROM tenk2;
  count 
@@ -92,6 +143,18 @@ SELECT wait_for_stats();
 (1 row)
 
 -- check effects
+SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
+  FROM pg_stat_user_tables
+ WHERE relname like 'trunc_stats_test%' order by relname;
+      relname      | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup 
+-------------------+-----------+-----------+-----------+------------+------------
+ trunc_stats_test  |         3 |         0 |         0 |          0 |          0
+ trunc_stats_test1 |         4 |         2 |         1 |          1 |          0
+ trunc_stats_test2 |         1 |         0 |         0 |          1 |          0
+ trunc_stats_test3 |         4 |         0 |         0 |          2 |          2
+ trunc_stats_test4 |         2 |         0 |         0 |          0 |          2
+(5 rows)
+
 SELECT st.seq_scan >= pr.seq_scan + 1,
        st.seq_tup_read >= pr.seq_tup_read + cl.reltuples,
        st.idx_scan >= pr.idx_scan + 1,
@@ -119,4 +182,5 @@ FROM prevstats AS pr;
  t
 (1 row)
 
+DROP TABLE trunc_stats_test, trunc_stats_test1, trunc_stats_test2, trunc_stats_test3, trunc_stats_test4;
 -- End of Stats Test
diff --git a/src/test/regress/sql/prepared_xacts.sql b/src/test/regress/sql/prepared_xacts.sql
index 7902152775c..56d5857ae9c 100644
--- a/src/test/regress/sql/prepared_xacts.sql
+++ b/src/test/regress/sql/prepared_xacts.sql
@@ -152,7 +152,34 @@ SELECT * FROM pxtest3;
 -- There should be no prepared transactions
 SELECT gid FROM pg_prepared_xacts;
 
+CREATE TABLE pxtest5 (a SERIAL);
+INSERT INTO pxtest5 DEFAULT VALUES;
+
+SELECT * FROM pxtest5;
+
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+  INSERT INTO pxtest5 DEFAULT VALUES;
+  INSERT INTO pxtest5 DEFAULT VALUES;
+  TRUNCATE pxtest5;
+  INSERT INTO pxtest5 DEFAULT VALUES;
+PREPARE TRANSACTION 'trunc-and-pgstat';
+
+SELECT pg_sleep(0.5);
+SELECT n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
+  FROM pg_stat_user_tables
+ WHERE relname='pxtest5';
+
+COMMIT PREPARED 'trunc-and-pgstat';
+
+SELECT pg_sleep(0.5);
+SELECT n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
+  FROM pg_stat_user_tables
+ WHERE relname='pxtest5';
+
+SELECT * FROM pxtest5;
+
 -- Clean up
 DROP TABLE pxtest2;
 DROP TABLE pxtest3;  -- will still be there if prepared xacts are disabled
 DROP TABLE pxtest4;
+DROP TABLE pxtest5;
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 16163008308..cd2d5927bc5 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -58,6 +58,64 @@ begin
 end
 $$ language plpgsql;
 
+-- test effects of TRUNCATE on n_live_tup/n_dead_tup counters
+CREATE TABLE trunc_stats_test(id serial);
+CREATE TABLE trunc_stats_test1(id serial);
+CREATE TABLE trunc_stats_test2(id serial);
+CREATE TABLE trunc_stats_test3(id serial);
+CREATE TABLE trunc_stats_test4(id serial);
+
+-- check that n_live_tup is reset to 0 after truncate
+INSERT INTO trunc_stats_test DEFAULT VALUES;
+INSERT INTO trunc_stats_test DEFAULT VALUES;
+INSERT INTO trunc_stats_test DEFAULT VALUES;
+TRUNCATE trunc_stats_test;
+
+-- test involving a truncate in a transaction; 4 ins but only 1 live
+INSERT INTO trunc_stats_test1 DEFAULT VALUES;
+INSERT INTO trunc_stats_test1 DEFAULT VALUES;
+INSERT INTO trunc_stats_test1 DEFAULT VALUES;
+UPDATE trunc_stats_test1 SET id = id + 10 WHERE id IN (1, 2);
+DELETE FROM trunc_stats_test1 WHERE id = 3;
+
+BEGIN;
+UPDATE trunc_stats_test1 SET id = id + 100;
+TRUNCATE trunc_stats_test1;
+INSERT INTO trunc_stats_test1 DEFAULT VALUES;
+COMMIT;
+
+-- use a savepoint: 1 insert, 1 live
+BEGIN;
+INSERT INTO trunc_stats_test2 DEFAULT VALUES;
+INSERT INTO trunc_stats_test2 DEFAULT VALUES;
+SAVEPOINT p1;
+INSERT INTO trunc_stats_test2 DEFAULT VALUES;
+TRUNCATE trunc_stats_test2;
+INSERT INTO trunc_stats_test2 DEFAULT VALUES;
+RELEASE SAVEPOINT p1;
+COMMIT;
+
+-- rollback a savepoint: this should count 4 inserts and have 2
+-- live tuples after commit (and 2 dead ones due to aborted subxact)
+BEGIN;
+INSERT INTO trunc_stats_test3 DEFAULT VALUES;
+INSERT INTO trunc_stats_test3 DEFAULT VALUES;
+SAVEPOINT p1;
+INSERT INTO trunc_stats_test3 DEFAULT VALUES;
+INSERT INTO trunc_stats_test3 DEFAULT VALUES;
+TRUNCATE trunc_stats_test3;
+INSERT INTO trunc_stats_test3 DEFAULT VALUES;
+ROLLBACK TO SAVEPOINT p1;
+COMMIT;
+
+-- rollback a truncate: this should count 2 inserts and produce 2 dead tuples
+BEGIN;
+INSERT INTO trunc_stats_test4 DEFAULT VALUES;
+INSERT INTO trunc_stats_test4 DEFAULT VALUES;
+TRUNCATE trunc_stats_test4;
+INSERT INTO trunc_stats_test4 DEFAULT VALUES;
+ROLLBACK;
+
 -- do a seqscan
 SELECT count(*) FROM tenk2;
 -- do an indexscan
@@ -71,12 +129,17 @@ SELECT pg_sleep(1.0);
 SELECT wait_for_stats();
 
 -- check effects
+SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
+  FROM pg_stat_user_tables
+ WHERE relname like 'trunc_stats_test%' order by relname;
+
 SELECT st.seq_scan >= pr.seq_scan + 1,
        st.seq_tup_read >= pr.seq_tup_read + cl.reltuples,
        st.idx_scan >= pr.idx_scan + 1,
        st.idx_tup_fetch >= pr.idx_tup_fetch + 1
   FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
  WHERE st.relname='tenk2' AND cl.relname='tenk2';
+
 SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
        st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
   FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
@@ -85,4 +148,5 @@ SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
 SELECT pr.snap_ts < pg_stat_get_snapshot_timestamp() as snapshot_newer
 FROM prevstats AS pr;
 
+DROP TABLE trunc_stats_test, trunc_stats_test1, trunc_stats_test2, trunc_stats_test3, trunc_stats_test4;
 -- End of Stats Test