v9-0007-Open-up-VACOPT_SKIP_LOCKED-to-users.patch

From 32c15a6acb7b239181ce224308470ee7f57c70fa Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Wed, 5 Sep 2018 14:45:16 +0000
Subject: [PATCH v9 7/7] Open up VACOPT_SKIP_LOCKED to users.

---
 doc/src/sgml/ref/analyze.sgml                      |  19 +++
 doc/src/sgml/ref/vacuum.sgml                       |  17 ++
 src/backend/parser/gram.y                          |  12 ++
 src/include/nodes/parsenodes.h                     |   3 +-
 src/test/isolation/expected/vacuum-skip-locked.out | 171 +++++++++++++++++++++
 src/test/isolation/isolation_schedule              |   1 +
 src/test/isolation/specs/vacuum-skip-locked.spec   |  59 +++++++
 src/test/regress/expected/vacuum.out               |  12 +-
 src/test/regress/sql/vacuum.sql                    |  10 ++
 9 files changed, 301 insertions(+), 3 deletions(-)
 create mode 100644 src/test/isolation/expected/vacuum-skip-locked.out
 create mode 100644 src/test/isolation/specs/vacuum-skip-locked.spec

diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 10b3a9a673..40dec976e7 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>
 
@@ -76,6 +77,24 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
     </listitem>
    </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: if a relation cannot be locked
+      immediately without waiting, the relation is skipped.  Note that even with
+      this option, <command>ANALYZE</command> can still block when opening the
+      relation's indexes.  Also, while <command>ANALYZE</command> ordinarily
+      processes all leaf partitions of partitioned tables, this option will
+      cause <command>ANALYZE</command> to skip all leaf partitions if there is a
+      conflicting lock on the partitioned table.  Similarly,
+      <command>ANALYZE</command> will skip partitioned tables if there is a
+      conflicting lock on any of its leaf partitions.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><replaceable class="parameter">table_name</replaceable></term>
     <listitem>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index b760e8ede1..85987916f5 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>
 
@@ -160,6 +161,22 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     </listitem>
    </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: if a relation cannot be locked
+      immediately without waiting, the relation is skipped.  Note that even with
+      this option, <command>VACUUM</command> can still block when opening the
+      relation’s indexes.  Also, while <command>VACUUM</command> ordinarily
+      processes all leaf partitions of specified partitioned tables, this option
+      will cause <command>VACUUM</command> to skip all leaf partitions if there
+      is a conflicting lock on the partitioned table.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><replaceable class="parameter">table_name</replaceable></term>
     <listitem>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 4bd2223f26..bbf7f0298d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10563,6 +10563,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),
@@ -10596,6 +10598,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 07ab1a3dde..8aa536dc8d 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3141,8 +3141,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 0000000000..42688d8803
--- /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;
+
+WARNING:  skipping analyze of "parted" --- lock not available
+step analyze_all_parts: ANALYZE (SKIP_LOCKED) parted;
+step commit: 
+	COMMIT;
+
+
+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;
+
+WARNING:  skipping analyze of "parted" --- lock not available
+step vac_analyze_all_parts: VACUUM (ANALYZE, SKIP_LOCKED) parted;
+step commit: 
+	COMMIT;
+
+
+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 c23b401225..dd57a96e78 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 0000000000..3f05141388
--- /dev/null
+++ b/src/test/isolation/specs/vacuum-skip-locked.spec
@@ -0,0 +1,59 @@
+# Test for the SKIP_LOCKED option for 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 5993a90247..b342982089 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -116,9 +116,19 @@ ERROR:  column "does_not_exist" of relation "vacparted" does not exist
 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"
+ERROR:  unrecognized ANALYZE option "nonexistant"
 LINE 1: ANALYZE (nonexistant-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 7f74da3cbd..bfdfd87744 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -93,6 +93,16 @@ ANALYZE vactst (i), vacparted (does_not_exist);
 ANALYZE (VERBOSE) does_not_exist;
 ANALYZE (nonexistant-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;
 DROP TABLE vacparted;
-- 
2.16.2