src/test/isolation2/input/autovacuum-analyze.source

-- Enable auto-ANALYZE only on AUTOVACUUM launcher. When set `autovacuum` to
-- true on Master, the launcher will take care of databases' analyze job on Master.
-- VACUUM anti-XID wraparounds of 'template0' is not changed.

-- Speed up test.
ALTER SYSTEM SET autovacuum_naptime = 5;
select * from pg_reload_conf();

--
-- Test1, sanity test to make sure auto-analyze works
--

-- Analyze all exists relations to prevent autoanalyze on them.
-- These actually don't have too much effort for preventing
-- autoanalyze on other tables, but it could reduce some of tables.
1: ANALYZE;

-- Prepare the table to be ANALYZEd
1: CREATE TABLE anatest (id bigint);

-- Track report gpstat on master
SELECT gp_inject_fault('gp_pgstat_report_on_master', 'suspend', '', '', 'anatest', 1, -1, 0, 1);
-- Track that we have updated the attributes stats in pg_statistic when finished
SELECT gp_inject_fault('analyze_finished_one_relation', 'skip', '', '', 'anatest', 1, -1, 0, 1);
-- Suspend the autovacuum worker from analyze before
SELECT gp_inject_fault('auto_vac_worker_after_report_activity', 'suspend', '', '', 'anatest', 1, -1, 0, 1);

1&: INSERT INTO anatest select i from generate_series(1, 1000) as i;

-- Wait until report pgstat on master
SELECT gp_wait_until_triggered_fault('gp_pgstat_report_on_master', 1, 1);
SELECT gp_inject_fault('gp_pgstat_report_on_master', 'reset', 1);

1<:

-- Wait until autovacuum is triggered
SELECT gp_wait_until_triggered_fault('auto_vac_worker_after_report_activity', 1, 1);
select datname, query from pg_stat_activity where query like '%ANALYZE%' and datname=current_database();
SELECT gp_inject_fault('auto_vac_worker_after_report_activity', 'reset', 1);

-- Wait until autovacuum worker updates pg_database
SELECT gp_wait_until_triggered_fault('analyze_finished_one_relation', 1, 1);
SELECT gp_inject_fault('analyze_finished_one_relation', 'reset', 1);

-- Should have statistic updated.
SELECT count(*) FROM pg_statistic where starelid = 'anatest'::regclass;
select relpages, reltuples from pg_class where oid = 'anatest'::regclass;


--
-- Test2, sanity test to make sure auto-analyze works on partition leaf tables
--

-- Prepare the table to be ANALYZEd
1: CREATE TABLE rankpart (id int, rank int, product int) DISTRIBUTED BY (id) PARTITION BY RANGE (rank) ( START (1) END (10) EVERY (2), DEFAULT PARTITION extra );

-- Track report gpstat on master
SELECT gp_inject_fault('gp_pgstat_report_on_master', 'suspend', '', '', 'rankpart_1_prt_extra', 1, -1, 0, 1);
-- Track that we have updated the attributes stats in pg_statistic when finished
SELECT gp_inject_fault('analyze_finished_one_relation', 'skip', '', '', 'rankpart_1_prt_5', 1, -1, 0, 1);
-- Suspend the autovacuum worker from analyze before
SELECT gp_inject_fault('auto_vac_worker_after_report_activity', 'suspend', '', '', 'rankpart_1_prt_extra', 1, -1, 0, 1);

-- No data inserted into rankpart_1_prt_6
1&: insert into rankpart select i, i % 8, i from generate_series(1, 1000)i;

-- Wait until report pgstat on master
SELECT gp_wait_until_triggered_fault('gp_pgstat_report_on_master', 1, 1);
SELECT gp_inject_fault('gp_pgstat_report_on_master', 'reset', 1);

1<:

-- Wait until autovacuum is triggered
SELECT gp_wait_until_triggered_fault('auto_vac_worker_after_report_activity', 1, 1);
select datname, query from pg_stat_activity where query like '%ANALYZE%' and datname=current_database();
SELECT gp_inject_fault('auto_vac_worker_after_report_activity', 'reset', 1);

-- Wait until autovacuum worker updates pg_database
SELECT gp_wait_until_triggered_fault('analyze_finished_one_relation', 1, 1);
SELECT gp_inject_fault('analyze_finished_one_relation', 'reset', 1);
SELECT pg_sleep(5);

-- Should have statistic updated.
SELECT count(*) FROM pg_statistic where starelid = 'rankpart_1_prt_extra'::regclass;
SELECT count(*) FROM pg_statistic where starelid = 'rankpart_1_prt_2'::regclass;
SELECT count(*) FROM pg_statistic where starelid = 'rankpart_1_prt_3'::regclass;
SELECT count(*) FROM pg_statistic where starelid = 'rankpart_1_prt_4'::regclass;
SELECT count(*) FROM pg_statistic where starelid = 'rankpart_1_prt_5'::regclass;
SELECT count(*) FROM pg_statistic where starelid = 'rankpart_1_prt_6'::regclass;
SELECT count(*) FROM pg_statistic where starelid = 'rankpart'::regclass;
select relpages, reltuples from pg_class where oid = 'rankpart_1_prt_extra'::regclass;
select relpages, reltuples from pg_class where oid = 'rankpart_1_prt_2'::regclass;
select relpages, reltuples from pg_class where oid = 'rankpart_1_prt_3'::regclass;
select relpages, reltuples from pg_class where oid = 'rankpart_1_prt_4'::regclass;
select relpages, reltuples from pg_class where oid = 'rankpart_1_prt_5'::regclass;
select relpages, reltuples from pg_class where oid = 'rankpart_1_prt_6'::regclass;
select relpages, reltuples from pg_class where oid = 'rankpart'::regclass;


--
-- Test3, if another transaction want to acquire lock which conflict with
-- auto-analyze's ShareUpdateExclusiveLock, the auto-analyze should abort.
--

1: CREATE TABLE anaabort(id int);

-- Track report gpstat on master
SELECT gp_inject_fault('gp_pgstat_report_on_master', 'suspend', '', '', 'anaabort', 1, -1, 0, 1);
-- Suspend the autovacuum worker from analyze before
SELECT gp_inject_fault('auto_vac_worker_after_report_activity', 'suspend', '', '', 'anaabort', 1, -1, 0, 1);
-- Suspend the autovacuum worker after holding ShareUpdateExclusiveLock.
SELECT gp_inject_fault('analyze_after_hold_lock', 'infinite_loop', '', '', 'anaabort', 1, -1, 0, 1);
-- Track the autovacuum worker abort for conflict ShareUpdateExclusiveLock.
SELECT gp_inject_fault('auto_vac_worker_abort', 'skip', '', '', 'anaabort', 1, -1, 0, 1);

1&: INSERT INTO anaabort select i from generate_series(1, 1000) as i;

-- Wait until report pgstat on master
SELECT gp_wait_until_triggered_fault('gp_pgstat_report_on_master', 1, 1);
SELECT gp_inject_fault('gp_pgstat_report_on_master', 'reset', 1);

1<:

-- Wait until autovacuum is triggered
SELECT gp_wait_until_triggered_fault('auto_vac_worker_after_report_activity', 1, 1);
select datname, query from pg_stat_activity where query like '%ANALYZE%' and datname=current_database();
SELECT gp_inject_fault('auto_vac_worker_after_report_activity', 'reset', 1);

-- Wait until autovacuum work hold ShareUpdateExclusiveLock.
SELECT gp_wait_until_triggered_fault('analyze_after_hold_lock', 1, 1);

-- Acquire EXCLUSIVE lock on the analyze table
1: BEGIN;
1&: LOCK TABLE anaabort in EXCLUSIVE mode;

-- The auto-analyze should abort
SELECT gp_wait_until_triggered_fault('auto_vac_worker_abort', 1, 1);
SELECT gp_inject_fault('auto_vac_worker_abort', 'reset', 1);
SELECT gp_inject_fault('analyze_after_hold_lock', 'reset', 1);

-- Get lock
1<:

-- Shouldn't have statistic updated.
SELECT count(*) FROM pg_statistic where starelid = 'anaabort'::regclass;
select relpages, reltuples from pg_class where oid = 'anaabort'::regclass;

1: END;

--
-- Test 4, auto-ANALYZE and auto_stats could work at the same time.
-- With `on_no_stats` mode, if insert into a empty table, ANALYZE gets triggered.
-- But this doesn't prevent auto-ANALYZE for the table. (Case 1)
-- And for `on_change` mode, if the modified tuples reach the auto_stats threshold,
-- auto_stats will get triggered, this also doesn't prevent auto-ANALYZE. (Case 2)
-- But, if not reach the auto_stats threshold, then only auto-ANALYZE will be considered. (Case 3)
-- More details, please refer to the Implementation part of
-- src/backend/postmaster/README.auto-ANALYZE.
--

-- Case 1 --
2: SET gp_autostats_mode = 'on_no_stats';

-- Prepare the table to be ANALYZEd
2: CREATE TABLE autostatstbl (id bigint);

-- Track that we have updated the attributes stats in pg_statistic when finished
SELECT gp_inject_fault('analyze_finished_one_relation', 'skip', '', '', 'autostatstbl', 1, -1, 0, 1);
-- Suspend the autovacuum worker from analyze before
SELECT gp_inject_fault('auto_vac_worker_after_report_activity', 'suspend', '', '', 'autostatstbl', 1, -1, 0, 1);

-- with auto_stats, the auto-ANALYZE still trigger
2: INSERT INTO autostatstbl select i from generate_series(1, 1000) as i;
2: select pg_sleep(0.77); -- Force pgstat_report_stat() to send tabstat.

-- auto_stats executed but auto-ANALYZE not execute yet since we suspend before finish ANALYZE.
SELECT count(*) FROM pg_statistic where starelid = 'autostatstbl'::regclass;
select relpages, reltuples from pg_class where oid = 'autostatstbl'::regclass;
-- expect analyze_count = 1, autoanalyze_count = 0, and n_mod_since_analyze = 1000 since ANALYZE executed
-- in same transaction for the insert statement.
select analyze_count, autoanalyze_count, n_mod_since_analyze from pg_stat_all_tables_internal where relname = 'autostatstbl';

-- Wait until autovacuum is triggered
SELECT gp_wait_until_triggered_fault('auto_vac_worker_after_report_activity', 1, 1);
select datname, query from pg_stat_activity where query like '%ANALYZE%' and datname=current_database();
SELECT gp_inject_fault('auto_vac_worker_after_report_activity', 'reset', 1);

-- Wait until autovacuum worker updates pg_database
SELECT gp_wait_until_triggered_fault('analyze_finished_one_relation', 1, 1);
SELECT gp_inject_fault('analyze_finished_one_relation', 'reset', 1);

-- we can see the auto-ANALYZE finished
select relpages, reltuples from pg_class where oid = 'autostatstbl'::regclass;
-- expect analyze_count = 1, autoanalyze_count = 1, and n_mod_since_analyze = 0
select analyze_count, autoanalyze_count, n_mod_since_analyze from pg_stat_all_tables_internal where relname = 'autostatstbl';

-- Case 2 --
-- with auto_stats 'on_change' mode, the auto-ANALYZE still trigger
2: SET gp_autostats_mode = 'on_change';
2: SET gp_autostats_on_change_threshold = 500;

-- Track that we have updated the attributes stats in pg_statistic when finished
SELECT gp_inject_fault('analyze_finished_one_relation', 'skip', '', '', 'autostatstbl', 1, -1, 0, 1);
-- Suspend the autovacuum worker from analyze before
SELECT gp_inject_fault('auto_vac_worker_after_report_activity', 'suspend', '', '', 'autostatstbl', 1, -1, 0, 1);

2: INSERT INTO autostatstbl select i from generate_series(1001, 2000) as i;
2: select pg_sleep(0.77); -- Force pgstat_report_stat() to send tabstat.

-- auto_stats executed but auto-ANALYZE not execute yet since we suspend before finish ANALYZE.
select relpages, reltuples from pg_class where oid = 'autostatstbl'::regclass;
-- expect analyze_count = 2, autoanalyze_count = 1, and n_mod_since_analyze = 1000 since ANALYZE executed
-- in same transaction for the insert statement.
select analyze_count, autoanalyze_count, n_mod_since_analyze from pg_stat_all_tables_internal where relname = 'autostatstbl';

-- Wait until autovacuum is triggered
SELECT gp_wait_until_triggered_fault('auto_vac_worker_after_report_activity', 1, 1);
select datname, query from pg_stat_activity where query like '%ANALYZE%' and datname=current_database();
SELECT gp_inject_fault('auto_vac_worker_after_report_activity', 'reset', 1);

-- Wait until autovacuum worker updates pg_database
SELECT gp_wait_until_triggered_fault('analyze_finished_one_relation', 1, 1);
SELECT gp_inject_fault('analyze_finished_one_relation', 'reset', 1);

-- we can see the auto-ANALYZE finished, check statistic and analyze count
select relpages, reltuples from pg_class where oid = 'autostatstbl'::regclass;
-- expect analyze_count = 2, autoanalyze_count = 2, and n_mod_since_analyze = 0
select analyze_count, autoanalyze_count, n_mod_since_analyze from pg_stat_all_tables_internal where relname = 'autostatstbl';


-- Case 3 --
-- reset the threshold, so auto_stats will not be triggered and then auto-ANALYZE will be triggered.
2: RESET gp_autostats_on_change_threshold;

-- Track that we have updated the attributes stats in pg_statistic when finished
SELECT gp_inject_fault('analyze_finished_one_relation', 'skip', '', '', 'autostatstbl', 1, -1, 0, 1);
-- Suspend the autovacuum worker from analyze before
SELECT gp_inject_fault('auto_vac_worker_after_report_activity', 'suspend', '', '', 'autostatstbl', 1, -1, 0, 1);

2: INSERT INTO autostatstbl select i from generate_series(2001, 3000) as i;
2: select pg_sleep(0.77); -- Force pgstat_report_stat() to send tabstat.

-- auto_stats should not executed and auto-ANALYZE not execute yet since we suspend before finish ANALYZE.
select relpages, reltuples from pg_class where oid = 'autostatstbl'::regclass;
-- expect analyze_count = 2, autoanalyze_count = 2, and n_mod_since_analyze = 1000 since ANALYZE executed
-- in same transaction for the insert statement.
select analyze_count, autoanalyze_count, n_mod_since_analyze from pg_stat_all_tables_internal where relname = 'autostatstbl';

-- Wait until autovacuum is triggered
SELECT gp_wait_until_triggered_fault('auto_vac_worker_after_report_activity', 1, 1);
select datname, query from pg_stat_activity where query like '%ANALYZE%' and datname=current_database();
SELECT gp_inject_fault('auto_vac_worker_after_report_activity', 'reset', 1);

-- Wait until autovacuum worker updates pg_database
SELECT gp_wait_until_triggered_fault('analyze_finished_one_relation', 1, 1);
SELECT gp_inject_fault('analyze_finished_one_relation', 'reset', 1);

-- we can see the auto-ANALYZE finished, check statistic and analyze count
select relpages, reltuples from pg_class where oid = 'autostatstbl'::regclass;
-- expect analyze_count = 2, autoanalyze_count = 3, and n_mod_since_analyze = 0 since ANALYZE executed
select analyze_count, autoanalyze_count, n_mod_since_analyze from pg_stat_all_tables_internal where relname = 'autostatstbl';

-- Reset GUCs.
ALTER SYSTEM RESET autovacuum_naptime;
select * from pg_reload_conf();