aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/ref/analyze.sgml19
-rw-r--r--doc/src/sgml/ref/vacuum.sgml21
-rw-r--r--src/backend/commands/vacuum.c26
-rw-r--r--src/backend/parser/gram.y12
-rw-r--r--src/include/nodes/parsenodes.h3
-rw-r--r--src/test/isolation/expected/vacuum-skip-locked.out171
-rw-r--r--src/test/isolation/isolation_schedule1
-rw-r--r--src/test/isolation/specs/vacuum-skip-locked.spec59
-rw-r--r--src/test/regress/expected/vacuum.out16
-rw-r--r--src/test/regress/sql/vacuum.sql12
10 files changed, 333 insertions, 7 deletions
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 10b3a9a6733..fea7f465215 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -27,6 +27,7 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
VERBOSE
+ SKIP_LOCKED
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -77,6 +78,24 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
</varlistentry>
<varlistentry>
+ <term><literal>SKIP_LOCKED</literal></term>
+ <listitem>
+ <para>
+ Specifies that <command>ANALYZE</command> should not wait for any
+ conflicting locks to be released when beginning work on a relation:
+ if a relation cannot be locked immediately without waiting, the relation
+ is skipped. Note that even with this option, <command>ANALYZE</command>
+ may still block when opening the relation's indexes or when acquiring
+ sample rows from partitions, table inheritance children, and some
+ types of foreign tables. Also, while <command>ANALYZE</command>
+ ordinarily processes all partitions of specified partitioned tables,
+ this option will cause <command>ANALYZE</command> to skip all
+ partitions if there is a conflicting lock on the partitioned table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">table_name</replaceable></term>
<listitem>
<para>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index b760e8ede18..fd911f57766 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -31,6 +31,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
VERBOSE
ANALYZE
DISABLE_PAGE_SKIPPING
+ SKIP_LOCKED
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -161,6 +162,26 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</varlistentry>
<varlistentry>
+ <term><literal>SKIP_LOCKED</literal></term>
+ <listitem>
+ <para>
+ Specifies that <command>VACUUM</command> should not wait for any
+ conflicting locks to be released when beginning work on a relation:
+ if a relation cannot be locked immediately without waiting, the relation
+ is skipped. Note that even with this option,
+ <command>VACUUM</command> may still block when opening the relation's
+ indexes. Additionally, <command>VACUUM ANALYZE</command> may still
+ block when acquiring sample rows from partitions, table inheritance
+ children, and some types of foreign tables. Also, while
+ <command>VACUUM</command> ordinarily processes all partitions of
+ specified partitioned tables, this option will cause
+ <command>VACUUM</command> to skip all partitions if there is a
+ conflicting lock on the partitioned table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">table_name</replaceable></term>
<listitem>
<para>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 4e3823b0f0a..a86963fc86a 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -622,6 +622,7 @@ expand_vacuum_rel(VacuumRelation *vrel, int options)
HeapTuple tuple;
Form_pg_class classForm;
bool include_parts;
+ int rvr_opts;
/*
* Since autovacuum workers supply OIDs when calling vacuum(), no
@@ -634,7 +635,30 @@ expand_vacuum_rel(VacuumRelation *vrel, int options)
* below, as well as find_all_inheritors's expectation that the caller
* holds some lock on the starting relation.
*/
- relid = RangeVarGetRelid(vrel->relation, AccessShareLock, false);
+ rvr_opts = (options & VACOPT_SKIP_LOCKED) ? RVR_SKIP_LOCKED : 0;
+ relid = RangeVarGetRelidExtended(vrel->relation,
+ AccessShareLock,
+ rvr_opts,
+ NULL, NULL);
+
+ /*
+ * If the lock is unavailable, emit the same log statement that
+ * vacuum_rel() and analyze_rel() would.
+ */
+ if (!OidIsValid(relid))
+ {
+ if (options & VACOPT_VACUUM)
+ ereport(WARNING,
+ (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
+ errmsg("skipping vacuum of \"%s\" --- lock not available",
+ vrel->relation->relname)));
+ else
+ ereport(WARNING,
+ (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
+ errmsg("skipping analyze of \"%s\" --- lock not available",
+ vrel->relation->relname)));
+ return vacrels;
+ }
/*
* To check whether the relation is a partitioned table and its
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ce60e99cff5..6d23bfb0b35 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10538,6 +10538,8 @@ vacuum_option_elem:
{
if (strcmp($1, "disable_page_skipping") == 0)
$$ = VACOPT_DISABLE_PAGE_SKIPPING;
+ else if (strcmp($1, "skip_locked") == 0)
+ $$ = VACOPT_SKIP_LOCKED;
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -10571,6 +10573,16 @@ analyze_option_list:
analyze_option_elem:
VERBOSE { $$ = VACOPT_VERBOSE; }
+ | IDENT
+ {
+ if (strcmp($1, "skip_locked") == 0)
+ $$ = VACOPT_SKIP_LOCKED;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("unrecognized ANALYZE option \"%s\"", $1),
+ parser_errposition(@1)));
+ }
;
analyze_keyword:
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 200df8e6595..aa4a0dba2a6 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3154,8 +3154,7 @@ typedef enum VacuumOption
VACOPT_VERBOSE = 1 << 2, /* print progress info */
VACOPT_FREEZE = 1 << 3, /* FREEZE option */
VACOPT_FULL = 1 << 4, /* FULL (non-concurrent) vacuum */
- VACOPT_SKIP_LOCKED = 1 << 5, /* skip if cannot get lock (autovacuum
- * only) */
+ VACOPT_SKIP_LOCKED = 1 << 5, /* skip if cannot get lock */
VACOPT_SKIPTOAST = 1 << 6, /* don't process the TOAST table, if any */
VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7 /* don't skip any pages */
} VacuumOption;
diff --git a/src/test/isolation/expected/vacuum-skip-locked.out b/src/test/isolation/expected/vacuum-skip-locked.out
new file mode 100644
index 00000000000..95ca4569ccb
--- /dev/null
+++ b/src/test/isolation/expected/vacuum-skip-locked.out
@@ -0,0 +1,171 @@
+Parsed test spec with 2 sessions
+
+starting permutation: lock_share vac_specified commit
+step lock_share:
+ BEGIN;
+ LOCK part1 IN SHARE MODE;
+
+WARNING: skipping vacuum of "part1" --- lock not available
+step vac_specified: VACUUM (SKIP_LOCKED) part1, part2;
+step commit:
+ COMMIT;
+
+
+starting permutation: lock_share vac_all_parts commit
+step lock_share:
+ BEGIN;
+ LOCK part1 IN SHARE MODE;
+
+step vac_all_parts: VACUUM (SKIP_LOCKED) parted;
+step commit:
+ COMMIT;
+
+
+starting permutation: lock_share analyze_specified commit
+step lock_share:
+ BEGIN;
+ LOCK part1 IN SHARE MODE;
+
+WARNING: skipping analyze of "part1" --- lock not available
+step analyze_specified: ANALYZE (SKIP_LOCKED) part1, part2;
+step commit:
+ COMMIT;
+
+
+starting permutation: lock_share analyze_all_parts commit
+step lock_share:
+ BEGIN;
+ LOCK part1 IN SHARE MODE;
+
+step analyze_all_parts: ANALYZE (SKIP_LOCKED) parted;
+step commit:
+ COMMIT;
+
+
+starting permutation: lock_share vac_analyze_specified commit
+step lock_share:
+ BEGIN;
+ LOCK part1 IN SHARE MODE;
+
+WARNING: skipping vacuum of "part1" --- lock not available
+step vac_analyze_specified: VACUUM (ANALYZE, SKIP_LOCKED) part1, part2;
+step commit:
+ COMMIT;
+
+
+starting permutation: lock_share vac_analyze_all_parts commit
+step lock_share:
+ BEGIN;
+ LOCK part1 IN SHARE MODE;
+
+step vac_analyze_all_parts: VACUUM (ANALYZE, SKIP_LOCKED) parted;
+step commit:
+ COMMIT;
+
+
+starting permutation: lock_share vac_full_specified commit
+step lock_share:
+ BEGIN;
+ LOCK part1 IN SHARE MODE;
+
+WARNING: skipping vacuum of "part1" --- lock not available
+step vac_full_specified: VACUUM (SKIP_LOCKED, FULL) part1, part2;
+step commit:
+ COMMIT;
+
+
+starting permutation: lock_share vac_full_all_parts commit
+step lock_share:
+ BEGIN;
+ LOCK part1 IN SHARE MODE;
+
+step vac_full_all_parts: VACUUM (SKIP_LOCKED, FULL) parted;
+step commit:
+ COMMIT;
+
+
+starting permutation: lock_access_exclusive vac_specified commit
+step lock_access_exclusive:
+ BEGIN;
+ LOCK part1 IN ACCESS EXCLUSIVE MODE;
+
+WARNING: skipping vacuum of "part1" --- lock not available
+step vac_specified: VACUUM (SKIP_LOCKED) part1, part2;
+step commit:
+ COMMIT;
+
+
+starting permutation: lock_access_exclusive vac_all_parts commit
+step lock_access_exclusive:
+ BEGIN;
+ LOCK part1 IN ACCESS EXCLUSIVE MODE;
+
+step vac_all_parts: VACUUM (SKIP_LOCKED) parted;
+step commit:
+ COMMIT;
+
+
+starting permutation: lock_access_exclusive analyze_specified commit
+step lock_access_exclusive:
+ BEGIN;
+ LOCK part1 IN ACCESS EXCLUSIVE MODE;
+
+WARNING: skipping analyze of "part1" --- lock not available
+step analyze_specified: ANALYZE (SKIP_LOCKED) part1, part2;
+step commit:
+ COMMIT;
+
+
+starting permutation: lock_access_exclusive analyze_all_parts commit
+step lock_access_exclusive:
+ BEGIN;
+ LOCK part1 IN ACCESS EXCLUSIVE MODE;
+
+step analyze_all_parts: ANALYZE (SKIP_LOCKED) parted; <waiting ...>
+step commit:
+ COMMIT;
+
+step analyze_all_parts: <... completed>
+
+starting permutation: lock_access_exclusive vac_analyze_specified commit
+step lock_access_exclusive:
+ BEGIN;
+ LOCK part1 IN ACCESS EXCLUSIVE MODE;
+
+WARNING: skipping vacuum of "part1" --- lock not available
+step vac_analyze_specified: VACUUM (ANALYZE, SKIP_LOCKED) part1, part2;
+step commit:
+ COMMIT;
+
+
+starting permutation: lock_access_exclusive vac_analyze_all_parts commit
+step lock_access_exclusive:
+ BEGIN;
+ LOCK part1 IN ACCESS EXCLUSIVE MODE;
+
+step vac_analyze_all_parts: VACUUM (ANALYZE, SKIP_LOCKED) parted; <waiting ...>
+step commit:
+ COMMIT;
+
+step vac_analyze_all_parts: <... completed>
+
+starting permutation: lock_access_exclusive vac_full_specified commit
+step lock_access_exclusive:
+ BEGIN;
+ LOCK part1 IN ACCESS EXCLUSIVE MODE;
+
+WARNING: skipping vacuum of "part1" --- lock not available
+step vac_full_specified: VACUUM (SKIP_LOCKED, FULL) part1, part2;
+step commit:
+ COMMIT;
+
+
+starting permutation: lock_access_exclusive vac_full_all_parts commit
+step lock_access_exclusive:
+ BEGIN;
+ LOCK part1 IN ACCESS EXCLUSIVE MODE;
+
+step vac_full_all_parts: VACUUM (SKIP_LOCKED, FULL) parted;
+step commit:
+ COMMIT;
+
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index c23b401225d..dd57a96e788 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -67,6 +67,7 @@ test: vacuum-reltuples
test: timeouts
test: vacuum-concurrent-drop
test: vacuum-conflict
+test: vacuum-skip-locked
test: predicate-hash
test: predicate-gist
test: predicate-gin
diff --git a/src/test/isolation/specs/vacuum-skip-locked.spec b/src/test/isolation/specs/vacuum-skip-locked.spec
new file mode 100644
index 00000000000..4d59b294ca9
--- /dev/null
+++ b/src/test/isolation/specs/vacuum-skip-locked.spec
@@ -0,0 +1,59 @@
+# Test for SKIP_LOCKED option of VACUUM and ANALYZE commands.
+#
+# This also verifies that log messages are not emitted for skipped relations
+# that were not specified in the VACUUM or ANALYZE command.
+
+setup
+{
+ CREATE TABLE parted (a INT) PARTITION BY LIST (a);
+ CREATE TABLE part1 PARTITION OF parted FOR VALUES IN (1);
+ CREATE TABLE part2 PARTITION OF parted FOR VALUES IN (2);
+}
+
+teardown
+{
+ DROP TABLE IF EXISTS parted;
+}
+
+session "s1"
+step "lock_share"
+{
+ BEGIN;
+ LOCK part1 IN SHARE MODE;
+}
+step "lock_access_exclusive"
+{
+ BEGIN;
+ LOCK part1 IN ACCESS EXCLUSIVE MODE;
+}
+step "commit"
+{
+ COMMIT;
+}
+
+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; }
+
+permutation "lock_share" "vac_specified" "commit"
+permutation "lock_share" "vac_all_parts" "commit"
+permutation "lock_share" "analyze_specified" "commit"
+permutation "lock_share" "analyze_all_parts" "commit"
+permutation "lock_share" "vac_analyze_specified" "commit"
+permutation "lock_share" "vac_analyze_all_parts" "commit"
+permutation "lock_share" "vac_full_specified" "commit"
+permutation "lock_share" "vac_full_all_parts" "commit"
+permutation "lock_access_exclusive" "vac_specified" "commit"
+permutation "lock_access_exclusive" "vac_all_parts" "commit"
+permutation "lock_access_exclusive" "analyze_specified" "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"
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 5993a902472..fa9d663abda 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -115,10 +115,20 @@ ERROR: column "does_not_exist" of relation "vacparted" does not exist
-- parenthesized syntax for ANALYZE
ANALYZE (VERBOSE) does_not_exist;
ERROR: relation "does_not_exist" does not exist
-ANALYZE (nonexistant-arg) does_not_exist;
-ERROR: syntax error at or near "nonexistant"
-LINE 1: ANALYZE (nonexistant-arg) does_not_exist;
+ANALYZE (nonexistent-arg) does_not_exist;
+ERROR: unrecognized ANALYZE option "nonexistent"
+LINE 1: ANALYZE (nonexistent-arg) does_not_exist;
^
+-- ensure argument order independence, and that SKIP_LOCKED on non-existing
+-- relation still errors out.
+ANALYZE (SKIP_LOCKED, VERBOSE) does_not_exist;
+ERROR: relation "does_not_exist" does not exist
+ANALYZE (VERBOSE, SKIP_LOCKED) does_not_exist;
+ERROR: relation "does_not_exist" does not exist
+-- SKIP_LOCKED option
+VACUUM (SKIP_LOCKED) vactst;
+VACUUM (SKIP_LOCKED, FULL) vactst;
+ANALYZE (SKIP_LOCKED) vactst;
DROP TABLE vaccluster;
DROP TABLE vactst;
DROP TABLE vacparted;
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 7f74da3cbd8..9defa0d8b2c 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -91,7 +91,17 @@ ANALYZE vactst (i), vacparted (does_not_exist);
-- parenthesized syntax for ANALYZE
ANALYZE (VERBOSE) does_not_exist;
-ANALYZE (nonexistant-arg) does_not_exist;
+ANALYZE (nonexistent-arg) does_not_exist;
+
+-- ensure argument order independence, and that SKIP_LOCKED on non-existing
+-- relation still errors out.
+ANALYZE (SKIP_LOCKED, VERBOSE) does_not_exist;
+ANALYZE (VERBOSE, SKIP_LOCKED) does_not_exist;
+
+-- SKIP_LOCKED option
+VACUUM (SKIP_LOCKED) vactst;
+VACUUM (SKIP_LOCKED, FULL) vactst;
+ANALYZE (SKIP_LOCKED) vactst;
DROP TABLE vaccluster;
DROP TABLE vactst;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-31 15:07:07 +0000