src/test/isolation2/sql/vacuum_progress_row.sql

-- @Description Test to ensure we correctly report progress in
-- pg_stat_progress_create_vacuum for append-optimized tables.

set default_table_access_method=ao_row;

-- Setup the append-optimized table to be vacuumed
DROP TABLE IF EXISTS vacuum_progress_ao_row;
CREATE TABLE vacuum_progress_ao_row(i int, j int);

-- Add two indexes to be vacuumed as well
CREATE INDEX on vacuum_progress_ao_row(i);
CREATE INDEX on vacuum_progress_ao_row(j);

-- Insert all tuples to seg1 from two current sessions so that data are stored
-- in two segment files.
1: BEGIN;
2: BEGIN;
1: INSERT INTO vacuum_progress_ao_row SELECT 0, i FROM generate_series(1, 100000) i;
2: INSERT INTO vacuum_progress_ao_row SELECT 0, i FROM generate_series(1, 100000) i;
-- Commit so that the logical EOF of segno 2 is non-zero.
2: COMMIT;
2: BEGIN;
2: INSERT INTO vacuum_progress_ao_row SELECT 0, i FROM generate_series(1, 100000) i;
-- Abort so that segno 2 has dead tuples after its logical EOF
2: ABORT;
2q:
-- Abort so that segno 1 has logical EOF = 0.
1: ABORT;

-- Also delete half of the tuples evenly before the EOF of segno 2.
DELETE FROM vacuum_progress_ao_row where j % 2 = 0;

-- Lookup pg_class and collected stats view before VACUUM
SELECT relpages, reltuples, relallvisible FROM pg_class where relname = 'vacuum_progress_ao_row';
SELECT n_live_tup, n_dead_tup, last_vacuum, vacuum_count FROM pg_stat_all_tables WHERE relname = 'vacuum_progress_ao_row';

-- Perform VACUUM and observe the progress

-- Suspend execution at pre-cleanup phase after truncating both segfiles to their logical EOF.
SELECT gp_inject_fault('appendonly_after_truncate_segment_file', 'suspend', '', '', '', 2, 2, 0, dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';

1: set Debug_appendonly_print_compaction to on;
1&: VACUUM vacuum_progress_ao_row;
SELECT gp_wait_until_triggered_fault('appendonly_after_truncate_segment_file', 2, dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
-- We are in pre_cleanup phase and some blocks should've been vacuumed by now
1U: select relid::regclass as relname, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count, max_dead_tuples, num_dead_tuples from pg_stat_progress_vacuum;

-- Resume execution and suspend again in the middle of compact phase
SELECT gp_inject_fault('appendonly_insert', 'suspend', '', '', '', 200, 200, 0, dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
SELECT gp_inject_fault('appendonly_after_truncate_segment_file', 'reset', dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
SELECT gp_wait_until_triggered_fault('appendonly_insert', 200, dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
-- We are in compact phase. num_dead_tuples should increase as we move and count tuples, one by one.
1U: select relid::regclass as relname, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count, max_dead_tuples, num_dead_tuples from pg_stat_progress_vacuum;

-- Resume execution and suspend again after compacting all segfiles
SELECT gp_inject_fault('vacuum_ao_after_compact', 'suspend', dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
SELECT gp_inject_fault('appendonly_insert', 'reset', dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
SELECT gp_wait_until_triggered_fault('vacuum_ao_after_compact', 1, dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
-- After compacting all segfiles we expect 50000 dead tuples
1U: select relid::regclass as relname, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count, max_dead_tuples, num_dead_tuples from pg_stat_progress_vacuum;

-- Resume execution and entering post_cleaup phase, suspend at the end of it.
SELECT gp_inject_fault('vacuum_ao_post_cleanup_end', 'suspend', dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
SELECT gp_inject_fault('vacuum_ao_after_compact', 'reset', dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
SELECT gp_wait_until_triggered_fault('vacuum_ao_post_cleanup_end', 1, dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
-- We should have skipped recycling the awaiting drop segment because the segment was still visible to the SELECT gp_wait_until_triggered_fault query.
1U: select relid::regclass as relname, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count, max_dead_tuples, num_dead_tuples from pg_stat_progress_vacuum;
SELECT gp_inject_fault('vacuum_ao_post_cleanup_end', 'reset', dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
1<:

-- pg_class and collected stats view should be updated after the 1st VACUUM
1U: SELECT wait_until_dead_tup_change_to('vacuum_progress_ao_row'::regclass::oid, 0);
SELECT relpages, reltuples, relallvisible FROM pg_class where relname = 'vacuum_progress_ao_row';
SELECT n_live_tup, n_dead_tup, last_vacuum is not null as has_last_vacuum, vacuum_count FROM pg_stat_all_tables WHERE relname = 'vacuum_progress_ao_row';

-- Perform VACUUM again to recycle the remaining awaiting drop segment marked by the previous run.
SELECT gp_inject_fault('vacuum_ao_after_index_delete', 'suspend', dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
1&: VACUUM vacuum_progress_ao_row;
-- Resume execution and entering pre_cleanup phase, suspend at vacuuming indexes.
SELECT gp_inject_fault('vacuum_ao_after_compact', 'reset', dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
SELECT gp_wait_until_triggered_fault('vacuum_ao_after_index_delete', 1, dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
-- We are in vacuuming indexes phase (part of ao pre_cleanup phase), index_vacuum_count should increase to 1.
1U: select relid::regclass as relname, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count, max_dead_tuples, num_dead_tuples from pg_stat_progress_vacuum;

-- Resume execution and moving on to truncate segments that were marked as AWAITING_DROP, there should be only 1.
SELECT gp_inject_fault('appendonly_after_truncate_segment_file', 'suspend', dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
SELECT gp_inject_fault('vacuum_ao_after_index_delete', 'reset', dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
SELECT gp_wait_until_triggered_fault('appendonly_after_truncate_segment_file', 1, dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
-- We are in post_cleanup phase and should have truncated the old segfile. Both indexes should be vacuumed by now, and heap_blks_vacuumed should also increased
1U: select relid::regclass as relname, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count, max_dead_tuples, num_dead_tuples from pg_stat_progress_vacuum;

SELECT gp_inject_fault('appendonly_after_truncate_segment_file', 'reset', dbid) FROM gp_segment_configuration WHERE content = 1 AND role = 'p';
1<:

-- Vacuum has finished, nothing should show up in the progress view.
1U: select relid::regclass as relname, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count, max_dead_tuples, num_dead_tuples from pg_stat_progress_vacuum;

-- pg_class and collected stats view should be updated after the 2nd VACUUM
1U: SELECT wait_until_dead_tup_change_to('vacuum_progress_ao_row'::regclass::oid, 0);
SELECT relpages, reltuples, relallvisible FROM pg_class where relname = 'vacuum_progress_ao_row';
SELECT n_live_tup, n_dead_tup, last_vacuum is not null as has_last_vacuum, vacuum_count FROM pg_stat_all_tables WHERE relname = 'vacuum_progress_ao_row';

-- open if system views gp_stat_progress_vacuum* are enabled
--1q:
---- Test vacuum worker process is changed at post-cleanup phase due to mirror down.
---- Current behavior is it will clear previous compact phase num_dead_tuples in post-cleanup
---- phase (at injecting point vacuum_ao_post_cleanup_end), which is different from above case
---- in which vacuum worker isn't changed.
--ALTER SYSTEM SET gp_fts_mark_mirror_down_grace_period to 10;
--ALTER SYSTEM SET gp_fts_probe_interval to 10;
--SELECT gp_segment_id, pg_reload_conf() FROM gp_id UNION SELECT gp_segment_id, pg_reload_conf() FROM gp_dist_random('gp_id');
--
--DROP TABLE IF EXISTS vacuum_progress_ao_row;
--CREATE TABLE vacuum_progress_ao_row(i int, j int);
--CREATE INDEX on vacuum_progress_ao_row(i);
--CREATE INDEX on vacuum_progress_ao_row(j);
--1: BEGIN;
--2: BEGIN;
--1: INSERT INTO vacuum_progress_ao_row SELECT i, i FROM generate_series(1, 100000) i;
--2: INSERT INTO vacuum_progress_ao_row SELECT i, i FROM generate_series(1, 100000) i;
--2: COMMIT;
--2: BEGIN;
--2: INSERT INTO vacuum_progress_ao_row SELECT i, i FROM generate_series(1, 100000) i;
--2: ABORT;
--2q:
--1: ABORT;
--DELETE FROM vacuum_progress_ao_row where j % 2 = 0;
--
---- Suspend execution at the end of compact phase.
--2: SELECT gp_inject_fault('vacuum_ao_after_compact', 'suspend', dbid) FROM gp_segment_configuration WHERE content > -1 AND role = 'p';
--
--1: set debug_appendonly_print_compaction to on;
--1&: vacuum vacuum_progress_ao_row;
--
--2: SELECT gp_wait_until_triggered_fault('vacuum_ao_after_compact', 3, dbid) FROM gp_segment_configuration WHERE content > -1 AND role = 'p';
--
---- Non-zero progressing data num_dead_tuples is showed up.
--select gp_segment_id, relid::regclass as relname, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count, max_dead_tuples, num_dead_tuples from gp_stat_progress_vacuum where gp_segment_id > -1;
--select relid::regclass as relname, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count, max_dead_tuples, num_dead_tuples from gp_stat_progress_vacuum_summary;
--
---- Resume execution of compact phase and block at syncrep.
--2: SELECT gp_inject_fault_infinite('wal_sender_loop', 'suspend', dbid) FROM gp_segment_configuration WHERE role = 'p' and content = 1;
--2: SELECT gp_inject_fault('vacuum_ao_after_compact', 'reset', dbid) FROM gp_segment_configuration WHERE content > -1 AND role = 'p';
---- stop the mirror should turn off syncrep
--2: SELECT pg_ctl(datadir, 'stop', 'immediate') FROM gp_segment_configuration WHERE content=1 AND role = 'm';
--
---- Resume walsender to detect mirror down and suspend at the beginning
---- of post-cleanup taken over by a new vacuum worker.
--2: SELECT gp_inject_fault('vacuum_worker_changed', 'suspend', dbid) FROM gp_segment_configuration WHERE content > -1 AND role = 'p';
---- resume walsender and let it exit so that mirror stop can be detected
--2: SELECT gp_inject_fault_infinite('wal_sender_loop', 'reset', dbid) FROM gp_segment_configuration WHERE role = 'p' and content = 1;
---- Ensure we enter into the target logic which stops cumulative data but
---- initializes a new vacrelstats at the beginning of post-cleanup phase.
---- Also all segments should reach to the same "vacuum_worker_changed" point
---- due to FTS version being changed.
--2: SELECT gp_wait_until_triggered_fault('vacuum_worker_changed', 1, dbid) FROM gp_segment_configuration WHERE content > -1 AND role = 'p';
---- now seg1's mirror is marked as down
--2: SELECT content, role, preferred_role, mode, status FROM gp_segment_configuration WHERE content > -1;
--
---- Resume execution and entering post_cleaup phase, suspend at the end of it.
--2: SELECT gp_inject_fault('vacuum_ao_post_cleanup_end', 'suspend', dbid) FROM gp_segment_configuration WHERE content > -1 AND role = 'p';
--2: SELECT gp_inject_fault('vacuum_worker_changed', 'reset', dbid) FROM gp_segment_configuration WHERE content > -1 AND role = 'p';
--2: SELECT gp_wait_until_triggered_fault('vacuum_ao_post_cleanup_end', 1, dbid) FROM gp_segment_configuration WHERE content > -1 AND role = 'p';
--
---- The previous collected num_dead_tuples in compact phase is zero.
--select gp_segment_id, relid::regclass as relname, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count, max_dead_tuples, num_dead_tuples from gp_stat_progress_vacuum where gp_segment_id > -1;
--select relid::regclass as relname, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count, max_dead_tuples, num_dead_tuples from gp_stat_progress_vacuum_summary;
--
--2: SELECT gp_inject_fault('vacuum_ao_post_cleanup_end', 'reset', dbid) FROM gp_segment_configuration WHERE content > -1 AND role = 'p';
--
--1<:
--
---- restore environment
--1: reset debug_appendonly_print_compaction;
--
--2: SELECT pg_ctl_start(datadir, port) FROM gp_segment_configuration WHERE role = 'm' AND content = 1;
--2: SELECT wait_until_all_segments_synchronized();
--
---- Cleanup
--SELECT gp_inject_fault_infinite('all', 'reset', dbid) FROM gp_segment_configuration;
--reset Debug_appendonly_print_compaction;
--reset default_table_access_method;
--ALTER SYSTEM RESET gp_fts_mark_mirror_down_grace_period;
--ALTER SYSTEM RESET gp_fts_probe_interval;
--SELECT gp_segment_id, pg_reload_conf() FROM gp_id UNION SELECT gp_segment_id, pg_reload_conf() FROM gp_dist_random('gp_id');
--