8000 Reduce lock levels of some trigger DDL and add FKs · postgrespro/postgres_cluster@0ef0396 · GitHub
[go: up one dir, main page]
Skip to content

Commit 0ef0396

Browse files
simonat2ndQuadrantsimonat2ndQuadrant
committed
8000
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 KEY Original work by Simon Riggs, extracted and refreshed by Andreas Karlsson New test cases added by Andreas Karlsson Reviewed by Noah Misch, Andres Freund, Michael Paquier and Simon Riggs
1 parent ca68053 commit 0ef0396

File tree

7 files changed

+1236
-489
lines changed
  • regress/expected

    • 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 specify a 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-
    case AT_ColumnDefault:
    2899-
    case AT_ProcessedConstraint: /* becomes AT_AddConstraint */
    2900-
    case AT_AddConstraintRecurse: /* becomes AT_AddConstraint */
    2901-
    case AT_ReAddConstraint: /* becomes AT_AddConstraint */
    29022897
    case AT_EnableTrig:
    29032898
    case AT_EnableAlwaysTrig:
    29042899
    case AT_EnableReplicaTrig:
    @@ -2907,6 +2902,14 @@ AlterTableGetLockLevel(List *cmds)
    29072902
    case AT_DisableTrig:
    29082903
    case AT_DisableTrigAll:
    29092904
    case AT_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+
    case AT_ColumnDefault:
    29102913
    case AT_AlterConstraint:
    29112914
    case AT_AddIndex: /* from ADD CONSTRAINT */
    29122915
    case AT_AddIndexConstraint:
    @@ -2918,6 +2921,9 @@ AlterTableGetLockLevel(List *cmds)
    29182921
    break;
    29192922

    29202923
    case AT_AddConstraint:
    2924+
    case AT_ProcessedConstraint: /* becomes AT_AddConstraint */
    2925+
    case AT_AddConstraintRecurse: /* becomes AT_AddConstraint */
    2926+
    case AT_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)
    0