The new SQL Standard (SQL:2011) contains this:
"Table constraints are either enforced or not enforced. Domain
constraints and assertions are always enforced.", 4.17.2
The SQL Standard allows you to turn the checking on and off for CHECK
constraints, UNIQUE constraints and FOREIGN KEYS.
Which of those make sense for us, if any? The ability to create FKs
without checking all the data has been frequently requested to me over
many years. OTOH, I can't really see any point in turning on/off all of
the other aspects mentioned by the SQL Standard, especially indexes.
It's lots of work and seems likely to end with poorer data quality. And
the obvious thing is if you don't want a CHECK constraint, just drop
it...
My proposal is that we add a short and simple clause NOT ENFORCED onto
the ADD constraint syntax. So we have
ALTER TABLE foo
ADD FOREIGN KEY .... NOT ENFORCED;
The "enforced" state is not persisted - once added the FK is checked
every time. So there is no additional column on pg_constraint.
The benefit here is that we implement a capability that allows skipping
very long running SQL statements when required, and doesn't require too
much code. It has been discussed before on hackers, but that was before
it was part of the SQL Standard. Oracle has had this for years and it is
popular feature. We can expect other RDBMS to implement this feature,
now it is part of the standard.
If you want more than my good-bits-only proposal, it really isn't going
to happen for 9.1, and seems pretty pointless anyway.
Very short hack to implement this attached for discussion. No tests, not
even a compile - just showing how quick a patch this can be.
Thoughts? Alternative syntax?
--
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 784feae..ecadcbd 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -42,7 +42,7 @@ 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> [ ENFORCED | NOT ENFORCED ]
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 +220,13 @@ 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> [ ENFORCED | NOT ENFORCED ]</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 constraints can be defined
+ as <literal>NOT ENFORCED</literal>, rather than the default setting
+ <literal>ENFORCED</literal>.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 937992b..3cacad0 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -304,13 +304,13 @@ static void ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
IndexStmt *stmt, bool is_rebuild, LOCKMODE lockmode);
static void ATExecAddConstraint(List **wqueue,
AlteredTableInfo *tab, Relation rel,
- Constraint *newConstraint, bool recurse, LOCKMODE lockmode);
+ Constraint *newConstraint, bool recurse, LOCKMODE lockmode, bool enforced);
static void ATAddCheckConstraint(List **wqueue,
AlteredTableInfo *tab, Relation rel,
Constraint *constr,
bool recurse, bool recursing, LOCKMODE lockmode);
static void ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
- Constraint *fkconstraint, LOCKMODE lockmode);
+ Constraint *fkconstraint, LOCKMODE lockmode, bool enforced);
static void ATExecDropConstraint(Relation rel, const char *constrName,
DropBehavior behavior,
bool recurse, bool recursing,
@@ -2970,11 +2970,11 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
break;
case AT_AddConstraint: /* ADD CONSTRAINT */
ATExecAddConstraint(wqueue, tab, rel, (Constraint *) cmd->def,
- false, lockmode);
+ false, lockmode, cmd->enforced);
break;
case AT_AddConstraintRecurse: /* ADD CONSTRAINT with recursion */
ATExecAddConstraint(wqueue, tab, rel, (Constraint *) cmd->def,
- true, lockmode);
+ true, lockmode, cmd->enforced);
break;
case AT_DropConstraint: /* DROP CONSTRAINT */
ATExecDropConstraint(rel, cmd->name, cmd->behavior,
@@ -4914,7 +4914,7 @@ ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
*/
static void
ATExecAddConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
- Constraint *newConstraint, bool recurse, LOCKMODE lockmode)
+ Constraint *newConstraint, bool recurse, LOCKMODE lockmode, bool enforced)
{
Assert(IsA(newConstraint, Constraint));
@@ -4958,7 +4958,7 @@ ATExecAddConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
RelationGetNamespace(rel),
NIL);
- ATAddForeignKeyConstraint(tab, rel, newConstraint, lockmode);
+ ATAddForeignKeyConstraint(tab, rel, newConstraint, lockmode, enforced);
break;
default:
@@ -5088,7 +5088,7 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
*/
static void
ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
- Constraint *fkconstraint, LOCKMODE lockmode)
+ Constraint *fkconstraint, LOCKMODE lockmode, bool enforced)
{
Relation pkrel;
int16 pkattnum[INDEX_MAX_KEYS];
@@ -5344,9 +5344,10 @@ 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). We also skip the check if
+ * we are adding a foreign key and specifying NOT ENFORCED.
*/
- if (!fkconstraint->skip_validation)
+ if (!fkconstraint->skip_validation && enforced)
{
NewConstraint *newcon;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9ec75f7..62222fb 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -1727,11 +1727,12 @@ alter_table_cmd:
$$ = (Node *)n;
}
/* ALTER TABLE <name> ADD CONSTRAINT ... */
- | ADD_P TableConstraint
+ | ADD_P TableConstraint TableConstraintEnforcement
{
AlterTableCmd *n = makeNode(AlterTableCmd);
n->subtype = AT_AddConstraint;
n->def = $2;
+ n->enforced = $3;
$$ = (Node *)n;
}
/* ALTER TABLE <name> DROP CONSTRAINT IF EXISTS <name> [RESTRICT|CASCADE] */
@@ -1942,6 +1943,10 @@ alter_using:
| /* EMPTY */ { $$ = NULL; }
;
+TableConstraintEnforcement:
+ ENFORCED { $$ = TRUE; }
+ | NOT ENFORCED { $$ = FALSE; }
+
reloptions:
'(' reloption_list ')' { $$ = $2; }
;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 8b34b76..b3916b2 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1179,6 +1179,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 enforced; /* Whether or not we perform check */
} AlterTableCmd;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
