On Mon, 2010-12-13 at 17:15 +0000, Peter Geoghegan wrote:
> On 13 December 2010 16:08, Robert Haas <robertmh...@gmail.com> wrote:
> > On Mon, Dec 13, 2010 at 10:49 AM, Simon Riggs <si...@2ndquadrant.com> wrote:
> >> 2. pg_validate_foreign_key('constraint name');
> >> Returns immediately if FK is valid
> >> Returns SETOF rows that violate the constraint, or if no rows are
> >> returned it updates constraint to show it is now valid.
> >> Lock held: AccessShareLock
> >
> > I'm less sure about this part. I think there should be a DDL
> > statement to validate the foreign key. The "return the problem" rows
> > behavior could be done some other way, or just left to the user to
> > write their own query.
>
> +1. I think that a DDL statement is more appropriate, because it makes
> the process sort of symmetrical.
Patch to implement the proposed feature attached, for CFJan2011.
2 sub-command changes:
ALTER TABLE foo ADD FOREIGN KEY fkoo ... NOT VALID;
ALTER TABLE foo VALIDATE CONSTRAINT fkoo;
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 17a1d34..5b90e9e 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -42,7 +42,8 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET ( <replaceable class="PARAMETER">attribute_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> RESET ( <replaceable class="PARAMETER">attribute_option</replaceable> [, ... ] )
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
- ADD <replaceable class="PARAMETER">table_constraint</replaceable>
+ ADD <replaceable class="PARAMETER">table_constraint</replaceable> [ NOT VALID ]
+ VALIDATE CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable>
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
ENABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
@@ -220,11 +221,27 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
</varlistentry>
<varlistentry>
- <term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable></literal></term>
+ <term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable>
+ [ NOT VALID ]</literal></term>
<listitem>
<para>
This form adds a new constraint to a table using the same syntax as
- <xref linkend="SQL-CREATETABLE">.
+ <xref linkend="SQL-CREATETABLE">. Newly added foreign key constraints can
+ also be defined as <literal>NOT VALID</literal> to avoid the
+ potentially lengthy initial check that must otherwise be performed.
+ Constraint checks are skipped at create table time, so
+ <xref linkend="SQL-CREATETABLE"> does not contain this option.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>VALIDATE CONSTRAINT</literal></term>
+ <listitem>
+ <para>
+ This form validates a foreign key constraint that was previously created
+ as <literal>NOT VALID</literal>. Constraints already marked valid do not
+ cause an error response.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 4c55db7..7cc521d 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -1835,6 +1835,7 @@ StoreRelCheck(Relation rel, char *ccname, Node *expr,
CONSTRAINT_CHECK, /* Constraint Type */
false, /* Is Deferrable */
false, /* Is Deferred */
+ true, /* Is Validated */
RelationGetRelid(rel), /* relation */
attNos, /* attrs in the constraint */
keycount, /* # attrs in the constraint */
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 4dd89e1..a19b139 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -777,6 +777,7 @@ index_create(Oid heapRelationId,
constraintType,
deferrable,
initdeferred,
+ true,
heapRelationId,
indexInfo->ii_KeyAttrNumbers,
indexInfo->ii_NumIndexAttrs,
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 3a38518..6619eed 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -46,6 +46,7 @@ CreateConstraintEntry(const char *constraintName,
char constraintType,
bool isDeferrable,
bool isDeferred,
+ bool isValidated,
Oid relId,
const int16 *constraintKey,
int constraintNKeys,
@@ -158,6 +159,7 @@ CreateConstraintEntry(const char *constraintName,
values[Anum_pg_constraint_contype - 1] = CharGetDatum(constraintType);
values[Anum_pg_constraint_condeferrable - 1] = BoolGetDatum(isDeferrable);
values[Anum_pg_constraint_condeferred - 1] = BoolGetDatum(isDeferred);
+ values[Anum_pg_constraint_convalidated - 1] = BoolGetDatum(isValidated);
values[Anum_pg_constraint_conrelid - 1] = ObjectIdGetDatum(relId);
values[Anum_pg_constraint_contypid - 1] = ObjectIdGetDatum(domainId);
values[Anum_pg_constraint_conindid - 1] = ObjectIdGetDatum(indexRelId);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f3bd565..8041a9b 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -254,6 +254,7 @@ static void AlterIndexNamespaces(Relation classRel, Relation rel,
static void AlterSeqNamespaces(Relation classRel, Relation rel,
Oid oldNspOid, Oid newNspOid,
const char *newNspName, LOCKMODE lockmode);
+static void ATExecValidateConstraint(Relation rel, const char *constrName);
static int transformColumnNameList(Oid relId, List *colList,
int16 *attnums, Oid *atttypids);
static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
@@ -264,9 +265,9 @@ static Oid transformFkeyCheckAttrs(Relation pkrel,
int numattrs, int16 *attnums,
Oid *opclasses);
static void checkFkeyPermissions(Relation rel, int16 *attnums, int natts);
-static void validateForeignKeyConstraint(Constraint *fkconstraint,
+static void validateForeignKeyConstraint(char *conname,
Relation rel, Relation pkrel,
- Oid pkindOid, Oid constraintOid);
+ Oid pkindOid, Oid constraintOid, bool must_use_query);
static void createForeignKeyTriggers(Relation rel, Constraint *fkconstraint,
Oid constraintOid, Oid indexOid);
static void ATController(Relation rel, List *cmds, bool recurse, LOCKMODE lockmode);
@@ -2646,7 +2647,7 @@ AlterTableGetLockLevel(List *cmds)
* though don't change the semantic results from normal data reads and writes.
* Delaying an ALTER TABLE behind currently active writes only delays the point
* where the new strategy begins to take effect, so there is no benefit in waiting.
- * In thise case the minimum restriction applies: we don't currently allow
+ * In these case the minimum restriction applies: we don't currently allow
* concurrent catalog updates.
*/
case AT_SetStatistics:
@@ -2657,6 +2658,7 @@ AlterTableGetLockLevel(List *cmds)
case AT_SetOptions:
case AT_ResetOptions:
case AT_SetStorage:
+ case AT_ValidateConstraint:
cmd_lockmode = ShareUpdateExclusiveLock;
break;
@@ -2878,6 +2880,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
ATPrepAddInherit(rel);
pass = AT_PASS_MISC;
break;
+ case AT_ValidateConstraint:
case AT_EnableTrig: /* ENABLE TRIGGER variants */
case AT_EnableAlwaysTrig:
case AT_EnableReplicaTrig:
@@ -3042,6 +3045,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
ATExecAddConstraint(wqueue, tab, rel, (Constraint *) cmd->def,
true, lockmode);
break;
+ case AT_ValidateConstraint:
+ ATExecValidateConstraint(rel, cmd->name);
+ break;
case AT_DropConstraint: /* DROP CONSTRAINT */
ATExecDropConstraint(rel, cmd->name, cmd->behavior,
false, false,
@@ -3294,9 +3300,15 @@ ATRewriteTables(List **wqueue, LOCKMODE lockmode)
*/
refrel = heap_open(con->refrelid, ShareRowExclusiveLock);
- validateForeignKeyConstraint(fkconstraint, rel, refrel,
+ validateForeignKeyConstraint(fkconstraint->conname, rel, refrel,
con->refindid,
- con->conid);
+ con->conid,
+ false);
+
+ /*
+ * No need to mark the constraint row as validated,
+ * we did that when we inserted the row earlier.
+ */
heap_close(refrel, NoLock);
}
@@ -5422,6 +5434,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
CONSTRAINT_FOREIGN,
fkconstraint->deferrable,
fkconstraint->initdeferred,
+ !fkconstraint->skip_validation,
RelationGetRelid(rel),
fkattnum,
numfks,
@@ -5451,7 +5464,8 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
/*
* Tell Phase 3 to check that the constraint is satisfied by existing rows
- * (we can skip this during table creation).
+ * We can skip this during table creation or if requested explicitly
+ * by specifying NOT VALID on an alter table statement.
*/
if (!fkconstraint->skip_validation)
{
@@ -5474,6 +5488,81 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
heap_close(pkrel, NoLock);
}
+/*
+ * ALTER TABLE VALIDATE CONSTRAINT
+ */
+static void
+ATExecValidateConstraint(Relation rel, const char *constrName)
+{
+ Relation conrel;
+ Form_pg_constraint con;
+ SysScanDesc scan;
+ ScanKeyData key;
+ HeapTuple tuple;
+ bool found = false;
+ Oid conid;
+
+ conrel = heap_open(ConstraintRelationId, RowExclusiveLock);
+
+ /*
+ * Find and the target constraint
+ */
+ ScanKeyInit(&key,
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ scan = systable_beginscan(conrel, ConstraintRelidIndexId,
+ true, SnapshotNow, 1, &key);
+
+ while (HeapTupleIsValid(tuple = systable_getnext(scan)))
+ {
+ con = (Form_pg_constraint) GETSTRUCT(tuple);
+
+ if (strcmp(NameStr(con->conname), constrName) != 0)
+ continue;
+
+ conid = HeapTupleGetOid(tuple);
+ found = true;
+ break;
+ }
+
+ if (found && con->contype == CONSTR_FOREIGN && !con->convalidated)
+ {
+ Relation refrel;
+
+ /*
+ * Triggers are already in place on both tables, so a
+ * concurrent write that alters the result here is not
+ * possible.
+ */
+ refrel = heap_open(con->confrelid, AccessShareLock);
+
+ validateForeignKeyConstraint((char *)constrName, rel, refrel,
+ con->conindid,
+ conid,
+ true); /* must use query */
+
+ /*
+ * Now update the catalog, while we have the door open.
+ */
+ con->convalidated = BoolGetDatum(true);
+ simple_heap_update(conrel, &tuple->t_self, tuple);
+ CatalogUpdateIndexes(conrel, tuple);
+
+ heap_close(refrel, NoLock);
+ }
+
+ systable_endscan(scan);
+ heap_close(conrel, RowExclusiveLock);
+
+ if (!found)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("foreign key constraint \"%s\" of relation \"%s\" does not exist",
+ constrName, RelationGetRelationName(rel))));
+ }
+}
/*
* transformColumnNameList - transform list of column names
@@ -5779,11 +5868,12 @@ checkFkeyPermissions(Relation rel, int16 *attnums, int natts)
* Caller must have opened and locked both relations appropriately.
*/
static void
-validateForeignKeyConstraint(Constraint *fkconstraint,
+validateForeignKeyConstraint(char *conname,
Relation rel,
Relation pkrel,
Oid pkindOid,
- Oid constraintOid)
+ Oid constraintOid,
+ bool must_use_query)
{
HeapScanDesc scan;
HeapTuple tuple;
@@ -5794,7 +5884,7 @@ validateForeignKeyConstraint(Constraint *fkconstraint,
*/
MemSet(&trig, 0, sizeof(trig));
trig.tgoid = InvalidOid;
- trig.tgname = fkconstraint->conname;
+ trig.tgname = conname;
trig.tgenabled = TRIGGER_FIRES_ON_ORIGIN;
trig.tgisinternal = TRUE;
trig.tgconstrrelid = RelationGetRelid(pkrel);
@@ -5811,6 +5901,12 @@ validateForeignKeyConstraint(Constraint *fkconstraint,
if (RI_Initial_Check(&trig, rel, pkrel))
return;
+ if (must_use_query)
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("cannot SELECT from primary key of relation \"%s\"",
+ RelationGetRelationName(rel))));
+
/*
* Scan through each tuple, calling RI_FKey_check_ins (insert trigger) as
* if that tuple had just been inserted. If any of those fail, it should
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 4802138..8d996a8 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -422,6 +422,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
CONSTRAINT_TRIGGER,
stmt->deferrable,
stmt->initdeferred,
+ true,
RelationGetRelid(rel),
NULL, /* no conkey */
0,
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 113bede..840cb81 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -2377,6 +2377,7 @@ domainAddConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid,
CONSTRAINT_CHECK, /* Constraint Type */
false, /* Is Deferrable */
false, /* Is Deferred */
+ true, /* Is Validated */
InvalidOid, /* not a relation constraint */
NULL,
0,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 660947c..2881211 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -545,7 +545,7 @@ static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
UNBOUNDED UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNLOGGED
UNTIL UPDATE USER USING
- VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
+ VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
VERBOSE VERSION_P VIEW VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRAPPER WRITE
@@ -1751,6 +1751,14 @@ alter_table_cmd:
n->def = $2;
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> VALIDATE CONSTRAINT ... */
+ | VALIDATE CONSTRAINT name
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_ValidateConstraint;
+ n->name = $3;
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> DROP CONSTRAINT IF EXISTS <name> [RESTRICT|CASCADE] */
| DROP CONSTRAINT IF_P EXISTS name opt_drop_behavior
{
@@ -2711,9 +2719,25 @@ ConstraintElem:
n->fk_matchtype = $9;
n->fk_upd_action = (char) ($10 >> 8);
n->fk_del_action = (char) ($10 & 0xFF);
- n->skip_validation = FALSE;
n->deferrable = ($11 & 1) != 0;
n->initdeferred = ($11 & 2) != 0;
+ n->skip_validation = false;
+ $$ = (Node *)n;
+ }
+ | FOREIGN KEY '(' columnList ')' REFERENCES qualified_name
+ opt_column_list key_match key_actions
+ NOT VALID
+ {
+ Constraint *n = makeNode(Constraint);
+ n->contype = CONSTR_FOREIGN;
+ n->location = @1;
+ n->pktable = $7;
+ n->fk_attrs = $4;
+ n->pk_attrs = $8;
+ n->fk_matchtype = $9;
+ n->fk_upd_action = (char) ($10 >> 8);
+ n->fk_del_action = (char) ($10 & 0xFF);
+ n->skip_validation = true;
$$ = (Node *)n;
}
;
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index 26dbcac..a7967ab 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -45,6 +45,7 @@ CATALOG(pg_constraint,2606)
char contype; /* constraint type; see codes below */
bool condeferrable; /* deferrable constraint? */
bool condeferred; /* deferred by default? */
+ bool convalidated; /* constraint has been validated? */
/*
* conrelid and conkey are only meaningful if the constraint applies to a
@@ -148,29 +149,30 @@ typedef FormData_pg_constraint *Form_pg_constraint;
* compiler constants for pg_constraint
* ----------------
*/
-#define Natts_pg_constraint 22
+#define Natts_pg_constraint 23
#define Anum_pg_constraint_conname 1
#define Anum_pg_constraint_connamespace 2
#define Anum_pg_constraint_contype 3
#define Anum_pg_constraint_condeferrable 4
#define Anum_pg_constraint_condeferred 5
-#define Anum_pg_constraint_conrelid 6
-#define Anum_pg_constraint_contypid 7
-#define Anum_pg_constraint_conindid 8
-#define Anum_pg_constraint_confrelid 9
-#define Anum_pg_constraint_confupdtype 10
-#define Anum_pg_constraint_confdeltype 11
-#define Anum_pg_constraint_confmatchtype 12
-#define Anum_pg_constraint_conislocal 13
-#define Anum_pg_constraint_coninhcount 14
-#define Anum_pg_constraint_conkey 15
-#define Anum_pg_constraint_confkey 16
-#define Anum_pg_constraint_conpfeqop 17
-#define Anum_pg_constraint_conppeqop 18
-#define Anum_pg_constraint_conffeqop 19
-#define Anum_pg_constraint_conexclop 20
-#define Anum_pg_constraint_conbin 21
-#define Anum_pg_constraint_consrc 22
+#define Anum_pg_constraint_convalidated 6
+#define Anum_pg_constraint_conrelid 7
+#define Anum_pg_constraint_contypid 8
+#define Anum_pg_constraint_conindid 9
+#define Anum_pg_constraint_confrelid 10
+#define Anum_pg_constraint_confupdtype 11
+#define Anum_pg_constraint_confdeltype 12
+#define Anum_pg_constraint_confmatchtype 13
+#define Anum_pg_constraint_conislocal 14
+#define Anum_pg_constraint_coninhcount 15
+#define Anum_pg_constraint_conkey 16
+#define Anum_pg_constraint_confkey 17
+#define Anum_pg_constraint_conpfeqop 18
+#define Anum_pg_constraint_conppeqop 19
+#define Anum_pg_constraint_conffeqop 20
+#define Anum_pg_constraint_conexclop 21
+#define Anum_pg_constraint_conbin 22
+#define Anum_pg_constraint_consrc 23
/* Valid values for contype */
@@ -205,6 +207,7 @@ extern Oid CreateConstraintEntry(const char *constraintName,
char constraintType,
bool isDeferrable,
bool isDeferred,
+ bool isValidated,
Oid relId,
const int16 *constraintKey,
int constraintNKeys,
@@ -228,6 +231,7 @@ extern Oid CreateConstraintEntry(const char *constraintName,
extern void RemoveConstraintById(Oid conId);
extern void RenameConstraintById(Oid conId, const char *newname);
+extern void SetValidatedConstraintById(Oid conId);
extern bool ConstraintNameIsUsed(ConstraintCategory conCat, Oid objId,
Oid objNamespace, const char *conname);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 3d2ae99..5714a54 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1140,6 +1140,7 @@ typedef enum AlterTableType
AT_ReAddIndex, /* internal to commands/tablecmds.c */
AT_AddConstraint, /* add constraint */
AT_AddConstraintRecurse, /* internal to commands/tablecmds.c */
+ AT_ValidateConstraint, /* validate constraint */
AT_ProcessedConstraint, /* pre-processed add constraint (local in
* parser/parse_utilcmd.c) */
AT_DropConstraint, /* drop constraint */
@@ -1181,6 +1182,7 @@ typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */
Node *transform; /* transformation expr for ALTER TYPE */
DropBehavior behavior; /* RESTRICT or CASCADE for DROP cases */
bool missing_ok; /* skip error if missing? */
+ bool validated;
} AlterTableCmd;
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 578d3cd..7dad001 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -397,6 +397,7 @@ PG_KEYWORD("user", USER, RESERVED_KEYWORD)
PG_KEYWORD("using", USING, RESERVED_KEYWORD)
PG_KEYWORD("vacuum", VACUUM, UNRESERVED_KEYWORD)
PG_KEYWORD("valid", VALID, UNRESERVED_KEYWORD)
+PG_KEYWORD("validate", VALIDATE, UNRESERVED_KEYWORD)
PG_KEYWORD("validator", VALIDATOR, UNRESERVED_KEYWORD)
PG_KEYWORD("value", VALUE_P, UNRESERVED_KEYWORD)
PG_KEYWORD("values", VALUES, COL_NAME_KEYWORD)
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 3d126bb..db8acff 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -184,6 +184,10 @@ DETAIL: Key (a)=(5) is not present in table "tmp2".
DELETE FROM tmp3 where a=5;
-- Try (and succeed)
ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
+ALTER TABLE tmp3 drop constraint tmpconstr;
+-- Try NOT VALID and then VALIDATE CONSTRAINT
+ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full NOT VALID;
+ALTER TABLE tmp3 validate constraint tmpconstr;
-- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
-- tmp4 is a,b
ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 4895768..6aaee1a 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -221,6 +221,11 @@ DELETE FROM tmp3 where a=5;
-- Try (and succeed)
ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
+ALTER TABLE tmp3 drop constraint tmpconstr;
+
+-- Try NOT VALID and then VALIDATE CONSTRAINT
+ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full NOT VALID;
+ALTER TABLE tmp3 validate constraint tmpconstr;
-- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
-- tmp4 is a,b
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
