src/test/isolation2/expected/qe_qd_pgstat.out

-- Test: QE→QD pgstat collection
-- Verifies that DML stats from QE segments reach the QD coordinator's
-- pg_stat_user_tables, enabling autovacuum to see modification counts.
-- Also verifies that gp_stat_user_tables_summary remains accurate on QEs.

--
-- Setup: disable autovacuum and auto_stats to prevent interference.
--
ALTER SYSTEM SET autovacuum = off;
ALTER
SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t              
(1 row)
SELECT pg_sleep(0.5);
 pg_sleep 
----------
          
(1 row)
SET gp_autostats_mode = none;
SET

--
-- Test 1: Distributed (hash) table — INSERT/UPDATE/DELETE stats reach QD
--
CREATE TABLE test_pgstat_dist(id int, val int) DISTRIBUTED BY (id);
CREATE

INSERT INTO test_pgstat_dist SELECT i, 0 FROM generate_series(1, 1000) i;
INSERT 1000
SELECT gp_stat_force_next_flush();
 gp_stat_force_next_flush 
--------------------------
                          
(1 row)

-- QD should see the stats sent from QEs
SELECT n_tup_ins, n_mod_since_analyze FROM pg_stat_user_tables WHERE relname = 'test_pgstat_dist';
 n_tup_ins | n_mod_since_analyze 
-----------+---------------------
 1000      | 1000                
(1 row)

-- QE summary should also show the same counts
SELECT n_tup_ins, n_mod_since_analyze FROM gp_stat_user_tables_summary WHERE relname = 'test_pgstat_dist';
 n_tup_ins | n_mod_since_analyze 
-----------+---------------------
 1000      | 1000                
(1 row)

-- UPDATE non-distribution-key column so it's a real update, not split update
UPDATE test_pgstat_dist SET val = 1 WHERE id <= 100;
UPDATE 100
SELECT gp_stat_force_next_flush();
 gp_stat_force_next_flush 
--------------------------
                          
(1 row)

SELECT n_tup_upd FROM pg_stat_user_tables WHERE relname = 'test_pgstat_dist';
 n_tup_upd 
-----------
 100       
(1 row)
SELECT n_tup_upd FROM gp_stat_user_tables_summary WHERE relname = 'test_pgstat_dist';
 n_tup_upd 
-----------
 100       
(1 row)

DELETE FROM test_pgstat_dist WHERE id <= 50;
DELETE 50
SELECT gp_stat_force_next_flush();
 gp_stat_force_next_flush 
--------------------------
                          
(1 row)

SELECT n_tup_del FROM pg_stat_user_tables WHERE relname = 'test_pgstat_dist';
 n_tup_del 
-----------
 50        
(1 row)
SELECT n_tup_del FROM gp_stat_user_tables_summary WHERE relname = 'test_pgstat_dist';
 n_tup_del 
-----------
 50        
(1 row)

--
-- Test 2: Replicated table — stats not double-counted
-- With 3 segments, each segment has all 500 rows, but only one segment
-- should report stats to QD.
--
CREATE TABLE test_pgstat_repl(id int) DISTRIBUTED REPLICATED;
CREATE

INSERT INTO test_pgstat_repl SELECT i FROM generate_series(1, 500) i;
INSERT 500
SELECT gp_stat_force_next_flush();
 gp_stat_force_next_flush 
--------------------------
                          
(1 row)

-- QD should show exactly 500, not 1500 (3 segments * 500)
SELECT n_tup_ins, n_mod_since_analyze FROM pg_stat_user_tables WHERE relname = 'test_pgstat_repl';
 n_tup_ins | n_mod_since_analyze 
-----------+---------------------
 500       | 500                 
(1 row)

-- QE summary divides replicated table stats by numsegments, so also 500
SELECT n_tup_ins, n_mod_since_analyze FROM gp_stat_user_tables_summary WHERE relname = 'test_pgstat_repl';
 n_tup_ins | n_mod_since_analyze 
-----------+---------------------
 500       | 500                 
(1 row)

--
-- Test 3: Transaction — committed DML stats are counted
--
CREATE TABLE test_pgstat_xact(id int) DISTRIBUTED BY (id);
CREATE

BEGIN;
BEGIN
INSERT INTO test_pgstat_xact SELECT i FROM generate_series(1, 300) i;
INSERT 300
DELETE FROM test_pgstat_xact WHERE id <= 100;
DELETE 100
COMMIT;
COMMIT
SELECT gp_stat_force_next_flush();
 gp_stat_force_next_flush 
--------------------------
                          
(1 row)

SELECT n_tup_ins, n_tup_del FROM pg_stat_user_tables WHERE relname = 'test_pgstat_xact';
 n_tup_ins | n_tup_del 
-----------+-----------
 300       | 100       
(1 row)
SELECT n_tup_ins, n_tup_del FROM gp_stat_user_tables_summary WHERE relname = 'test_pgstat_xact';
 n_tup_ins | n_tup_del 
-----------+-----------
 300       | 100       
(1 row)
SELECT count(*) FROM test_pgstat_xact;
 count 
-------
 200   
(1 row)

--
-- Test 4: Subtransaction rollback — n_tup_ins counts all attempted inserts
-- (PG counts attempted actions regardless of commit/abort)
--
CREATE TABLE test_pgstat_subxact(id int) DISTRIBUTED BY (id);
CREATE

BEGIN;
BEGIN
INSERT INTO test_pgstat_subxact SELECT i FROM generate_series(1, 200) i;
INSERT 200
SAVEPOINT sp1;
SAVEPOINT
INSERT INTO test_pgstat_subxact SELECT i FROM generate_series(201, 700) i;
INSERT 500
ROLLBACK TO sp1;
ROLLBACK
COMMIT;
COMMIT
SELECT gp_stat_force_next_flush();
 gp_stat_force_next_flush 
--------------------------
                          
(1 row)

-- n_tup_ins counts all attempted inserts (200 + 500 = 700)
-- but only 200 rows are actually in the table
SELECT n_tup_ins FROM pg_stat_user_tables WHERE relname = 'test_pgstat_subxact';
 n_tup_ins 
-----------
 700       
(1 row)
SELECT n_tup_ins FROM gp_stat_user_tables_summary WHERE relname = 'test_pgstat_subxact';
 n_tup_ins 
-----------
 700       
(1 row)
SELECT count(*) FROM test_pgstat_subxact;
 count 
-------
 200   
(1 row)

--
-- Test 5: Nested subtransactions — RELEASE merges into parent, ROLLBACK TO discards
--
CREATE TABLE test_pgstat_nested(id int) DISTRIBUTED BY (id);
CREATE

BEGIN;
BEGIN
INSERT INTO test_pgstat_nested SELECT i FROM generate_series(1, 100) i;
INSERT 100
SAVEPOINT sp1;
SAVEPOINT
INSERT INTO test_pgstat_nested SELECT i FROM generate_series(101, 200) i;
INSERT 100
SAVEPOINT sp2;
SAVEPOINT
INSERT INTO test_pgstat_nested SELECT i FROM generate_series(201, 300) i;
INSERT 100
RELEASE SAVEPOINT sp2;
RELEASE
ROLLBACK TO sp1;
ROLLBACK
COMMIT;
COMMIT
SELECT gp_stat_force_next_flush();
 gp_stat_force_next_flush 
--------------------------
                          
(1 row)

-- All 300 attempted inserts counted (100 outer + 100 sp1 + 100 sp2)
-- but only 100 rows remain (sp1 rollback discards sp1 and released sp2)
SELECT n_tup_ins FROM pg_stat_user_tables WHERE relname = 'test_pgstat_nested';
 n_tup_ins 
-----------
 300       
(1 row)
SELECT n_tup_ins FROM gp_stat_user_tables_summary WHERE relname = 'test_pgstat_nested';
 n_tup_ins 
-----------
 300       
(1 row)
SELECT count(*) FROM test_pgstat_nested;
 count 
-------
 100   
(1 row)

--
-- Test 6: Catalog (entry) table — QE doesn't crash on catalog DML
-- Catalog tables are filtered out (POLICYTYPE_ENTRY), so the stats
-- infrastructure should simply skip them without error.
--
CREATE FUNCTION test_pgstat_func() RETURNS void AS $$ BEGIN END; $$ LANGUAGE plpgsql;
CREATE
DROP FUNCTION test_pgstat_func();
DROP

--
-- Cleanup
--
DROP TABLE test_pgstat_dist;
DROP
DROP TABLE test_pgstat_repl;
DROP
DROP TABLE test_pgstat_xact;
DROP
DROP TABLE test_pgstat_subxact;
DROP
DROP TABLE test_pgstat_nested;
DROP

ALTER SYSTEM RESET autovacuum;
ALTER
SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t              
(1 row)