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

Commit722bf70

Browse files
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 anShareUpdateExclusiveLock on constraint table and RowShareLock on referencedtable. 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 parent7202ad7 commit722bf70

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
charconstraintType,
4747
boolisDeferrable,
4848
boolisDeferred,
49+
boolisValidated,
4950
OidrelId,
5051
constint16*constraintKey,
5152
intconstraintNKeys,
@@ -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
staticvoidAlterSeqNamespaces(RelationclassRel,Relationrel,
255255
OidoldNspOid,OidnewNspOid,
256256
constchar*newNspName,LOCKMODElockmode);
257+
staticvoidATExecValidateConstraint(Relationrel,constchar*constrName);
257258
staticinttransformColumnNameList(OidrelId,List*colList,
258259
int16*attnums,Oid*atttypids);
259260
staticinttransformFkeyGetPrimaryKey(Relationpkrel,Oid*indexOid,
@@ -264,7 +265,7 @@ static Oid transformFkeyCheckAttrs(Relation pkrel,
264265
intnumattrs,int16*attnums,
265266
Oid*opclasses);
266267
staticvoidcheckFkeyPermissions(Relationrel,int16*attnums,intnatts);
267-
staticvoidvalidateForeignKeyConstraint(Constraint*fkconstraint,
268+
staticvoidvalidateForeignKeyConstraint(char*conname,
268269
Relationrel,Relationpkrel,
269270
OidpkindOid,OidconstraintOid);
270271
staticvoidcreateForeignKeyTriggers(Relationrel,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-
* Inthise case the minimum restriction applies: we don't currently allow
2653+
* Inthis case the minimum restriction applies: we don't currently allow
26532654
* concurrent catalog updates.
26542655
*/
26552656
caseAT_SetStatistics:
@@ -2660,6 +2661,7 @@ AlterTableGetLockLevel(List *cmds)
26602661
caseAT_SetOptions:
26612662
caseAT_ResetOptions:
26622663
caseAT_SetStorage:
2664+
caseAT_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+
caseAT_ValidateConstraint:
28902893
caseAT_EnableTrig:/* ENABLE TRIGGER variants */
28912894
caseAT_EnableAlwaysTrig:
28922895
caseAT_EnableReplicaTrig:
@@ -3054,6 +3057,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
30543057
caseAT_AddIndexConstraint:/* ADD CONSTRAINT USING INDEX */
30553058
ATExecAddIndexConstraint(tab,rel, (IndexStmt*)cmd->def,lockmode);
30563059
break;
3060+
caseAT_ValidateConstraint:
3061+
ATExecValidateConstraint(rel,cmd->name);
3062+
break;
30573063
caseAT_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+
staticvoid
5581+
ATExecValidateConstraint(Relationrel,constchar*constrName)
5582+
{
5583+
Relationconrel;
5584+
Form_pg_constraintcon;
5585+
SysScanDescscan;
5586+
ScanKeyDatakey;
5587+
HeapTupletuple;
5588+
boolfound= false;
5589+
Oidconid;
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+
HeapTuplecopyTuple=heap_copytuple(tuple);
5618+
Form_pg_constraintcopy_con= (Form_pg_constraint)GETSTRUCT(copyTuple);
5619+
Relationrefrel;
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
staticvoid
5869-
validateForeignKeyConstraint(Constraint*fkconstraint,
5962+
validateForeignKeyConstraint(char*conname,
58705963
Relationrel,
58715964
Relationpkrel,
58725965
OidpkindOid,
@@ -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 VALIDVALIDATEVALIDATOR 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+
|VALIDATECONSTRAINTname
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
|DROPCONSTRAINTIF_PEXISTSnameopt_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+
|FOREIGNKEY'('columnList')'REFERENCESqualified_name
2760+
opt_column_listkey_matchkey_actions
2761+
NOTVALID
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)
[8]ページ先頭
©2009-2025 Movatter.jp