Skip to content

Commit 722bf70

Browse files
simonat2ndQuadrantsimonat2ndQuadrant
committed
Extend ALTER TABLE to allow Foreign Keys to be added without initial validation.
FK constraints that are marked NOT VALID may later be VALIDATED, which uses an ShareUpdateExclusiveLock on constraint table and RowShareLock on referenced table. Significantly reduces lock strength and duration when adding FKs. New state visible from psql. Simon Riggs, with reviews from Marko Tiikkaja and Robert Haas
1 parent 7202ad7 commit 722bf70

File tree

15 files changed

+215
-32
lines changed

15 files changed

+215
-32
lines changed

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

Lines changed: 20 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -44,6 +44,8 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
4444
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
4545
ADD <replaceable class="PARAMETER">table_constraint</replaceable>
4646
ADD <replaceable class="PARAMETER">table_constraint_using_index</replaceable>
47+
ADD <replaceable class="PARAMETER">table_constraint</replaceable> [ NOT VALID ]
48+
VALIDATE CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable>
4749
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
4850
DISABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
4951
ENABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
@@ -227,11 +229,27 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
227229
</varlistentry>
228230

229231
<varlistentry>
230-
<term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable></literal></term>
232+
<term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable>
233+
[ NOT VALID ]</literal></term>
231234
<listitem>
232235
<para>
233236
This form adds a new constraint to a table using the same syntax as
234-
<xref linkend="SQL-CREATETABLE">.
237+
<xref linkend="SQL-CREATETABLE">. Newly added foreign key constraints can
238+
also be defined as <literal>NOT VALID</literal> to avoid the
239+
potentially lengthy initial check that must otherwise be performed.
240+
Constraint checks are skipped at create table time, so
241+
<xref linkend="SQL-CREATETABLE"> does not contain this option.
242+
</para>
243+
</listitem>
244+
</varlistentry>
245+
246+
<varlistentry>
247+
<term><literal>VALIDATE CONSTRAINT</literal></term>
248+
<listitem>
249+
<para>
250+
This form validates a foreign key constraint that was previously created
251+
as <literal>NOT VALID</literal>. Constraints already marked valid do not
252+
cause an error response.
235253
</para>
236254
</listitem>
237255
</varlistentry>

‎src/backend/catalog/heap.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1837,6 +1837,7 @@ StoreRelCheck(Relation rel, char *ccname, Node *expr,
18371837
CONSTRAINT_CHECK, /* Constraint Type */
18381838
false, /* Is Deferrable */
18391839
false, /* Is Deferred */
1840+
true, /* Is Validated */
18401841
RelationGetRelid(rel), /* relation */
18411842
attNos, /* attrs in the constraint */
18421843
keycount, /* # attrs in the constraint */

‎src/backend/catalog/index.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1103,6 +1103,7 @@ index_constraint_create(Relation heapRelation,
11031103
constraintType,
11041104
deferrable,
11051105
initdeferred,
1106+
true,
11061107
RelationGetRelid(heapRelation),
11071108
indexInfo->ii_KeyAttrNumbers,
11081109
indexInfo->ii_NumIndexAttrs,

‎src/backend/catalog/pg_constraint.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -46,6 +46,7 @@ CreateConstraintEntry(const char *constraintName,
4646
char constraintType,
4747
bool isDeferrable,
4848
bool isDeferred,
49+
bool isValidated,
4950
Oid relId,
5051
const int16 *constraintKey,
5152
int constraintNKeys,
@@ -158,6 +159,7 @@ CreateConstraintEntry(const char *constraintName,
158159
values[Anum_pg_constraint_contype - 1] = CharGetDatum(constraintType);
159160
values[Anum_pg_constraint_condeferrable - 1] = BoolGetDatum(isDeferrable);
160161
values[Anum_pg_constraint_condeferred - 1] = BoolGetDatum(isDeferred);
162+
values[Anum_pg_constraint_convalidated - 1] = BoolGetDatum(isValidated);
161163
values[Anum_pg_constraint_conrelid - 1] = ObjectIdGetDatum(relId);
162164
values[Anum_pg_constraint_contypid - 1] = ObjectIdGetDatum(domainId);
163165
values[Anum_pg_constraint_conindid - 1] = ObjectIdGetDatum(indexRelId);

‎src/backend/commands/tablecmds.c

Lines changed: 99 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -254,6 +254,7 @@ static void AlterIndexNamespaces(Relation classRel, Relation rel,
254254
static void AlterSeqNamespaces(Relation classRel, Relation rel,
255255
Oid oldNspOid, Oid newNspOid,
256256
const char *newNspName, LOCKMODE lockmode);
257+
static void ATExecValidateConstraint(Relation rel, const char *constrName);
257258
static int transformColumnNameList(Oid relId, List *colList,
258259
int16 *attnums, Oid *atttypids);
259260
static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
@@ -264,7 +265,7 @@ static Oid transformFkeyCheckAttrs(Relation pkrel,
264265
int numattrs, int16 *attnums,
265266
Oid *opclasses);
266267
static void checkFkeyPermissions(Relation rel, int16 *attnums, int natts);
267-
static void validateForeignKeyConstraint(Constraint *fkconstraint,
268+
static void validateForeignKeyConstraint(char *conname,
268269
Relation rel, Relation pkrel,
269270
Oid pkindOid, Oid constraintOid);
270271
static void createForeignKeyTriggers(Relation rel, Constraint *fkconstraint,
@@ -2649,7 +2650,7 @@ AlterTableGetLockLevel(List *cmds)
26492650
* though don't change the semantic results from normal data reads and writes.
26502651
* Delaying an ALTER TABLE behind currently active writes only delays the point
26512652
* where the new strategy begins to take effect, so there is no benefit in waiting.
2652-
* In thise case the minimum restriction applies: we don't currently allow
2653+
* In this case the minimum restriction applies: we don't currently allow
26532654
* concurrent catalog updates.
26542655
*/
26552656
case AT_SetStatistics:
@@ -2660,6 +2661,7 @@ AlterTableGetLockLevel(List *cmds)
26602661
case AT_SetOptions:
26612662
case AT_ResetOptions:
26622663
case AT_SetStorage:
2664+
case AT_ValidateConstraint:
26632665
cmd_lockmode = ShareUpdateExclusiveLock;
26642666
break;
26652667

@@ -2887,6 +2889,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
28872889
ATPrepAddInherit(rel);
28882890
pass = AT_PASS_MISC;
28892891
break;
2892+
case AT_ValidateConstraint:
28902893
case AT_EnableTrig: /* ENABLE TRIGGER variants */
28912894
case AT_EnableAlwaysTrig:
28922895
case AT_EnableReplicaTrig:
@@ -3054,6 +3057,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
30543057
case AT_AddIndexConstraint: /* ADD CONSTRAINT USING INDEX */
30553058
ATExecAddIndexConstraint(tab, rel, (IndexStmt *) cmd->def, lockmode);
30563059
break;
3060+
case AT_ValidateConstraint:
3061+
ATExecValidateConstraint(rel, cmd->name);
3062+
break;
30573063
case AT_DropConstraint: /* DROP CONSTRAINT */
30583064
ATExecDropConstraint(rel, cmd->name, cmd->behavior,
30593065
false, false,
@@ -3307,10 +3313,15 @@ ATRewriteTables(List **wqueue, LOCKMODE lockmode)
33073313
*/
33083314
refrel = heap_open(con->refrelid, ShareRowExclusiveLock);
33093315

3310-
validateForeignKeyConstraint(fkconstraint, rel, refrel,
3316+
validateForeignKeyConstraint(fkconstraint->conname, rel, refrel,
33113317
con->refindid,
33123318
con->conid);
33133319

3320+
/*
3321+
* No need to mark the constraint row as validated,
3322+
* we did that when we inserted the row earlier.
3323+
*/
3324+
33143325
heap_close(refrel, NoLock);
33153326
}
33163327
}
@@ -5509,6 +5520,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
55095520
CONSTRAINT_FOREIGN,
55105521
fkconstraint->deferrable,
55115522
fkconstraint->initdeferred,
5523+
!fkconstraint->skip_validation,
55125524
RelationGetRelid(rel),
55135525
fkattnum,
55145526
numfks,
@@ -5538,7 +5550,8 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
55385550

55395551
/*
55405552
* Tell Phase 3 to check that the constraint is satisfied by existing rows
5541-
* (we can skip this during table creation).
5553+
* We can skip this during table creation or if requested explicitly
5554+
* by specifying NOT VALID on an alter table statement.
55425555
*/
55435556
if (!fkconstraint->skip_validation)
55445557
{
@@ -5561,6 +5574,86 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
55615574
heap_close(pkrel, NoLock);
55625575
}
55635576

5577+
/*
5578+
* ALTER TABLE VALIDATE CONSTRAINT
5579+
*/
5580+
static void
5581+
ATExecValidateConstraint(Relation rel, const char *constrName)
5582+
{
5583+
Relation conrel;
5584+
Form_pg_constraint con;
5585+
SysScanDesc scan;
5586+
ScanKeyData key;
5587+
HeapTuple tuple;
5588+
bool found = false;
5589+
Oid conid;
5590+
5591+
conrel = heap_open(ConstraintRelationId, RowExclusiveLock);
5592+
5593+
/*
5594+
* Find and the target constraint
5595+
*/
5596+
ScanKeyInit(&key,
5597+
Anum_pg_constraint_conrelid,
5598+
BTEqualStrategyNumber, F_OIDEQ,
5599+
ObjectIdGetDatum(RelationGetRelid(rel)));
5600+
scan = systable_beginscan(conrel, ConstraintRelidIndexId,
5601+
true, SnapshotNow, 1, &key);
5602+
5603+
while (HeapTupleIsValid(tuple = systable_getnext(scan)))
5604+
{
5605+
con = (Form_pg_constraint) GETSTRUCT(tuple);
5606+
5607+
if (strcmp(NameStr(con->conname), constrName) != 0)
5608+
continue;
5609+
5610+
conid = HeapTupleGetOid(tuple);
5611+
found = true;
5612+
break;
5613+
}
5614+
5615+
if (found && con->contype == CONSTRAINT_FOREIGN && !con->convalidated)
5616+
{
5617+
HeapTuple copyTuple = heap_copytuple(tuple);
5618+
Form_pg_constraint copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
5619+
Relation refrel;
5620+
5621+
/*
5622+
* Triggers are already in place on both tables, so a
5623+
* concurrent write that alters the result here is not
5624+
* possible. Normally we can run a query here to do the
5625+
* validation, which would only require AccessShareLock.
5626+
* In some cases, it is possible that we might need to
5627+
* fire triggers to perform the check, so we take a lock
5628+
* at RowShareLock level just in case.
5629+
*/
5630+
refrel = heap_open(con->confrelid, RowShareLock);
5631+
5632+
validateForeignKeyConstraint((char *)constrName, rel, refrel,
5633+
con->conindid,
5634+
conid);
5635+
5636+
/*
5637+
* Now update the catalog, while we have the door open.
5638+
*/
5639+
copy_con->convalidated = true;
5640+
simple_heap_update(conrel, &copyTuple->t_self, copyTuple);
5641+
CatalogUpdateIndexes(conrel, copyTuple);
5642+
heap_freetuple(copyTuple);
5643+
heap_close(refrel, NoLock);
5644+
}
5645+
5646+
systable_endscan(scan);
5647+
heap_close(conrel, RowExclusiveLock);
5648+
5649+
if (!found)
5650+
{
5651+
ereport(ERROR,
5652+
(errcode(ERRCODE_UNDEFINED_OBJECT),
5653+
errmsg("foreign key constraint \"%s\" of relation \"%s\" does not exist",
5654+
constrName, RelationGetRelationName(rel))));
5655+
}
5656+
}
55645657

55655658
/*
55665659
* transformColumnNameList - transform list of column names
@@ -5866,7 +5959,7 @@ checkFkeyPermissions(Relation rel, int16 *attnums, int natts)
58665959
* Caller must have opened and locked both relations appropriately.
58675960
*/
58685961
static void
5869-
validateForeignKeyConstraint(Constraint *fkconstraint,
5962+
validateForeignKeyConstraint(char *conname,
58705963
Relation rel,
58715964
Relation pkrel,
58725965
Oid pkindOid,
@@ -5881,7 +5974,7 @@ validateForeignKeyConstraint(Constraint *fkconstraint,
58815974
*/
58825975
MemSet(&trig, 0, sizeof(trig));
58835976
trig.tgoid = InvalidOid;
5884-
trig.tgname = fkconstraint->conname;
5977+
trig.tgname = conname;
58855978
trig.tgenabled = TRIGGER_FIRES_ON_ORIGIN;
58865979
trig.tgisinternal = TRUE;
58875980
trig.tgconstrrelid = RelationGetRelid(pkrel);

‎src/backend/commands/trigger.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -422,6 +422,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
422422
CONSTRAINT_TRIGGER,
423423
stmt->deferrable,
424424
stmt->initdeferred,
425+
true,
425426
RelationGetRelid(rel),
426427
NULL, /* no conkey */
427428
0,

‎src/backend/commands/typecmds.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2378,6 +2378,7 @@ domainAddConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid,
23782378
CONSTRAINT_CHECK, /* Constraint Type */
23792379
false, /* Is Deferrable */
23802380
false, /* Is Deferred */
2381+
true, /* Is Validated */
23812382
InvalidOid, /* not a relation constraint */
23822383
NULL,
23832384
0,

‎src/backend/parser/gram.y

Lines changed: 26 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -546,7 +546,7 @@ static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
546546
UNBOUNDED UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNLOGGED
547547
UNTIL UPDATE USER USING
548548

549-
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
549+
VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
550550
VERBOSE VERSION_P VIEW VOLATILE
551551

552552
WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRAPPER WRITE
@@ -1752,6 +1752,14 @@ alter_table_cmd:
17521752
n->def = $2;
17531753
$$ = (Node *)n;
17541754
}
1755+
/* ALTER TABLE <name> VALIDATE CONSTRAINT ... */
1756+
| VALIDATE CONSTRAINT name
1757+
{
1758+
AlterTableCmd *n = makeNode(AlterTableCmd);
1759+
n->subtype = AT_ValidateConstraint;
1760+
n->name = $3;
1761+
$$ = (Node *)n;
1762+
}
17551763
/* ALTER TABLE <name> DROP CONSTRAINT IF EXISTS <name> [RESTRICT|CASCADE] */
17561764
| DROP CONSTRAINT IF_P EXISTS name opt_drop_behavior
17571765
{
@@ -2743,9 +2751,25 @@ ConstraintElem:
27432751
n->fk_matchtype = $9;
27442752
n->fk_upd_action = (char) ($10 >> 8);
27452753
n->fk_del_action = (char) ($10 & 0xFF);
2746-
n->skip_validation = FALSE;
27472754
n->deferrable = ($11 & 1) != 0;
27482755
n->initdeferred = ($11 & 2) != 0;
2756+
n->skip_validation = false;
2757+
$$ = (Node *)n;
2758+
}
2759+
| FOREIGN KEY '(' columnList ')' REFERENCES qualified_name
2760+
opt_column_list key_match key_actions
2761+
NOT VALID
2762+
{
2763+
Constraint *n = makeNode(Constraint);
2764+
n->contype = CONSTR_FOREIGN;
2765+
n->location = @1;
2766+
n->pktable = $7;
2767+
n->fk_attrs = $4;
2768+
n->pk_attrs = $8;
2769+
n->fk_matchtype = $9;
2770+
n->fk_upd_action = (char) ($10 >> 8);
2771+
n->fk_del_action = (char) ($10 & 0xFF);
2772+
n->skip_validation = true;
27492773
$$ = (Node *)n;
27502774
}
27512775
;

‎src/backend/utils/adt/ri_triggers.c

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2608,8 +2608,11 @@ RI_FKey_keyequal_upd_fk(Trigger *trigger, Relation fk_rel,
26082608
* This is not a trigger procedure, but is called during ALTER TABLE
26092609
* ADD FOREIGN KEY to validate the initial table contents.
26102610
*
2611-
* We expect that a ShareRowExclusiveLock or higher has been taken on rel and pkrel;
2612-
* hence, we do not need to lock individual rows for the check.
2611+
* We expect that the caller has made provision to prevent any problems
2612+
* caused by concurrent actions. This could be either by locking rel and
2613+
* pkrel at ShareRowExclusiveLock or higher, or by otherwise ensuring
2614+
* that triggers implementing the checks are already active.
2615+
* Hence, we do not need to lock individual rows for the check.
26132616
*
26142617
* If the check fails because the current user doesn't have permissions
26152618
* to read both tables, return false to let our caller know that they will

‎src/bin/psql/describe.c

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1714,8 +1714,10 @@ describeOneTableDetails(const char *schemaname,
17141714
{
17151715
printfPQExpBuffer(&buf,
17161716
"SELECT conname,\n"
1717-
" pg_catalog.pg_get_constraintdef(r.oid, true) as condef\n"
1718-
"FROM pg_catalog.pg_constraint r\n"
1717+
" pg_catalog.pg_get_constraintdef(r.oid, true) as condef\n");
1718+
if (pset.sversion >= 90100)
1719+
appendPQExpBuffer(&buf, " ,convalidated\n");
1720+
appendPQExpBuffer(&buf, "FROM pg_catalog.pg_constraint r\n"
17191721
"WHERE r.conrelid = '%s' AND r.contype = 'f' ORDER BY 1",
17201722
oid);
17211723
result = PSQLexec(buf.data, false);
@@ -1734,6 +1736,9 @@ describeOneTableDetails(const char *schemaname,
17341736
PQgetvalue(result, i, 0),
17351737
PQgetvalue(result, i, 1));
17361738

1739+
if (strcmp(PQgetvalue(result, i, 2), "f") == 0)
1740+
appendPQExpBuffer(&buf, " NOT VALID");
1741+
17371742
printTableAddFooter(&cont, buf.data);
17381743
}
17391744
}

0 commit comments

Comments
 (0)