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

Commite1ccaff

Browse files
committed
Rework parsing of ConstraintAttributeSpec to improve NOT VALID handling.
The initial commit of the ALTER TABLE ADD FOREIGN KEY NOT VALID featurefailed to support labeling such constraints as deferrable. The best fixfor this seems to be to fold NOT VALID into ConstraintAttributeSpec.That's a bit more general than the documented syntax, but it allowsbetter-targeted syntax error messages.In addition, do some mostly-but-not-entirely-cosmetic code review forthe whole NOT VALID patch.
1 parente3df357 commite1ccaff

File tree

9 files changed

+175
-115
lines changed

9 files changed

+175
-115
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1898,7 +1898,8 @@
18981898
<entry><structfield>convalidated</structfield></entry>
18991899
<entry><type>bool</type></entry>
19001900
<entry></entry>
1901-
<entry>Has the constraint been validated? Can only be false for foreign keys</entry>
1901+
<entry>Has the constraint been validated?
1902+
Currently, can only be false for foreign keys</entry>
19021903
</row>
19031904

19041905
<row>

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

Lines changed: 28 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -42,9 +42,8 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
4242
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET ( <replaceable class="PARAMETER">attribute_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
4343
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> RESET ( <replaceable class="PARAMETER">attribute_option</replaceable> [, ... ] )
4444
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
45-
ADD <replaceable class="PARAMETER">table_constraint</replaceable>
46-
ADD <replaceable class="PARAMETER">table_constraint_using_index</replaceable>
4745
ADD <replaceable class="PARAMETER">table_constraint</replaceable> [ NOT VALID ]
46+
ADD <replaceable class="PARAMETER">table_constraint_using_index</replaceable>
4847
VALIDATE CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable>
4948
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
5049
DISABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
@@ -235,27 +234,21 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
235234
</varlistentry>
236235

237236
<varlistentry>
238-
<term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable>
239-
[ NOT VALID ]</literal></term>
237+
<term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable> [ NOT VALID ]</literal></term>
240238
<listitem>
241239
<para>
242240
This form adds a new constraint to a table using the same syntax as
243-
<xref linkend="SQL-CREATETABLE">. Newly added foreign key constraints can
244-
also be defined as <literal>NOT VALID</literal> to avoid the
245-
potentially lengthy initial check that must otherwise be performed.
246-
Constraint checks are skipped at create table time, so
247-
<xref linkend="SQL-CREATETABLE"> does not contain this option.
248-
</para>
249-
</listitem>
250-
</varlistentry>
251-
252-
<varlistentry>
253-
<term><literal>VALIDATE CONSTRAINT</literal></term>
254-
<listitem>
255-
<para>
256-
This form validates a foreign key constraint that was previously created
257-
as <literal>NOT VALID</literal>. Constraints already marked valid do not
258-
cause an error response.
241+
<xref linkend="SQL-CREATETABLE">, plus the option <literal>NOT
242+
VALID</literal>, which is currently only allowed for foreign key
243+
constraints.
244+
If the constraint is marked <literal>NOT VALID</literal>, the
245+
potentially-lengthy initial check to verify that all rows in the table
246+
satisfy the constraint is skipped. The constraint will still be
247+
enforced against subsequent inserts or updates (that is, they'll fail
248+
unless there is a matching row in the referenced table). But the
249+
database will not assume that the constraint holds for all rows in
250+
the table, until it is validated by using the <literal>VALIDATE
251+
CONSTRAINT</literal> option.
259252
</para>
260253
</listitem>
261254
</varlistentry>
@@ -311,6 +304,21 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
311304
</listitem>
312305
</varlistentry>
313306

307+
<varlistentry>
308+
<term><literal>VALIDATE CONSTRAINT</literal></term>
309+
<listitem>
310+
<para>
311+
This form validates a foreign key constraint that was previously created
312+
as <literal>NOT VALID</literal>, by scanning the table to ensure there
313+
are no unmatched rows. Nothing happens if the constraint is
314+
already marked valid.
315+
The value of separating validation from initial creation of the
316+
constraint is that validation requires a lesser lock on the table
317+
than constraint creation does.
318+
</para>
319+
</listitem>
320+
</varlistentry>
321+
314322
<varlistentry>
315323
<term><literal>DROP CONSTRAINT [ IF EXISTS ]</literal></term>
316324
<listitem>

‎src/backend/commands/tablecmds.c

Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -258,7 +258,7 @@ static void AlterIndexNamespaces(Relation classRel, Relation rel,
258258
staticvoidAlterSeqNamespaces(RelationclassRel,Relationrel,
259259
OidoldNspOid,OidnewNspOid,
260260
constchar*newNspName,LOCKMODElockmode);
261-
staticvoidATExecValidateConstraint(Relationrel,constchar*constrName);
261+
staticvoidATExecValidateConstraint(Relationrel,char*constrName);
262262
staticinttransformColumnNameList(OidrelId,List*colList,
263263
int16*attnums,Oid*atttypids);
264264
staticinttransformFkeyGetPrimaryKey(Relationpkrel,Oid*indexOid,
@@ -5726,9 +5726,9 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
57265726
createForeignKeyTriggers(rel,fkconstraint,constrOid,indexOid);
57275727

57285728
/*
5729-
* Tell Phase 3 to check that the constraint is satisfied by existing rows
5730-
* We can skip this during table creation or if requested explicitly by
5731-
* specifying NOT VALIDon analter table statement.
5729+
* Tell Phase 3 to check that the constraint is satisfied by existing rows.
5730+
* We can skip this during table creation, or if requested explicitly by
5731+
* specifying NOT VALIDin anADD FOREIGN KEY command.
57325732
*/
57335733
if (!fkconstraint->skip_validation)
57345734
{
@@ -5755,7 +5755,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
57555755
* ALTER TABLE VALIDATE CONSTRAINT
57565756
*/
57575757
staticvoid
5758-
ATExecValidateConstraint(Relationrel,constchar*constrName)
5758+
ATExecValidateConstraint(Relationrel,char*constrName)
57595759
{
57605760
Relationconrel;
57615761
SysScanDescscan;
@@ -5810,7 +5810,7 @@ ATExecValidateConstraint(Relation rel, const char *constrName)
58105810
*/
58115811
refrel=heap_open(con->confrelid,RowShareLock);
58125812

5813-
validateForeignKeyConstraint((char*)constrName,rel,refrel,
5813+
validateForeignKeyConstraint(constrName,rel,refrel,
58145814
con->conindid,
58155815
conid);
58165816

@@ -5830,6 +5830,7 @@ ATExecValidateConstraint(Relation rel, const char *constrName)
58305830
heap_close(conrel,RowExclusiveLock);
58315831
}
58325832

5833+
58335834
/*
58345835
* transformColumnNameList - transform list of column names
58355836
*

‎src/backend/commands/trigger.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1006,6 +1006,8 @@ ConvertTriggerToFK(CreateTrigStmt *stmt, Oid funcoid)
10061006
}
10071007
fkcon->deferrable=stmt->deferrable;
10081008
fkcon->initdeferred=stmt->initdeferred;
1009+
fkcon->skip_validation= false;
1010+
fkcon->initially_valid= true;
10091011

10101012
/* ... and execute it */
10111013
ProcessUtility((Node*)atstmt,

0 commit comments

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