You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by yj...@apache.org on 2025/01/02 03:12:18 UTC
(cloudberry) 10/41: Tests of VACUUM (SKIP_LOCKED) (#14265)
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 <wa...@vmware.com>
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: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org