This is an automated email from the ASF dual-hosted git repository. yjhjstz pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit a2d8a0ec6bd004c9bc1acefbc023e7d241bc8bd9 Author: Yao Wang <[email protected]> AuthorDate: Wed Oct 19 10:41:53 2022 +0800 Tests of VACUUM (SKIP_LOCKED) (#14265) On PG12 an option SKIP_LOCKED was added to VACUUM and ANALYZE to indicate whether we need to skip tables when a lock cannot be acquired at the time. However, relevant tests don't cover all the scenarios on GPDB. This PR is to add more tests to cover the scenarios. --- src/test/isolation/expected/vacuum-skip-locked.out | 148 +++++++++++++++++++++ src/test/isolation/specs/vacuum-skip-locked.spec | 66 +++++++-- .../expected/vacuum_skip_locked_onseg.out | 53 ++++++++ src/test/isolation2/isolation2_schedule | 1 + .../isolation2/sql/vacuum_skip_locked_onseg.sql | 41 ++++++ 5 files changed, 295 insertions(+), 14 deletions(-) diff --git a/src/test/isolation/expected/vacuum-skip-locked.out b/src/test/isolation/expected/vacuum-skip-locked.out index 20f79649dc..f0aab2a86b 100644 --- a/src/test/isolation/expected/vacuum-skip-locked.out +++ b/src/test/isolation/expected/vacuum-skip-locked.out @@ -147,3 +147,151 @@ step vac_full_all_parts: VACUUM (SKIP_LOCKED, FULL) parted; step commit: COMMIT; + +starting permutation: lock_share_ao vac_specified_ao commit +step lock_share_ao: + BEGIN; + LOCK part1_ao IN SHARE MODE; + +s2: WARNING: skipping vacuum of "part1_ao" --- lock not available +step vac_specified_ao: VACUUM (SKIP_LOCKED) part1_ao, part2_ao; +step commit: + COMMIT; + + +starting permutation: lock_share_ao vac_all_parts_ao commit +step lock_share_ao: + BEGIN; + LOCK part1_ao IN SHARE MODE; + +step vac_all_parts_ao: VACUUM (SKIP_LOCKED) parted_ao; +step commit: + COMMIT; + + +starting permutation: lock_share_ao analyze_specified_ao commit +step lock_share_ao: + BEGIN; + LOCK part1_ao IN SHARE MODE; + +s2: WARNING: skipping analyze of "part1_ao" --- lock not available +step analyze_specified_ao: ANALYZE (SKIP_LOCKED) part1_ao, part2_ao; +step commit: + COMMIT; + + +starting permutation: lock_share_ao analyze_all_parts_ao commit +step lock_share_ao: + BEGIN; + LOCK part1_ao IN SHARE MODE; + +step analyze_all_parts_ao: ANALYZE (SKIP_LOCKED) parted_ao; +step commit: + COMMIT; + + +starting permutation: lock_share_ao vac_analyze_specified_ao commit +step lock_share_ao: + BEGIN; + LOCK part1_ao IN SHARE MODE; + +s2: WARNING: skipping vacuum of "part1_ao" --- lock not available +step vac_analyze_specified_ao: VACUUM (ANALYZE, SKIP_LOCKED) part1_ao, part2_ao; +step commit: + COMMIT; + + +starting permutation: lock_share_ao vac_analyze_all_parts_ao commit +step lock_share_ao: + BEGIN; + LOCK part1_ao IN SHARE MODE; + +step vac_analyze_all_parts_ao: VACUUM (ANALYZE, SKIP_LOCKED) parted_ao; +step commit: + COMMIT; + + +starting permutation: lock_share_ao vac_full_specified_ao commit +step lock_share_ao: + BEGIN; + LOCK part1_ao IN SHARE MODE; + +s2: WARNING: skipping vacuum of "part1_ao" --- lock not available +step vac_full_specified_ao: VACUUM (SKIP_LOCKED, FULL) part1_ao, part2_ao; +step commit: + COMMIT; + + +starting permutation: lock_share_ao vac_full_all_parts_ao commit +step lock_share_ao: + BEGIN; + LOCK part1_ao IN SHARE MODE; + +step vac_full_all_parts_ao: VACUUM (SKIP_LOCKED, FULL) parted_ao; +step commit: + COMMIT; + + +starting permutation: lock_access_exclusive_ao vac_specified_ao commit +step lock_access_exclusive_ao: + BEGIN; + LOCK part1_ao IN ACCESS EXCLUSIVE MODE; + +s2: WARNING: skipping vacuum of "part1_ao" --- lock not available +step vac_specified_ao: VACUUM (SKIP_LOCKED) part1_ao, part2_ao; +step commit: + COMMIT; + + +starting permutation: lock_access_exclusive_ao vac_all_parts_ao commit +step lock_access_exclusive_ao: + BEGIN; + LOCK part1_ao IN ACCESS EXCLUSIVE MODE; + +step vac_all_parts_ao: VACUUM (SKIP_LOCKED) parted_ao; +step commit: + COMMIT; + + +starting permutation: lock_access_exclusive_ao analyze_specified_ao commit +step lock_access_exclusive_ao: + BEGIN; + LOCK part1_ao IN ACCESS EXCLUSIVE MODE; + +s2: WARNING: skipping analyze of "part1_ao" --- lock not available +step analyze_specified_ao: ANALYZE (SKIP_LOCKED) part1_ao, part2_ao; +step commit: + COMMIT; + + +starting permutation: lock_access_exclusive_ao vac_analyze_specified_ao commit +step lock_access_exclusive_ao: + BEGIN; + LOCK part1_ao IN ACCESS EXCLUSIVE MODE; + +s2: WARNING: skipping vacuum of "part1_ao" --- lock not available +step vac_analyze_specified_ao: VACUUM (ANALYZE, SKIP_LOCKED) part1_ao, part2_ao; +step commit: + COMMIT; + + +starting permutation: lock_access_exclusive_ao vac_full_specified_ao commit +step lock_access_exclusive_ao: + BEGIN; + LOCK part1_ao IN ACCESS EXCLUSIVE MODE; + +s2: WARNING: skipping vacuum of "part1_ao" --- lock not available +step vac_full_specified_ao: VACUUM (SKIP_LOCKED, FULL) part1_ao, part2_ao; +step commit: + COMMIT; + + +starting permutation: lock_access_exclusive_ao vac_full_all_parts_ao commit +step lock_access_exclusive_ao: + BEGIN; + LOCK part1_ao IN ACCESS EXCLUSIVE MODE; + +step vac_full_all_parts_ao: VACUUM (SKIP_LOCKED, FULL) parted_ao; +step commit: + COMMIT; + diff --git a/src/test/isolation/specs/vacuum-skip-locked.spec b/src/test/isolation/specs/vacuum-skip-locked.spec index ce49dd44de..9b3ea7bfae 100644 --- a/src/test/isolation/specs/vacuum-skip-locked.spec +++ b/src/test/isolation/specs/vacuum-skip-locked.spec @@ -10,11 +10,17 @@ setup ALTER TABLE part1 SET (autovacuum_enabled = false); CREATE TABLE part2 PARTITION OF parted FOR VALUES IN (2); ALTER TABLE part2 SET (autovacuum_enabled = false); + + CREATE TABLE parted_ao (a INT) using ao_column + distributed by (a) PARTITION BY LIST (a); + CREATE TABLE part1_ao PARTITION OF parted_ao FOR VALUES IN (1); + CREATE TABLE part2_ao PARTITION OF parted_ao FOR VALUES IN (2); } teardown { DROP TABLE IF EXISTS parted; + DROP TABLE IF EXISTS parted_ao; } session s1 @@ -32,16 +38,34 @@ step commit { COMMIT; } +step "lock_share_ao" +{ + BEGIN; + LOCK part1_ao IN SHARE MODE; +} +step "lock_access_exclusive_ao" +{ + BEGIN; + LOCK part1_ao IN ACCESS EXCLUSIVE MODE; +} -session s2 -step vac_specified { VACUUM (SKIP_LOCKED) part1, part2; } -step vac_all_parts { VACUUM (SKIP_LOCKED) parted; } -step analyze_specified { ANALYZE (SKIP_LOCKED) part1, part2; } -step analyze_all_parts { ANALYZE (SKIP_LOCKED) parted; } -step vac_analyze_specified { VACUUM (ANALYZE, SKIP_LOCKED) part1, part2; } -step vac_analyze_all_parts { VACUUM (ANALYZE, SKIP_LOCKED) parted; } -step vac_full_specified { VACUUM (SKIP_LOCKED, FULL) part1, part2; } -step vac_full_all_parts { VACUUM (SKIP_LOCKED, FULL) parted; } +session "s2" +step "vac_specified" { VACUUM (SKIP_LOCKED) part1, part2; } +step "vac_all_parts" { VACUUM (SKIP_LOCKED) parted; } +step "analyze_specified" { ANALYZE (SKIP_LOCKED) part1, part2; } +step "analyze_all_parts" { ANALYZE (SKIP_LOCKED) parted; } +step "vac_analyze_specified" { VACUUM (ANALYZE, SKIP_LOCKED) part1, part2; } +step "vac_analyze_all_parts" { VACUUM (ANALYZE, SKIP_LOCKED) parted; } +step "vac_full_specified" { VACUUM (SKIP_LOCKED, FULL) part1, part2; } +step "vac_full_all_parts" { VACUUM (SKIP_LOCKED, FULL) parted; } +step "vac_specified_ao" { VACUUM (SKIP_LOCKED) part1_ao, part2_ao; } +step "vac_all_parts_ao" { VACUUM (SKIP_LOCKED) parted_ao; } +step "analyze_specified_ao" { ANALYZE (SKIP_LOCKED) part1_ao, part2_ao; } +step "analyze_all_parts_ao" { ANALYZE (SKIP_LOCKED) parted_ao; } +step "vac_analyze_specified_ao" { VACUUM (ANALYZE, SKIP_LOCKED) part1_ao, part2_ao; } +step "vac_analyze_all_parts_ao" { VACUUM (ANALYZE, SKIP_LOCKED) parted_ao; } +step "vac_full_specified_ao" { VACUUM (SKIP_LOCKED, FULL) part1_ao, part2_ao; } +step "vac_full_all_parts_ao" { VACUUM (SKIP_LOCKED, FULL) parted_ao; } permutation lock_share vac_specified commit permutation lock_share vac_all_parts commit @@ -59,9 +83,23 @@ permutation lock_access_exclusive analyze_specified commit # different from upstream. Even in PostgreSQL, the documentation for # SKIP_LOCKED says that it may still block if it needs to acquire sample # rows from the partitions. -#permutation lock_access_exclusive analyze_all_parts commit -permutation lock_access_exclusive vac_analyze_specified commit -#permutation lock_access_exclusive vac_analyze_all_parts commit -permutation lock_access_exclusive vac_full_specified commit -permutation lock_access_exclusive vac_full_all_parts commit +#permutation "lock_access_exclusive" "analyze_all_parts" "commit" +permutation "lock_access_exclusive" "vac_analyze_specified" "commit" +#permutation "lock_access_exclusive" "vac_analyze_all_parts" "commit" +permutation "lock_access_exclusive" "vac_full_specified" "commit" +permutation "lock_access_exclusive" "vac_full_all_parts" "commit" +permutation "lock_share_ao" "vac_specified_ao" "commit" +permutation "lock_share_ao" "vac_all_parts_ao" "commit" +permutation "lock_share_ao" "analyze_specified_ao" "commit" +permutation "lock_share_ao" "analyze_all_parts_ao" "commit" +permutation "lock_share_ao" "vac_analyze_specified_ao" "commit" +permutation "lock_share_ao" "vac_analyze_all_parts_ao" "commit" +permutation "lock_share_ao" "vac_full_specified_ao" "commit" +permutation "lock_share_ao" "vac_full_all_parts_ao" "commit" +permutation "lock_access_exclusive_ao" "vac_specified_ao" "commit" +permutation "lock_access_exclusive_ao" "vac_all_parts_ao" "commit" +permutation "lock_access_exclusive_ao" "analyze_specified_ao" "commit" +permutation "lock_access_exclusive_ao" "vac_analyze_specified_ao" "commit" +permutation "lock_access_exclusive_ao" "vac_full_specified_ao" "commit" +permutation "lock_access_exclusive_ao" "vac_full_all_parts_ao" "commit" diff --git a/src/test/isolation2/expected/vacuum_skip_locked_onseg.out b/src/test/isolation2/expected/vacuum_skip_locked_onseg.out new file mode 100644 index 0000000000..243f663a27 --- /dev/null +++ b/src/test/isolation2/expected/vacuum_skip_locked_onseg.out @@ -0,0 +1,53 @@ +-- Test VACUUM with SKIP_LOCKED +-- The test focuses on the vacuum behavior when the table is locked +-- on segments. There is another test vacuum-skip-locked in +-- isolation dir which focuses on regular test cases (table is locked +-- on master). + +1: CREATE TABLE vacuum_tbl (c1 int) DISTRIBUTED BY (c1); +CREATE + +-- Connect to seg #0 in utility mode, lock the table in share mode +0U: BEGIN; +BEGIN +0U: LOCK vacuum_tbl IN SHARE MODE; +LOCK + +-- Issue vacuum with SKIP_LOCKED option +-- Note that some ANALYZE options are disabled here because the ANALYZE +-- try to acquire sample on segments and block. It is acceptable on +-- GPDB. See the comments in isolation/specs/vacuum-skip-locked.spec. + +2: VACUUM (SKIP_LOCKED) vacuum_tbl; +VACUUM +--2: ANALYZE (SKIP_LOCKED) vacuum_tbl; +--2: VACUUM (ANALYZE, SKIP_LOCKED) vacuum_tbl; +2: VACUUM (SKIP_LOCKED, FULL) vacuum_tbl; +VACUUM + +0U: COMMIT; +COMMIT + +-- Connect to seg #0 in utility mode, lock the table in exclusive mode +0U: BEGIN; +BEGIN +0U: LOCK vacuum_tbl IN ACCESS EXCLUSIVE MODE; +LOCK + +-- Issue vacuum with SKIP_LOCKED option +-- Note that some ANALYZE options are disabled here because the ANALYZE +-- try to acquire sample on segments and block. It is acceptable on +-- GPDB. See the comments in isolation/specs/vacuum-skip-locked.spec. + +2: VACUUM (SKIP_LOCKED) vacuum_tbl; +VACUUM +--2: ANALYZE (SKIP_LOCKED) vacuum_tbl; +--2: VACUUM (ANALYZE, SKIP_LOCKED) vacuum_tbl; +2: VACUUM (SKIP_LOCKED, FULL) vacuum_tbl; +VACUUM + +0U: COMMIT; +COMMIT + +1: DROP TABLE IF EXISTS vacuum_tbl; +DROP diff --git a/src/test/isolation2/isolation2_schedule b/src/test/isolation2/isolation2_schedule index 57e3b76252..594c413835 100644 --- a/src/test/isolation2/isolation2_schedule +++ b/src/test/isolation2/isolation2_schedule @@ -6,6 +6,7 @@ test: check_gxid test: checkpoint_dtx_info test: autovacuum-analyze +test: vacuum_skip_locked_onseg test: lockmodes # test: pg_rewind_fail_missing_xlog test: prepared_xact_deadlock_pg_rewind diff --git a/src/test/isolation2/sql/vacuum_skip_locked_onseg.sql b/src/test/isolation2/sql/vacuum_skip_locked_onseg.sql new file mode 100644 index 0000000000..3de48f116a --- /dev/null +++ b/src/test/isolation2/sql/vacuum_skip_locked_onseg.sql @@ -0,0 +1,41 @@ +-- Test VACUUM with SKIP_LOCKED +-- The test focuses on the vacuum behavior when the table is locked +-- on segments. There is another test vacuum-skip-locked in +-- isolation dir which focuses on regular test cases (table is locked +-- on master). + +1: CREATE TABLE vacuum_tbl (c1 int) DISTRIBUTED BY (c1); + +-- Connect to seg #0 in utility mode, lock the table in share mode +0U: BEGIN; +0U: LOCK vacuum_tbl IN SHARE MODE; + +-- Issue vacuum with SKIP_LOCKED option +-- Note that some ANALYZE options are disabled here because the ANALYZE +-- try to acquire sample on segments and block. It is acceptable on +-- GPDB. See the comments in isolation/specs/vacuum-skip-locked.spec. + +2: VACUUM (SKIP_LOCKED) vacuum_tbl; +--2: ANALYZE (SKIP_LOCKED) vacuum_tbl; +--2: VACUUM (ANALYZE, SKIP_LOCKED) vacuum_tbl; +2: VACUUM (SKIP_LOCKED, FULL) vacuum_tbl; + +0U: COMMIT; + +-- Connect to seg #0 in utility mode, lock the table in exclusive mode +0U: BEGIN; +0U: LOCK vacuum_tbl IN ACCESS EXCLUSIVE MODE; + +-- Issue vacuum with SKIP_LOCKED option +-- Note that some ANALYZE options are disabled here because the ANALYZE +-- try to acquire sample on segments and block. It is acceptable on +-- GPDB. See the comments in isolation/specs/vacuum-skip-locked.spec. + +2: VACUUM (SKIP_LOCKED) vacuum_tbl; +--2: ANALYZE (SKIP_LOCKED) vacuum_tbl; +--2: VACUUM (ANALYZE, SKIP_LOCKED) vacuum_tbl; +2: VACUUM (SKIP_LOCKED, FULL) vacuum_tbl; + +0U: COMMIT; + +1: DROP TABLE IF EXISTS vacuum_tbl; --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
