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

Commit0ef0396

Browse files
Reduce lock levels of some trigger DDL and add FKs
Reduce lock levels to ShareRowExclusive for the following SQL CREATE TRIGGER (but not DROP or ALTER) ALTER TABLE ENABLE TRIGGER ALTER TABLE DISABLE TRIGGER ALTER TABLE … ADD CONSTRAINT FOREIGN KEYOriginal work by Simon Riggs, extracted and refreshed by Andreas KarlssonNew test cases added by Andreas KarlssonReviewed by Noah Misch, Andres Freund, Michael Paquier and Simon Riggs
1 parentca68053 commit0ef0396

File tree

7 files changed

+1236
-489
lines changed

7 files changed

+1236
-489
lines changed

‎doc/src/sgml/mvcc.sgml

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -909,9 +909,9 @@ ERROR: could not serialize access due to read/write dependencies among transact
909909
</para>
910910

911911
<para>
912-
This lock mode is not automatically acquired by any
913-
<productname>PostgreSQL</productname> command.
914-
</para>
912+
Acquired by <command>CREATE TRIGGER</command> and many forms of
913+
<command>ALTER TABLE</command> (see <xref linkend="SQL-ALTERTABLE">).
914+
</para>>
915915
</listitem>
916916
</varlistentry>
917917

@@ -958,9 +958,9 @@ ERROR: could not serialize access due to read/write dependencies among transact
958958
<command>TRUNCATE</command>, <command>REINDEX</command>,
959959
<command>CLUSTER</command>, and <command>VACUUM FULL</command>
960960
commands. Many forms of <command>ALTER TABLE</> also acquire
961-
a lock at this level (see <xref linkend="SQL-ALTERTABLE">).
962-
This is also the default lock mode for<command>LOCK TABLE</command>
963-
statements that do not specifya mode explicitly.
961+
a lock at this level. This is also the default lock mode for
962+
<command>LOCK TABLE</command> statements that do not specify
963+
a mode explicitly.
964964
</para>
965965
</listitem>
966966
</varlistentry>

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -406,6 +406,9 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
406406
mode, and triggers configured as <literal>ENABLE ALWAYS</literal> will
407407
fire regardless of the current replication mode.
408408
</para>
409+
<para>
410+
This command acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock.
411+
</para>
409412
</listitem>
410413
</varlistentry>
411414

‎src/backend/commands/tablecmds.c

Lines changed: 18 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -2892,13 +2892,8 @@ AlterTableGetLockLevel(List *cmds)
28922892
break;
28932893

28942894
/*
2895-
* These subcommands affect write operations only. XXX
2896-
* Theoretically, these could be ShareRowExclusiveLock.
2895+
* These subcommands affect write operations only.
28972896
*/
2898-
caseAT_ColumnDefault:
2899-
caseAT_ProcessedConstraint:/* becomes AT_AddConstraint */
2900-
caseAT_AddConstraintRecurse:/* becomes AT_AddConstraint */
2901-
caseAT_ReAddConstraint:/* becomes AT_AddConstraint */
29022897
caseAT_EnableTrig:
29032898
caseAT_EnableAlwaysTrig:
29042899
caseAT_EnableReplicaTrig:
@@ -2907,6 +2902,14 @@ AlterTableGetLockLevel(List *cmds)
29072902
caseAT_DisableTrig:
29082903
caseAT_DisableTrigAll:
29092904
caseAT_DisableTrigUser:
2905+
cmd_lockmode=ShareRowExclusiveLock;
2906+
break;
2907+
2908+
/*
2909+
* These subcommands affect write operations only. XXX
2910+
* Theoretically, these could be ShareRowExclusiveLock.
2911+
*/
2912+
caseAT_ColumnDefault:
29102913
caseAT_AlterConstraint:
29112914
caseAT_AddIndex:/* from ADD CONSTRAINT */
29122915
caseAT_AddIndexConstraint:
@@ -2918,6 +2921,9 @@ AlterTableGetLockLevel(List *cmds)
29182921
break;
29192922

29202923
caseAT_AddConstraint:
2924+
caseAT_ProcessedConstraint:/* becomes AT_AddConstraint */
2925+
caseAT_AddConstraintRecurse:/* becomes AT_AddConstraint */
2926+
caseAT_ReAddConstraint:/* becomes AT_AddConstraint */
29212927
if (IsA(cmd->def,Constraint))
29222928
{
29232929
Constraint*con= (Constraint*)cmd->def;
@@ -2943,11 +2949,9 @@ AlterTableGetLockLevel(List *cmds)
29432949
/*
29442950
* We add triggers to both tables when we add a
29452951
* Foreign Key, so the lock level must be at least
2946-
* as strong as CREATE TRIGGER. XXX Might be set
2947-
* down to ShareRowExclusiveLock though trigger
2948-
* info is accessed by pg_get_triggerdef
2952+
* as strong as CREATE TRIGGER.
29492953
*/
2950-
cmd_lockmode=AccessExclusiveLock;
2954+
cmd_lockmode=ShareRowExclusiveLock;
29512955
break;
29522956

29532957
default:
@@ -6193,16 +6197,13 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
61936197
ListCell*old_pfeqop_item=list_head(fkconstraint->old_conpfeqop);
61946198

61956199
/*
6196-
* Grab an exclusive lock on the pk table, so that someone doesn't delete
6197-
* rows out from under us. (Although a lesser lock would do for that
6198-
* purpose, we'll need exclusive lock anyway to add triggers to the pk
6199-
* table; trying to start with a lesser lock will just create a risk of
6200-
* deadlock.)
6200+
* Grab ShareRowExclusiveLock on the pk table, so that someone doesn't
6201+
* delete rows out from under us.
62016202
*/
62026203
if (OidIsValid(fkconstraint->old_pktable_oid))
6203-
pkrel=heap_open(fkconstraint->old_pktable_oid,AccessExclusiveLock);
6204+
pkrel=heap_open(fkconstraint->old_pktable_oid,ShareRowExclusiveLock);
62046205
else
6205-
pkrel=heap_openrv(fkconstraint->pktable,AccessExclusiveLock);
6206+
pkrel=heap_openrv(fkconstraint->pktable,ShareRowExclusiveLock);
62066207

62076208
/*
62086209
* Validity checks (permission checks wait till we have the column

‎src/backend/commands/trigger.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -165,9 +165,9 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
165165
referenced;
166166

167167
if (OidIsValid(relOid))
168-
rel=heap_open(relOid,AccessExclusiveLock);
168+
rel=heap_open(relOid,ShareRowExclusiveLock);
169169
else
170-
rel=heap_openrv(stmt->relation,AccessExclusiveLock);
170+
rel=heap_openrv(stmt->relation,ShareRowExclusiveLock);
171171

172172
/*
173173
* Triggers must be on tables or views, and there are additional

0 commit comments

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