Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 803b130

Browse filesBrowse files
michaelpqmichaelpq
committed
Add option SKIP_LOCKED to VACUUM and ANALYZE
When specified, this option allows VACUUM to skip the work on a relation if there is a conflicting lock on it when trying to open it at the beginning of its processing. Similarly to autovacuum, this comes with a couple of limitations while the relation is processed which can cause the process to still block: - when opening the relation indexes. - when acquiring row samples for table inheritance trees, partition trees or certain types of foreign tables, and that a lock is taken on some leaves of such trees. Author: Nathan Bossart Reviewed-by: Michael Paquier, Andres Freund, Masahiko Sawada Discussion: https://postgr.es/m/9EF7EBE4-720D-4CF1-9D0E-4403D7E92990@amazon.com Discussion: https://postgr.es/m/20171201160907.27110.74730@wrigleys.postgresql.org
1 parent d173652 commit 803b130
Copy full SHA for 803b130

File tree

Expand file treeCollapse file tree

10 files changed

+333
-7
lines changed
Filter options
Expand file treeCollapse file tree

10 files changed

+333
-7
lines changed

‎doc/src/sgml/ref/analyze.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/analyze.sgml
+19Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,7 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
2727
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
2828

2929
VERBOSE
30+
SKIP_LOCKED
3031

3132
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
3233

@@ -76,6 +77,24 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
7677
</listitem>
7778
</varlistentry>
7879

80+
<varlistentry>
81+
<term><literal>SKIP_LOCKED</literal></term>
82+
<listitem>
83+
<para>
84+
Specifies that <command>ANALYZE</command> should not wait for any
85+
conflicting locks to be released when beginning work on a relation:
86+
if a relation cannot be locked immediately without waiting, the relation
87+
is skipped. Note that even with this option, <command>ANALYZE</command>
88+
may still block when opening the relation's indexes or when acquiring
89+
sample rows from partitions, table inheritance children, and some
90+
types of foreign tables. Also, while <command>ANALYZE</command>
91+
ordinarily processes all partitions of specified partitioned tables,
92+
this option will cause <command>ANALYZE</command> to skip all
93+
partitions if there is a conflicting lock on the partitioned table.
94+
</para>
95+
</listitem>
96+
</varlistentry>
97+
7998
<varlistentry>
8099
<term><replaceable class="parameter">table_name</replaceable></term>
81100
<listitem>

‎doc/src/sgml/ref/vacuum.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/vacuum.sgml
+21Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -31,6 +31,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
3131
VERBOSE
3232
ANALYZE
3333
DISABLE_PAGE_SKIPPING
34+
SKIP_LOCKED
3435

3536
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
3637

@@ -160,6 +161,26 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
160161
</listitem>
161162
</varlistentry>
162163

164+
<varlistentry>
165+
<term><literal>SKIP_LOCKED</literal></term>
166+
<listitem>
167+
<para>
168+
Specifies that <command>VACUUM</command> should not wait for any
169+
conflicting locks to be released when beginning work on a relation:
170+
if a relation cannot be locked immediately without waiting, the relation
171+
is skipped. Note that even with this option,
172+
<command>VACUUM</command> may still block when opening the relation's
173+
indexes. Additionally, <command>VACUUM ANALYZE</command> may still
174+
block when acquiring sample rows from partitions, table inheritance
175+
children, and some types of foreign tables. Also, while
176+
<command>VACUUM</command> ordinarily processes all partitions of
177+
specified partitioned tables, this option will cause
178+
<command>VACUUM</command> to skip all partitions if there is a
179+
conflicting lock on the partitioned table.
180+
</para>
181+
</listitem>
182+
</varlistentry>
183+
163184
<varlistentry>
164185
<term><replaceable class="parameter">table_name</replaceable></term>
165186
<listitem>

‎src/backend/commands/vacuum.c

Copy file name to clipboardExpand all lines: src/backend/commands/vacuum.c
+25-1Lines changed: 25 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -622,6 +622,7 @@ expand_vacuum_rel(VacuumRelation *vrel, int options)
622622
HeapTuple tuple;
623623
Form_pg_class classForm;
624624
bool include_parts;
625+
int rvr_opts;
625626

626627
/*
627628
* Since autovacuum workers supply OIDs when calling vacuum(), no
@@ -634,7 +635,30 @@ expand_vacuum_rel(VacuumRelation *vrel, int options)
634635
* below, as well as find_all_inheritors's expectation that the caller
635636
* holds some lock on the starting relation.
636637
*/
637-
relid = RangeVarGetRelid(vrel->relation, AccessShareLock, false);
638+
rvr_opts = (options & VACOPT_SKIP_LOCKED) ? RVR_SKIP_LOCKED : 0;
639+
relid = RangeVarGetRelidExtended(vrel->relation,
640+
AccessShareLock,
641+
rvr_opts,
642+
NULL, NULL);
643+
644+
/*
645+
* If the lock is unavailable, emit the same log statement that
646+
* vacuum_rel() and analyze_rel() would.
647+
*/
648+
if (!OidIsValid(relid))
649+
{
650+
if (options & VACOPT_VACUUM)
651+
ereport(WARNING,
652+
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
653+
errmsg("skipping vacuum of \"%s\" --- lock not available",
654+
vrel->relation->relname)));
655+
else
656+
ereport(WARNING,
657+
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
658+
errmsg("skipping analyze of \"%s\" --- lock not available",
659+
vrel->relation->relname)));
660+
return vacrels;
661+
}
638662

639663
/*
640664
* To check whether the relation is a partitioned table and its

‎src/backend/parser/gram.y

Copy file name to clipboardExpand all lines: src/backend/parser/gram.y
+12Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10538,6 +10538,8 @@ vacuum_option_elem:
1053810538
{
1053910539
if (strcmp($1, "disable_page_skipping") == 0)
1054010540
$$ = VACOPT_DISABLE_PAGE_SKIPPING;
10541+
else if (strcmp($1, "skip_locked") == 0)
10542+
$$ = VACOPT_SKIP_LOCKED;
1054110543
else
1054210544
ereport(ERROR,
1054310545
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -10571,6 +10573,16 @@ analyze_option_list:
1057110573

1057210574
analyze_option_elem:
1057310575
VERBOSE { $$ = VACOPT_VERBOSE; }
10576+
| IDENT
10577+
{
10578+
if (strcmp($1, "skip_locked") == 0)
10579+
$$ = VACOPT_SKIP_LOCKED;
10580+
else
10581+
ereport(ERROR,
10582+
(errcode(ERRCODE_SYNTAX_ERROR),
10583+
errmsg("unrecognized ANALYZE option \"%s\"", $1),
10584+
parser_errposition(@1)));
10585+
}
1057410586
;
1057510587

1057610588
analyze_keyword:

‎src/include/nodes/parsenodes.h

Copy file name to clipboardExpand all lines: src/include/nodes/parsenodes.h
+1-2Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3154,8 +3154,7 @@ typedef enum VacuumOption
31543154
VACOPT_VERBOSE = 1 << 2, /* print progress info */
31553155
VACOPT_FREEZE = 1 << 3, /* FREEZE option */
31563156
VACOPT_FULL = 1 << 4, /* FULL (non-concurrent) vacuum */
3157-
VACOPT_SKIP_LOCKED = 1 << 5, /* skip if cannot get lock (autovacuum
3158-
* only) */
3157+
VACOPT_SKIP_LOCKED = 1 << 5, /* skip if cannot get lock */
31593158
VACOPT_SKIPTOAST = 1 << 6, /* don't process the TOAST table, if any */
31603159
VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7 /* don't skip any pages */
31613160
} VacuumOption;

‎src/test/isolation/expected/vacuum-skip-locked.out

Copy file name to clipboard
+171Lines changed: 171 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,171 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: lock_share vac_specified commit
4+
step lock_share:
5+
BEGIN;
6+
LOCK part1 IN SHARE MODE;
7+
8+
WARNING: skipping vacuum of "part1" --- lock not available
9+
step vac_specified: VACUUM (SKIP_LOCKED) part1, part2;
10+
step commit:
11+
COMMIT;
12+
13+
14+
starting permutation: lock_share vac_all_parts commit
15+
step lock_share:
16+
BEGIN;
17+
LOCK part1 IN SHARE MODE;
18+
19+
step vac_all_parts: VACUUM (SKIP_LOCKED) parted;
20+
step commit:
21+
COMMIT;
22+
23+
24+
starting permutation: lock_share analyze_specified commit
25+
step lock_share:
26+
BEGIN;
27+
LOCK part1 IN SHARE MODE;
28+
29+
WARNING: skipping analyze of "part1" --- lock not available
30+
step analyze_specified: ANALYZE (SKIP_LOCKED) part1, part2;
31+
step commit:
32+
COMMIT;
33+
34+
35+
starting permutation: lock_share analyze_all_parts commit
36+
step lock_share:
37+
BEGIN;
38+
LOCK part1 IN SHARE MODE;
39+
40+
step analyze_all_parts: ANALYZE (SKIP_LOCKED) parted;
41+
step commit:
42+
COMMIT;
43+
44+
45+
starting permutation: lock_share vac_analyze_specified commit
46+
step lock_share:
47+
BEGIN;
48+
LOCK part1 IN SHARE MODE;
49+
50+
WARNING: skipping vacuum of "part1" --- lock not available
51+
step vac_analyze_specified: VACUUM (ANALYZE, SKIP_LOCKED) part1, part2;
52+
step commit:
53+
COMMIT;
54+
55+
56+
starting permutation: lock_share vac_analyze_all_parts commit
57+
step lock_share:
58+
BEGIN;
59+
LOCK part1 IN SHARE MODE;
60+
61+
step vac_analyze_all_parts: VACUUM (ANALYZE, SKIP_LOCKED) parted;
62+
step commit:
63+
COMMIT;
64+
65+
66+
starting permutation: lock_share vac_full_specified commit
67+
step lock_share:
68+
BEGIN;
69+
LOCK part1 IN SHARE MODE;
70+
71+
WARNING: skipping vacuum of "part1" --- lock not available
72+
step vac_full_specified: VACUUM (SKIP_LOCKED, FULL) part1, part2;
73+
step commit:
74+
COMMIT;
75+
76+
77+
starting permutation: lock_share vac_full_all_parts commit
78+
step lock_share:
79+
BEGIN;
80+
LOCK part1 IN SHARE MODE;
81+
82+
step vac_full_all_parts: VACUUM (SKIP_LOCKED, FULL) parted;
83+
step commit:
84+
COMMIT;
85+
86+
87+
starting permutation: lock_access_exclusive vac_specified commit
88+
step lock_access_exclusive:
89+
BEGIN;
90+
LOCK part1 IN ACCESS EXCLUSIVE MODE;
91+
92+
WARNING: skipping vacuum of "part1" --- lock not available
93+
step vac_specified: VACUUM (SKIP_LOCKED) part1, part2;
94+
step commit:
95+
COMMIT;
96+
97+
98+
starting permutation: lock_access_exclusive vac_all_parts commit
99+
step lock_access_exclusive:
100+
BEGIN;
101+
LOCK part1 IN ACCESS EXCLUSIVE MODE;
102+
103+
step vac_all_parts: VACUUM (SKIP_LOCKED) parted;
104+
step commit:
105+
COMMIT;
106+
107+
108+
starting permutation: lock_access_exclusive analyze_specified commit
109+
step lock_access_exclusive:
110+
BEGIN;
111+
LOCK part1 IN ACCESS EXCLUSIVE MODE;
112+
113+
WARNING: skipping analyze of "part1" --- lock not available
114+
step analyze_specified: ANALYZE (SKIP_LOCKED) part1, part2;
115+
step commit:
116+
COMMIT;
117+
118+
119+
starting permutation: lock_access_exclusive analyze_all_parts commit
120+
step lock_access_exclusive:
121+
BEGIN;
122+
LOCK part1 IN ACCESS EXCLUSIVE MODE;
123+
124+
step analyze_all_parts: ANALYZE (SKIP_LOCKED) parted; <waiting ...>
125+
step commit:
126+
COMMIT;
127+
128+
step analyze_all_parts: <... completed>
129+
130+
starting permutation: lock_access_exclusive vac_analyze_specified commit
131+
step lock_access_exclusive:
132+
BEGIN;
133+
LOCK part1 IN ACCESS EXCLUSIVE MODE;
134+
135+
WARNING: skipping vacuum of "part1" --- lock not available
136+
step vac_analyze_specified: VACUUM (ANALYZE, SKIP_LOCKED) part1, part2;
137+
step commit:
138+
COMMIT;
139+
140+
141+
starting permutation: lock_access_exclusive vac_analyze_all_parts commit
142+
step lock_access_exclusive:
143+
BEGIN;
144+
LOCK part1 IN ACCESS EXCLUSIVE MODE;
145+
146+
step vac_analyze_all_parts: VACUUM (ANALYZE, SKIP_LOCKED) parted; <waiting ...>
147+
step commit:
148+
COMMIT;
149+
150+
step vac_analyze_all_parts: <... completed>
151+
152+
starting permutation: lock_access_exclusive vac_full_specified commit
153+
step lock_access_exclusive:
154+
BEGIN;
155+
LOCK part1 IN ACCESS EXCLUSIVE MODE;
156+
157+
WARNING: skipping vacuum of "part1" --- lock not available
158+
step vac_full_specified: VACUUM (SKIP_LOCKED, FULL) part1, part2;
159+
step commit:
160+
COMMIT;
161+
162+
163+
starting permutation: lock_access_exclusive vac_full_all_parts commit
164+
step lock_access_exclusive:
165+
BEGIN;
166+
LOCK part1 IN ACCESS EXCLUSIVE MODE;
167+
168+
step vac_full_all_parts: VACUUM (SKIP_LOCKED, FULL) parted;
169+
step commit:
170+
COMMIT;
171+

‎src/test/isolation/isolation_schedule

Copy file name to clipboardExpand all lines: src/test/isolation/isolation_schedule
+1Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -67,6 +67,7 @@ test: vacuum-reltuples
6767
test: timeouts
6868
test: vacuum-concurrent-drop
6969
test: vacuum-conflict
70+
test: vacuum-skip-locked
7071
test: predicate-hash
7172
test: predicate-gist
7273
test: predicate-gin

‎src/test/isolation/specs/vacuum-skip-locked.spec

Copy file name to clipboard
+59Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,59 @@
1+
# Test for SKIP_LOCKED option of VACUUM and ANALYZE commands.
2+
#
3+
# This also verifies that log messages are not emitted for skipped relations
4+
# that were not specified in the VACUUM or ANALYZE command.
5+
6+
setup
7+
{
8+
CREATE TABLE parted (a INT) PARTITION BY LIST (a);
9+
CREATE TABLE part1 PARTITION OF parted FOR VALUES IN (1);
10+
CREATE TABLE part2 PARTITION OF parted FOR VALUES IN (2);
11+
}
12+
13+
teardown
14+
{
15+
DROP TABLE IF EXISTS parted;
16+
}
17+
18+
session "s1"
19+
step "lock_share"
20+
{
21+
BEGIN;
22+
LOCK part1 IN SHARE MODE;
23+
}
24+
step "lock_access_exclusive"
25+
{
26+
BEGIN;
27+
LOCK part1 IN ACCESS EXCLUSIVE MODE;
28+
}
29+
step "commit"
30+
{
31+
COMMIT;
32+
}
33+
34+
session "s2"
35+
step "vac_specified" { VACUUM (SKIP_LOCKED) part1, part2; }
36+
step "vac_all_parts" { VACUUM (SKIP_LOCKED) parted; }
37+
step "analyze_specified" { ANALYZE (SKIP_LOCKED) part1, part2; }
38+
step "analyze_all_parts" { ANALYZE (SKIP_LOCKED) parted; }
39+
step "vac_analyze_specified" { VACUUM (ANALYZE, SKIP_LOCKED) part1, part2; }
40+
step "vac_analyze_all_parts" { VACUUM (ANALYZE, SKIP_LOCKED) parted; }
41+
step "vac_full_specified" { VACUUM (SKIP_LOCKED, FULL) part1, part2; }
42+
step "vac_full_all_parts" { VACUUM (SKIP_LOCKED, FULL) parted; }
43+
44+
permutation "lock_share" "vac_specified" "commit"
45+
permutation "lock_share" "vac_all_parts" "commit"
46+
permutation "lock_share" "analyze_specified" "commit"
47+
permutation "lock_share" "analyze_all_parts" "commit"
48+
permutation "lock_share" "vac_analyze_specified" "commit"
49+
permutation "lock_share" "vac_analyze_all_parts" "commit"
50+
permutation "lock_share" "vac_full_specified" "commit"
51+
permutation "lock_share" "vac_full_all_parts" "commit"
52+
permutation "lock_access_exclusive" "vac_specified" "commit"
53+
permutation "lock_access_exclusive" "vac_all_parts" "commit"
54+
permutation "lock_access_exclusive" "analyze_specified" "commit"
55+
permutation "lock_access_exclusive" "analyze_all_parts" "commit"
56+
permutation "lock_access_exclusive" "vac_analyze_specified" "commit"
57+
permutation "lock_access_exclusive" "vac_analyze_all_parts" "commit"
58+
permutation "lock_access_exclusive" "vac_full_specified" "commit"
59+
permutation "lock_access_exclusive" "vac_full_all_parts" "commit"

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.