Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commit803b130

Browse files
committed
Add option SKIP_LOCKED to VACUUM and ANALYZE
When specified, this option allows VACUUM to skip the work on a relationif there is a conflicting lock on it when trying to open it at thebeginning of its processing.Similarly to autovacuum, this comes with a couple of limitations whilethe 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 treesor certain types of foreign tables, and that a lock is taken on someleaves of such trees.Author: Nathan BossartReviewed-by: Michael Paquier, Andres Freund, Masahiko SawadaDiscussion:https://postgr.es/m/9EF7EBE4-720D-4CF1-9D0E-4403D7E92990@amazon.comDiscussion:https://postgr.es/m/20171201160907.27110.74730@wrigleys.postgresql.org
1 parentd173652 commit803b130

File tree

10 files changed

+333
-7
lines changed

10 files changed

+333
-7
lines changed

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

Lines 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

Lines 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

Lines 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
HeapTupletuple;
623623
Form_pg_classclassForm;
624624
boolinclude_parts;
625+
intrvr_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+
returnvacrels;
661+
}
638662

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

‎src/backend/parser/gram.y

Lines 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+
elseif (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

Lines 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;
Lines 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

Lines 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
Lines 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+
CREATETABLEparted (aINT)PARTITIONBYLIST (a);
9+
CREATETABLEpart1PARTITIONOFpartedFORVALUESIN (1);
10+
CREATETABLEpart2PARTITIONOFpartedFORVALUESIN (2);
11+
}
12+
13+
teardown
14+
{
15+
DROPTABLEIFEXISTSparted;
16+
}
17+
18+
session"s1"
19+
step"lock_share"
20+
{
21+
BEGIN;
22+
LOCKpart1INSHAREMODE;
23+
}
24+
step"lock_access_exclusive"
25+
{
26+
BEGIN;
27+
LOCKpart1INACCESSEXCLUSIVEMODE;
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)
[8]ページ先頭
©2009-2025 Movatter.jp