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

Commite5550d5

Browse files
Reduce lock levels of some ALTER TABLE cmds
VALIDATE CONSTRAINTCLUSTER ONSET WITHOUT CLUSTERALTER COLUMN SET STATISTICSALTER COLUMN SET ()ALTER COLUMN RESET ()All other sub-commands use AccessExclusiveLockSimon Riggs and Noah MischReviews by Robert Haas and Andres Freund
1 parent80a5cf6 commite5550d5

File tree

13 files changed

+401
-119
lines changed

13 files changed

+401
-119
lines changed

‎doc/src/sgml/mvcc.sgml‎

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -865,7 +865,9 @@ ERROR: could not serialize access due to read/write dependencies among transact
865865
<para>
866866
Acquired by <command>VACUUM</command> (without <option>FULL</option>),
867867
<command>ANALYZE</>, <command>CREATE INDEX CONCURRENTLY</>, and
868-
some forms of <command>ALTER TABLE</command>.
868+
<command>ALTER TABLE VALIDATE</command> and other
869+
<command>ALTER TABLE</command> variants (for full details see
870+
<xref linkend="SQL-ALTERTABLE">).
869871
</para>
870872
</listitem>
871873
</varlistentry>
@@ -951,10 +953,11 @@ ERROR: could not serialize access due to read/write dependencies among transact
951953
</para>
952954

953955
<para>
954-
Acquired by the <command>ALTER TABLE</>, <command>DROP TABLE</>,
956+
Acquired by the <command>DROP TABLE</>,
955957
<command>TRUNCATE</command>, <command>REINDEX</command>,
956958
<command>CLUSTER</command>, and <command>VACUUM FULL</command>
957-
commands.
959+
commands. Many forms of <command>ALTER TABLE</> also acquire
960+
a lock at this level (see <xref linkend="SQL-ALTERTABLE">).
958961
This is also the default lock mode for <command>LOCK TABLE</command>
959962
statements that do not specify a mode explicitly.
960963
</para>

‎doc/src/sgml/ref/alter_table.sgml‎

Lines changed: 34 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -84,7 +84,10 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
8484

8585
<para>
8686
<command>ALTER TABLE</command> changes the definition of an existing table.
87-
There are several subforms:
87+
There are several subforms described below. Note that the lock level required
88+
may differ for each subform. An <literal>ACCESS EXCLUSIVE</literal> lock is held
89+
unless explicitly noted. When multiple subcommands are listed, the lock
90+
held will be the strictest one required from any subcommand.
8891

8992
<variablelist>
9093
<varlistentry>
@@ -181,6 +184,9 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
181184
<productname>PostgreSQL</productname> query planner, refer to
182185
<xref linkend="planner-stats">.
183186
</para>
187+
<para>
188+
SET STATISTICS acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
189+
</para>
184190
</listitem>
185191
</varlistentry>
186192

@@ -213,6 +219,10 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
213219
of statistics by the <productname>PostgreSQL</productname> query
214220
planner, refer to <xref linkend="planner-stats">.
215221
</para>
222+
<para>
223+
Changing per-attribute options acquires a
224+
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
225+
</para>
216226
</listitem>
217227
</varlistentry>
218228

@@ -338,11 +348,17 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
338348
Nothing happens if the constraint is already marked valid.
339349
</para>
340350
<para>
341-
Validation can be a long process on larger tables and currently requires
342-
an <literal>ACCESS EXCLUSIVE</literal> lock. The value of separating
351+
Validation can be a long process on larger tables. The value of separating
343352
validation from initial creation is that you can defer validation to less
344353
busy times, or can be used to give additional time to correct pre-existing
345-
errors while preventing new errors.
354+
errors while preventing new errors. Note also that validation on its own
355+
does not prevent normal write commands against the table while it runs.
356+
</para>
357+
<para>
358+
Validation acquires only a <literal>SHARE UPDATE EXCLUSIVE</literal> lock
359+
on the table being altered. If the constraint is a foreign key then
360+
a <literal>ROW SHARE</literal> lock is also required on
361+
the table referenced by the constraint.
346362
</para>
347363
</listitem>
348364
</varlistentry>
@@ -408,6 +424,9 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
408424
<xref linkend="SQL-CLUSTER">
409425
operations. It does not actually re-cluster the table.
410426
</para>
427+
<para>
428+
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
429+
</para>
411430
</listitem>
412431
</varlistentry>
413432

@@ -420,6 +439,9 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
420439
index specification from the table. This affects
421440
future cluster operations that don't specify an index.
422441
</para>
442+
<para>
443+
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
444+
</para>
423445
</listitem>
424446
</varlistentry>
425447

@@ -1078,6 +1100,14 @@ ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES
10781100
</programlisting>
10791101
</para>
10801102

1103+
<para>
1104+
To add a foreign key constraint to a table with the least impact on other work:
1105+
<programlisting>
1106+
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
1107+
ALTER TABLE distributors VALIDATE CONSTRAINT distfk;
1108+
</programlisting>
1109+
</para>
1110+
10811111
<para>
10821112
To add a (multicolumn) unique constraint to a table:
10831113
<programlisting>

‎src/backend/catalog/toasting.c‎

Lines changed: 37 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -27,20 +27,23 @@
2727
#include"catalog/toasting.h"
2828
#include"miscadmin.h"
2929
#include"nodes/makefuncs.h"
30+
#include"storage/lock.h"
3031
#include"utils/builtins.h"
3132
#include"utils/rel.h"
3233
#include"utils/syscache.h"
3334

3435
/* Potentially set by contrib/pg_upgrade_support functions */
3536
Oidbinary_upgrade_next_toast_pg_type_oid=InvalidOid;
3637

38+
staticvoidCheckAndCreateToastTable(OidrelOid,Datumreloptions,
39+
LOCKMODElockmode,boolcheck);
3740
staticboolcreate_toast_table(Relationrel,OidtoastOid,OidtoastIndexOid,
38-
Datumreloptions);
41+
Datumreloptions,LOCKMODElockmode,boolcheck);
3942
staticboolneeds_toast_table(Relationrel);
4043

4144

4245
/*
43-
*AlterTableCreateToastTable
46+
*CreateToastTable variants
4447
*If the table needs a toast table, and doesn't already have one,
4548
*then create a toast table for it.
4649
*
@@ -52,21 +55,32 @@ static bool needs_toast_table(Relation rel);
5255
* to end with CommandCounterIncrement if it makes any changes.
5356
*/
5457
void
55-
AlterTableCreateToastTable(OidrelOid,Datumreloptions)
58+
AlterTableCreateToastTable(OidrelOid,Datumreloptions,LOCKMODElockmode)
59+
{
60+
CheckAndCreateToastTable(relOid,reloptions,lockmode, true);
61+
}
62+
63+
void
64+
NewHeapCreateToastTable(OidrelOid,Datumreloptions,LOCKMODElockmode)
65+
{
66+
CheckAndCreateToastTable(relOid,reloptions,lockmode, false);
67+
}
68+
69+
void
70+
NewRelationCreateToastTable(OidrelOid,Datumreloptions)
71+
{
72+
CheckAndCreateToastTable(relOid,reloptions,AccessExclusiveLock, false);
73+
}
74+
75+
staticvoid
76+
CheckAndCreateToastTable(OidrelOid,Datumreloptions,LOCKMODElockmode,boolcheck)
5677
{
5778
Relationrel;
5879

59-
/*
60-
* Grab an exclusive lock on the target table, since we'll update its
61-
* pg_class tuple. This is redundant for all present uses, since caller
62-
* will have such a lock already. But the lock is needed to ensure that
63-
* concurrent readers of the pg_class tuple won't have visibility issues,
64-
* so let's be safe.
65-
*/
66-
rel=heap_open(relOid,AccessExclusiveLock);
80+
rel=heap_open(relOid,lockmode);
6781

6882
/* create_toast_table does all the work */
69-
(void)create_toast_table(rel,InvalidOid,InvalidOid,reloptions);
83+
(void)create_toast_table(rel,InvalidOid,InvalidOid,reloptions,lockmode,check);
7084

7185
heap_close(rel,NoLock);
7286
}
@@ -91,7 +105,8 @@ BootstrapToastTable(char *relName, Oid toastOid, Oid toastIndexOid)
91105
relName)));
92106

93107
/* create_toast_table does all the work */
94-
if (!create_toast_table(rel,toastOid,toastIndexOid, (Datum)0))
108+
if (!create_toast_table(rel,toastOid,toastIndexOid, (Datum)0,
109+
AccessExclusiveLock, false))
95110
elog(ERROR,"\"%s\" does not require a toast table",
96111
relName);
97112

@@ -107,7 +122,8 @@ BootstrapToastTable(char *relName, Oid toastOid, Oid toastIndexOid)
107122
* bootstrap they can be nonzero to specify hand-assigned OIDs
108123
*/
109124
staticbool
110-
create_toast_table(Relationrel,OidtoastOid,OidtoastIndexOid,Datumreloptions)
125+
create_toast_table(Relationrel,OidtoastOid,OidtoastIndexOid,
126+
Datumreloptions,LOCKMODElockmode,boolcheck)
111127
{
112128
OidrelOid=RelationGetRelid(rel);
113129
HeapTuplereltup;
@@ -160,6 +176,13 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, Datum reloptio
160176
!OidIsValid(binary_upgrade_next_toast_pg_class_oid)))
161177
return false;
162178

179+
/*
180+
* If requested check lockmode is sufficient. This is a cross check
181+
* in case of errors or conflicting decisions in earlier code.
182+
*/
183+
if (check&&lockmode!=AccessExclusiveLock)
184+
elog(ERROR,"AccessExclusiveLock required to add toast table.");
185+
163186
/*
164187
* Create the toast table and its index
165188
*/

‎src/backend/commands/cluster.c‎

Lines changed: 7 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -408,10 +408,10 @@ cluster_rel(Oid tableOid, Oid indexOid, bool recheck, bool verbose)
408408
/*
409409
* Verify that the specified heap and index are valid to cluster on
410410
*
411-
* Side effect: obtainsexclusivelock on the index. The callershould
412-
* already haveexclusive lockon the table,so the index lock is likely
413-
*redundant, but it seems best to grab it anyway to ensure the index
414-
*definition can't change under us.
411+
* Side effect: obtains lock on the index. The callermay
412+
*in some casesalready haveAccessExclusiveLockon the table,but
413+
*not in all cases so we can't rely on the table-level lock for
414+
*protection here.
415415
*/
416416
void
417417
check_index_is_clusterable(RelationOldHeap,OidindexOid,boolrecheck,LOCKMODElockmode)
@@ -696,10 +696,10 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
696696
*
697697
* If the relation doesn't have a TOAST table already, we can't need one
698698
* for the new relation. The other way around is possible though: if some
699-
* wide columns have been dropped,AlterTableCreateToastTable can decide
699+
* wide columns have been dropped,NewHeapCreateToastTable can decide
700700
* that no TOAST table is needed for the new table.
701701
*
702-
* Note thatAlterTableCreateToastTable ends with CommandCounterIncrement,
702+
* Note thatNewHeapCreateToastTable ends with CommandCounterIncrement,
703703
* so that the TOAST table will be visible for insertion.
704704
*/
705705
toastid=OldHeap->rd_rel->reltoastrelid;
@@ -714,7 +714,7 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
714714
if (isNull)
715715
reloptions= (Datum)0;
716716

717-
AlterTableCreateToastTable(OIDNewHeap,reloptions);
717+
NewHeapCreateToastTable(OIDNewHeap,reloptions,lockmode);
718718

719719
ReleaseSysCache(tuple);
720720
}

‎src/backend/commands/createas.c‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -359,7 +359,7 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
359359

360360
/*
361361
* If necessary, create a TOAST table for the target table. Note that
362-
*AlterTableCreateToastTable ends with CommandCounterIncrement(), so that
362+
*NewRelationCreateToastTable ends with CommandCounterIncrement(), so that
363363
* the TOAST table will be visible for insertion.
364364
*/
365365
CommandCounterIncrement();
@@ -373,7 +373,7 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
373373

374374
(void)heap_reloptions(RELKIND_TOASTVALUE,toast_options, true);
375375

376-
AlterTableCreateToastTable(intoRelationId,toast_options);
376+
NewRelationCreateToastTable(intoRelationId,toast_options);
377377

378378
/* Create the "view" part of a materialized view. */
379379
if (is_matview)

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp