ENABLE/DISABLE CONSTRAINT NAME
Hi hackers,
In order to achieve enable/disable constraint name，I made a few
modifications to the code.
First, someone used to build the constraints while building
table. Then inserting data must follow a certain order.
And people usually like to insert the data but not affected by
foreign keys or check.
Second, the check or the foreign key constraint will waste much
time while inserting the data into the table.
Due to the above reasons，I realized this command.
I add a field named 'conenabled' to pg_constraint, identifying
whether a constraint is enable or not;
I enable or disable a foreign key constraint, by enable or
disable the triggers of the foreign key;
Our database will depend on the value of 'conenabled' to use the
check constrint or not;
I think the internal trigger's naming can be changed,and the
function ATExecValidateConstraint can be changed too,
but I think that together we can discuss, to decide what to do.
Now,we can do those:
Syntax:
alter table disable constraint <constraint_name> ;
alter table enable constraint <constraint_name> [ no valid ];
alter table add constraint table_constriant [ disable ];
CREATE TABLE aa
(
a1 INT CHECK(a1>4),
a2 INT
);
ALTER TABLE aa ADD CONSTRAINT aa_a2_check CHECK(a2>10) DISABLE;
INSERT INTO aa VALUES (10,1);
DELETE FROM aa;
ALTER TABLE aa DROP CONSTRAINT aa_a2_check;
ALTER TABLE aa DISABLE CONSTRAINT aa_a1_check;
INSERT INTO aa VALUES (5,2);
UPDATE aa SET a1=2 WHERE a2=2;
INSERT INTO aa VALUES (1,1);
ALTER TABLE aa ENABLE CONSTRAINT aa_a1_check NOT VALID; //don't
validate the data
DELETE FROM aa;
ALTER TABLE aa DISABLE CONSTRAINT aa_a1_check;
ALTER TABLE aa ENABLE CONSTRAINT aa_a1_check; //validate the data
CREATE TABLE bb
(
b1 INT PRIMARY KEY,
b2 INT
);
CREATE TABLE cc
(
c1 INT REFERENCES bb(b1),
c2 INT
);
ALTER TABLE cc DISABLE CONSTRAINT cc_c1_fkey;
INSERT INTO cc VALUES (1,1);
INSERT INTO bb VALUES (2,2);
INSERT INTO cc VALUES (2,2);
UPDATE cc SET c1=1 WHERE c2=2;
ALTER TABLE cc ENABLE CONSTRAINT cc_c1_fkey NOT VALID; //don't
validate the data
ALTER TABLE cc DISABLE CONSTRAINT cc_c1_fkey;
DELETE FROM cc;
ALTER TABLE cc ENABLE CONSTRAINT cc_c1_fkey; //validate the data
I packed a patch about this modification.This is my first time to send
the patch,
I hope you give me some advice.
Best Regards!
Yours,
Wang Shuo
HighGo Software Co.,Ltd.
August 26, 2013
Attachments:
constraint_enabledordisable.patchtext/x-diff; name=constraint_enabledordisable.patchDownload
diff -uNr b/doc/src/sgml/ref/alter_table.sgml a/doc/src/sgml/ref/alter_table.sgml
--- b/doc/src/sgml/ref/alter_table.sgml 2013-08-27 13:39:47.686476913 +0800
+++ a/doc/src/sgml/ref/alter_table.sgml 2013-08-29 14:54:05.792017381 +0800
@@ -44,9 +44,11 @@
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET ( <replaceable class="PARAMETER">attribute_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> RESET ( <replaceable class="PARAMETER">attribute_option</replaceable> [, ... ] )
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
- ADD <replaceable class="PARAMETER">table_constraint</replaceable> [ NOT VALID ]
+ ADD <replaceable class="PARAMETER">table_constraint</replaceable> [ NOT VALID | DISABLE ]
ADD <replaceable class="PARAMETER">table_constraint_using_index</replaceable>
ALTER CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ DISABLE CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable>
+ ENABLE CONSTRAINT <replaceable class="PARAMETER">constraint_name</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 ]
@@ -245,12 +247,14 @@
</varlistentry>
<varlistentry>
- <term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable> [ NOT VALID ]</literal></term>
+ <term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable> [ NOT VALID | DISABLE]</literal></term>
<listitem>
<para>
This form adds a new constraint to a table using the same syntax as
<xref linkend="SQL-CREATETABLE">, plus the option <literal>NOT
VALID</literal>, which is currently only allowed for foreign key
+ and CHECK constraints.Plusing the option <literal>NOT
+ VALID</literal>, which is currently only allowed for foreign key
and CHECK constraints.
If the constraint is marked <literal>NOT VALID</literal>, the
potentially-lengthy initial check to verify that all rows in the table
@@ -262,6 +266,13 @@
database will not assume that the constraint holds for all rows in
the table, until it is validated by using the <literal>VALIDATE
CONSTRAINT</literal> option.
+ Plusing the option <literal>DISABLE</literal>,
+ which is currently only allowed for foreign key and CHECK constraints.
+ If the constraint is marked <literal>DISABLE</literal>, the
+ potentially-lengthy initial check to verify that all rows in the table
+ satisfy the constraint is skipped and the constraint is token off,
+ until it is validated by using the <literal>ENABLE CONSTRAINT</literal>
+ option.
</para>
</listitem>
</varlistentry>
@@ -328,6 +339,25 @@
</varlistentry>
<varlistentry>
+ <term><literal>DISABLE</literal>/<literal>ENABLE CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> [NOT VALID]</literal></term>
+ <listitem>
+ <para>
+ This form enables or disables a foreign key or check constraint.
+ Enable the constraint by scanning the table to ensure there
+ are no rows for which the constraint is not satisfied. It won't
+ scanning the table with <literal>NOT VALID</literal>.
+ </para>
+ <para>
+ Validation can be a long process on larger tables and currently requires
+ an <literal>ACCESS EXCLUSIVE</literal> lock. Enabling with
+ <literal>NOT VALID</literal> that you can defer validation to less busy times.
+ Enabling can be used to give additional time to correct pre-existing
+ errors while preventing new errors.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>VALIDATE CONSTRAINT</literal></term>
<listitem>
<para>
@@ -335,6 +365,7 @@
as <literal>NOT VALID</literal>, by scanning the table to ensure there
are no rows for which the constraint is not satisfied.
Nothing happens if the constraint is already marked valid.
+ The constraint must be enabled.
</para>
<para>
Validation can be a long process on larger tables and currently requires
diff -uNr b/src/backend/catalog/heap.c a/src/backend/catalog/heap.c
--- b/src/backend/catalog/heap.c 2013-08-27 13:39:47.774479421 +0800
+++ a/src/backend/catalog/heap.c 2013-08-29 15:45:51.821587805 +0800
@@ -96,7 +96,7 @@
static void RelationRemoveInheritance(Oid relid);
static void StoreRelCheck(Relation rel, char *ccname, Node *expr,
bool is_validated, bool is_local, int inhcount,
- bool is_no_inherit, bool is_internal);
+ bool is_no_inherit, bool is_internal, bool isenabled);
static void StoreConstraints(Relation rel, List *cooked_constraints,
bool is_internal);
static bool MergeWithExistingConstraint(Relation rel, char *ccname, Node *expr,
@@ -1946,7 +1946,7 @@
static void
StoreRelCheck(Relation rel, char *ccname, Node *expr,
bool is_validated, bool is_local, int inhcount,
- bool is_no_inherit, bool is_internal)
+ bool is_no_inherit, bool is_internal, bool isenabled)
{
char *ccbin;
char *ccsrc;
@@ -2010,6 +2010,7 @@
false, /* Is Deferrable */
false, /* Is Deferred */
is_validated,
+ isenabled,
RelationGetRelid(rel), /* relation */
attNos, /* attrs in the constraint */
keycount, /* # attrs in the constraint */
@@ -2073,7 +2074,7 @@
case CONSTR_CHECK:
StoreRelCheck(rel, con->name, con->expr, !con->skip_validation,
con->is_local, con->inhcount,
- con->is_no_inherit, is_internal);
+ con->is_no_inherit, is_internal, true);
numchecks++;
break;
default:
@@ -2307,7 +2308,7 @@
* OK, store it.
*/
StoreRelCheck(rel, ccname, expr, !cdef->skip_validation, is_local,
- is_local ? 0 : 1, cdef->is_no_inherit, is_internal);
+ is_local ? 0 : 1, cdef->is_no_inherit, is_internal, !cdef->isdisabled);
numchecks++;
diff -uNr b/src/backend/catalog/index.c a/src/backend/catalog/index.c
--- b/src/backend/catalog/index.c 2013-08-27 13:39:47.775479449 +0800
+++ a/src/backend/catalog/index.c 2013-08-29 15:53:03.899662428 +0800
@@ -1167,6 +1167,7 @@
deferrable,
initdeferred,
true,
+ true,
RelationGetRelid(heapRelation),
indexInfo->ii_KeyAttrNumbers,
indexInfo->ii_NumIndexAttrs,
@@ -1237,7 +1238,7 @@
trigger->initdeferred = initdeferred;
trigger->constrrel = NULL;
- (void) CreateTrigger(trigger, NULL, conOid, indexRelationId, true);
+ (void) CreateTrigger(trigger, NULL, conOid, indexRelationId, true, true);
}
/*
diff -uNr b/src/backend/catalog/pg_constraint.c a/src/backend/catalog/pg_constraint.c
--- b/src/backend/catalog/pg_constraint.c 2013-08-27 13:39:47.779479563 +0800
+++ a/src/backend/catalog/pg_constraint.c 2013-08-29 15:00:15.148514898 +0800
@@ -48,6 +48,7 @@
bool isDeferrable,
bool isDeferred,
bool isValidated,
+ bool isenabled,
Oid relId,
const int16 *constraintKey,
int constraintNKeys,
@@ -163,6 +164,11 @@
values[Anum_pg_constraint_condeferrable - 1] = BoolGetDatum(isDeferrable);
values[Anum_pg_constraint_condeferred - 1] = BoolGetDatum(isDeferred);
values[Anum_pg_constraint_convalidated - 1] = BoolGetDatum(isValidated);
+ if(isenabled)
+ values[Anum_pg_constraint_convalidated - 1] = BoolGetDatum(isValidated);
+ else
+ values[Anum_pg_constraint_convalidated - 1] = BoolGetDatum(isenabled);
+ values[Anum_pg_constraint_conenabled - 1] = BoolGetDatum(isenabled);
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 -uNr b/src/backend/commands/tablecmds.c a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c 2013-08-27 13:39:47.803480247 +0800
+++ a/src/backend/commands/tablecmds.c 2013-08-29 17:29:07.125765203 +0800
@@ -280,6 +280,9 @@
bool recurse, bool recursing, LOCKMODE lockmode);
static void ATExecValidateConstraint(Relation rel, char *constrName,
bool recurse, bool recursing, LOCKMODE lockmode);
+static void ATExecEnableDisableConstraint(Relation rel, char *constrName,bool recurse,
+ bool recursing, bool enable_or_disable,
+ bool validated,LOCKMODE lockmode);
static int transformColumnNameList(Oid relId, List *colList,
int16 *attnums, Oid *atttypids);
static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
@@ -2890,6 +2893,15 @@
case AT_ResetOptions:
case AT_SetStorage:
case AT_AlterConstraint:
+ case AT_EnableConstraint:
+ cmd_lockmode = ShareUpdateExclusiveLock;
+ break;
+ case AT_EnablenotvalidConstraint:
+ cmd_lockmode = ShareUpdateExclusiveLock;
+ break;
+ case AT_DisableConstraint:
+ cmd_lockmode = ShareUpdateExclusiveLock;
+ break;
case AT_ValidateConstraint:
cmd_lockmode = ShareUpdateExclusiveLock;
break;
@@ -3132,6 +3144,30 @@
ATSimplePermissions(rel, ATT_TABLE);
pass = AT_PASS_MISC;
break;
+ case AT_EnableConstraint: /* ENABLE CONSTRAINT */
+ ATSimplePermissions(rel, ATT_TABLE);
+ /* Recursion occurs during execution phase */
+ /* No command-specific prep needed except saving recurse flag */
+ if (recurse)
+ cmd->subtype = AT_EnableConstraintRecurse;
+ pass = AT_PASS_MISC;
+ break;
+ case AT_EnablenotvalidConstraint: /* ENABLE CONSTRAINT NOT VALID*/
+ ATSimplePermissions(rel, ATT_TABLE);
+ /* Recursion occurs during execution phase */
+ /* No command-specific prep needed except saving recurse flag */
+ if (recurse)
+ cmd->subtype = AT_EnablenotvalidConstraintRecurse;
+ pass = AT_PASS_MISC;
+ break;
+ case AT_DisableConstraint: /* DISABLE CONSTRAINT */
+ ATSimplePermissions(rel, ATT_TABLE);
+ /* Recursion occurs during execution phase */
+ /* No command-specific prep needed except saving recurse flag */
+ if (recurse)
+ cmd->subtype = AT_DisableConstraintRecurse;
+ pass = AT_PASS_MISC;
+ break;
case AT_ValidateConstraint: /* VALIDATE CONSTRAINT */
ATSimplePermissions(rel, ATT_TABLE);
/* Recursion occurs during execution phase */
@@ -3314,6 +3350,27 @@
case AT_AlterConstraint: /* ALTER CONSTRAINT */
ATExecAlterConstraint(rel, cmd, false, false, lockmode);
break;
+ case AT_EnableConstraint: /* ENABLE CONSTRAINT */
+ ATExecEnableDisableConstraint(rel, cmd->name, false, false, true, true, lockmode);
+ break;
+ case AT_EnablenotvalidConstraint: /* ENABLE CONSTRAINT NOT VAILD*/
+ ATExecEnableDisableConstraint(rel, cmd->name, false, false, true, false, lockmode);
+ break;
+ case AT_EnableConstraintRecurse: /* ENABLE CONSTRAINT with
+ * recursion */
+ ATExecEnableDisableConstraint(rel, cmd->name, true, false, true, true, lockmode);
+ break;
+ case AT_EnablenotvalidConstraintRecurse: /* ENABLE CONSTRAINT with
+ * recursion NOT VALID*/
+ ATExecEnableDisableConstraint(rel, cmd->name, true, false, true, false, lockmode);
+ break;
+ case AT_DisableConstraint: /* VALIDATE CONSTRAINT */
+ ATExecEnableDisableConstraint(rel, cmd->name, false, false, false, false, lockmode);
+ break;
+ case AT_DisableConstraintRecurse: /* VALIDATE CONSTRAINT with
+ * recursion */
+ ATExecEnableDisableConstraint(rel, cmd->name, true, false, false, false, lockmode);
+ break;
case AT_ValidateConstraint: /* VALIDATE CONSTRAINT */
ATExecValidateConstraint(rel, cmd->name, false, false, lockmode);
break;
@@ -6132,6 +6189,7 @@
fkconstraint->deferrable,
fkconstraint->initdeferred,
fkconstraint->initially_valid,
+ !fkconstraint->isdisabled,
RelationGetRelid(rel),
fkattnum,
numfks,
@@ -6377,6 +6435,11 @@
HeapTuple copyTuple;
Form_pg_constraint copy_con;
+ if (!con->conenabled)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("constraint must be enabled")));
+
if (con->contype == CONSTRAINT_FOREIGN)
{
Oid conid = HeapTupleGetOid(tuple);
@@ -6479,6 +6542,160 @@
/*
+ *
+ * Called by ALTER TABLE ENABLE/DISABLE CONSTRAINT to change 'conenabled'
+ * field for pg_constraint taking off the constraint.Only the Check and
+ * Foreign Key can be token off.
+ * Foreign Key : to change 'conenabled' field for pg_constraint and
+ * take off the triggers.
+ * Check : to change 'conenabled' field for pg_constraint.
+ *
+ */
+static void
+ATExecEnableDisableConstraint(Relation rel, char *constrName, bool recurse,
+ bool recursing, bool enable_or_disable,
+ bool validated, LOCKMODE lockmode)
+{
+ Relation conrel;
+ SysScanDesc scan;
+ ScanKeyData key;
+ HeapTuple tuple;
+ Form_pg_constraint con = NULL;
+ bool found = false;
+ HeapTuple copyTuple;
+ Form_pg_constraint copy_con;
+
+ conrel = heap_open(ConstraintRelationId, RowExclusiveLock);
+
+ /*
+ * Find and check the target constraint
+ */
+ ScanKeyInit(&key,
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ scan = systable_beginscan(conrel, ConstraintRelidIndexId,
+ true, NULL, 1, &key);
+
+
+ while (HeapTupleIsValid(tuple = systable_getnext(scan)))
+ {
+ con = (Form_pg_constraint) GETSTRUCT(tuple);
+ if (strcmp(NameStr(con->conname), constrName) == 0)
+ {
+ found = true;
+ break;
+ }
+ }
+
+ if (!found)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("constraint \"%s\" of relation \"%s\" does not exist",
+ constrName, RelationGetRelationName(rel))));
+
+ if (con->contype != CONSTRAINT_FOREIGN &&
+ con->contype != CONSTRAINT_CHECK)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key or check constraint",
+ constrName, RelationGetRelationName(rel))));
+
+ if (con->contype == CONSTRAINT_FOREIGN)
+ {
+ Oid conid = HeapTupleGetOid(tuple);
+ Relation refrel;
+
+ refrel = heap_open(con->confrelid, RowShareLock);
+
+ if(validated)
+ validateForeignKeyConstraint(constrName, rel, refrel,
+ con->conindid,
+ conid);
+ if(enable_or_disable)
+ EnableDisableFkey(rel, TRIGGER_FIRES_ON_ORIGIN, false, constrName);
+ else
+ EnableDisableFkey(rel, TRIGGER_DISABLED, false, constrName);
+
+ heap_close(refrel, NoLock);
+
+ }
+ else if (con->contype == CONSTRAINT_CHECK)
+ {
+ List *children = NIL;
+ ListCell *child;
+
+ /*
+ * If we're recursing, the parent has already done this, so skip
+ * it.
+ */
+ rel->rd_att->constr->check->ccenabled = enable_or_disable;
+
+ if (!recursing)
+ children = find_all_inheritors(RelationGetRelid(rel),
+ lockmode, NULL);
+
+ foreach(child, children)
+ {
+ Oid childoid = lfirst_oid(child);
+ Relation childrel;
+
+ if (childoid == RelationGetRelid(rel))
+ continue;
+
+ /*
+ * If we are told not to recurse, there had better not be any
+ * child tables; else the addition would put them out of step.
+ */
+ if (!recurse)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("constraint must be validated on child tables too")));
+
+ /* find_all_inheritors already got lock */
+ childrel = heap_open(childoid, NoLock);
+
+ ATExecEnableDisableConstraint(childrel, constrName, false,
+ true, enable_or_disable,
+ validated, lockmode);
+ heap_close(childrel, NoLock);
+ }
+ if(validated)
+ validateCheckConstraint(rel, tuple);
+
+ /*
+ * Invalidate relcache so that others see the new validated
+ * constraint.
+ */
+ CacheInvalidateRelcache(rel);
+ }
+
+ /*
+ * Now update the catalog, while we have the door open.
+ */
+ copyTuple = heap_copytuple(tuple);
+ copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
+ copy_con->conenabled = enable_or_disable;
+ if(!enable_or_disable)
+ copy_con->convalidated = enable_or_disable;
+ else
+ if(validated && enable_or_disable)
+ copy_con->convalidated = enable_or_disable;
+ simple_heap_update(conrel, ©Tuple->t_self, copyTuple);
+ CatalogUpdateIndexes(conrel, copyTuple);
+
+ InvokeObjectPostAlterHook(ConstraintRelationId,
+ HeapTupleGetOid(tuple), 0);
+
+ heap_freetuple(copyTuple);
+
+ systable_endscan(scan);
+
+ heap_close(conrel, RowExclusiveLock);
+}
+
+
+/*
* transformColumnNameList - transform list of column names
*
* Lookup each name and return its attnum and type OID
@@ -7009,7 +7226,7 @@
fk_trigger->constrrel = fkconstraint->pktable;
fk_trigger->args = NIL;
- (void) CreateTrigger(fk_trigger, NULL, constraintOid, indexOid, true);
+ (void) CreateTrigger(fk_trigger, NULL, constraintOid, indexOid, true, !fkconstraint->isdisabled);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -7083,7 +7300,7 @@
}
fk_trigger->args = NIL;
- (void) CreateTrigger(fk_trigger, NULL, constraintOid, indexOid, true);
+ (void) CreateTrigger(fk_trigger, NULL, constraintOid, indexOid, true, true);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -7136,7 +7353,7 @@
}
fk_trigger->args = NIL;
- (void) CreateTrigger(fk_trigger, NULL, constraintOid, indexOid, true);
+ (void) CreateTrigger(fk_trigger, NULL, constraintOid, indexOid, true, true);
/* Make changes-so-far visible */
CommandCounterIncrement();
diff -uNr b/src/backend/commands/trigger.c a/src/backend/commands/trigger.c
--- b/src/backend/commands/trigger.c 2013-08-27 13:39:47.805480304 +0800
+++ a/src/backend/commands/trigger.c 2013-08-29 15:11:04.857963230 +0800
@@ -119,7 +119,7 @@
Oid
CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Oid constraintOid, Oid indexOid,
- bool isInternal)
+ bool isInternal, bool conisenabled)
{
int16 tgtype;
int ncolumns;
@@ -426,6 +426,7 @@
stmt->deferrable,
stmt->initdeferred,
true,
+ true,
RelationGetRelid(rel),
NULL, /* no conkey */
0,
@@ -516,7 +517,10 @@
CStringGetDatum(trigname));
values[Anum_pg_trigger_tgfoid - 1] = ObjectIdGetDatum(funcoid);
values[Anum_pg_trigger_tgtype - 1] = Int16GetDatum(tgtype);
- values[Anum_pg_trigger_tgenabled - 1] = CharGetDatum(TRIGGER_FIRES_ON_ORIGIN);
+ if(!conisenabled && isInternal)
+ values[Anum_pg_trigger_tgenabled - 1] = CharGetDatum(TRIGGER_DISABLED);
+ else
+ values[Anum_pg_trigger_tgenabled - 1] = CharGetDatum(TRIGGER_FIRES_ON_ORIGIN);
values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal);
values[Anum_pg_trigger_tgconstrrelid - 1] = ObjectIdGetDatum(constrrelid);
values[Anum_pg_trigger_tgconstrindid - 1] = ObjectIdGetDatum(indexOid);
@@ -1423,6 +1427,68 @@
}
+/*
+ * EnableDisableFkey()
+ *
+ * Called by ALTER TABLE ENABLE/DISABLE FOREIGN KEY
+ * to change 'tgenabled' field for triggers of the specified foreign key(s)
+ *
+ * rel & fires_when & skip_system: for the function EnableDisableTrigger()
+ * conname: the foreign key to process, or NULL to scan all foreign keys
+ */
+void
+EnableDisableFkey(Relation rel, char fires_when, bool skip_system, char * conname)
+{
+ Relation tgrel;
+ int nkeys;
+ ScanKeyData keys[2];
+ SysScanDesc tgscan;
+ HeapTuple tuple;
+ Oid conoid;
+
+ //Scan the relevant entries in pg_triggers
+ tgrel = heap_open(TriggerRelationId, RowExclusiveLock);
+
+ ScanKeyInit(&keys[0],
+ Anum_pg_trigger_tgrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+
+ nkeys = 1;
+
+ tgscan = systable_beginscan(tgrel, TriggerRelidNameIndexId, true,
+ NULL, nkeys, keys);
+
+ if(conname)
+ conoid = get_relation_constraint_oid(rel->rd_node.relNode, conname, false);
+
+ while (HeapTupleIsValid(tuple = systable_getnext(tgscan)))
+ {
+ Form_pg_trigger oldtrig = (Form_pg_trigger) GETSTRUCT(tuple);
+
+ //Get the the triggers names which belong to the foreign key
+ if(conname)
+ {
+ if(oldtrig->tgfoid == 1644 && oldtrig->tgconstraint == conoid)
+ EnableDisableTrigger(rel, oldtrig->tgname.data, fires_when, skip_system);
+ if(oldtrig->tgfoid == 1645 && oldtrig->tgconstraint == conoid)
+ EnableDisableTrigger(rel, oldtrig->tgname.data, fires_when, skip_system);
+ }
+ else
+ {
+ if(oldtrig->tgfoid == 1644)
+ EnableDisableTrigger(rel, oldtrig->tgname.data, fires_when, skip_system);
+ if(oldtrig->tgfoid == 1645)
+ EnableDisableTrigger(rel, oldtrig->tgname.data, fires_when, skip_system);
+ }
+ }
+
+ systable_endscan(tgscan);
+
+ heap_close(tgrel, RowExclusiveLock);
+}
+
+
/*
* Build trigger data to attach to the given relcache entry.
*
diff -uNr b/src/backend/commands/typecmds.c a/src/backend/commands/typecmds.c
--- b/src/backend/commands/typecmds.c 2013-08-27 13:39:47.806480332 +0800
+++ a/src/backend/commands/typecmds.c 2013-08-29 15:12:24.458213969 +0800
@@ -2998,6 +2998,7 @@
false, /* Is Deferrable */
false, /* Is Deferred */
!constr->skip_validation, /* Is Validated */
+ true,
InvalidOid, /* not a relation constraint */
NULL,
0,
diff -uNr b/src/backend/executor/execMain.c a/src/backend/executor/execMain.c
--- b/src/backend/executor/execMain.c 2013-08-27 13:39:47.811480475 +0800
+++ a/src/backend/executor/execMain.c 2013-08-29 15:13:43.562451494 +0800
@@ -1532,6 +1532,10 @@
MemoryContext oldContext;
List *qual;
int i;
+ bool enabled = check->ccenabled;
+
+ if(!enabled)
+ return NULL;
/*
* If first time through for this result relation, build expression
@@ -1565,6 +1569,7 @@
/* And evaluate the constraints */
for (i = 0; i < ncheck; i++)
{
+ bool execqual_enabled;
qual = resultRelInfo->ri_ConstraintExprs[i];
/*
@@ -1572,7 +1577,12 @@
* is not to be treated as a failure. Therefore, tell ExecQual to
* return TRUE for NULL.
*/
- if (!ExecQual(qual, econtext, true))
+ if(!check[i].ccenabled)
+ execqual_enabled = false;
+ else
+ execqual_enabled = !ExecQual(qual, econtext, true);
+
+ if (execqual_enabled)
return check[i].ccname;
}
diff -uNr b/src/backend/nodes/copyfuncs.c a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c 2013-08-27 13:39:47.828480960 +0800
+++ a/src/backend/nodes/copyfuncs.c 2013-08-29 15:15:34.462589639 +0800
@@ -2398,6 +2398,7 @@
COPY_NODE_FIELD(old_conpfeqop);
COPY_SCALAR_FIELD(skip_validation);
COPY_SCALAR_FIELD(initially_valid);
+ COPY_SCALAR_FIELD(isdisabled);
return newnode;
}
diff -uNr b/src/backend/nodes/equalfuncs.c a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c 2013-08-27 13:39:47.828480960 +0800
+++ a/src/backend/nodes/equalfuncs.c 2013-08-29 15:16:25.278028015 +0800
@@ -2200,6 +2200,7 @@
COMPARE_NODE_FIELD(old_conpfeqop);
COMPARE_SCALAR_FIELD(skip_validation);
COMPARE_SCALAR_FIELD(initially_valid);
+ COMPARE_SCALAR_FIELD(isdisabled);
return true;
}
diff -uNr b/src/backend/parser/gram.y a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y 2013-08-27 13:39:47.856481758 +0800
+++ a/src/backend/parser/gram.y 2013-08-29 15:57:16.250817111 +0800
@@ -118,6 +118,7 @@
#define CAS_INITIALLY_DEFERRED 0x08
#define CAS_NOT_VALID 0x10
#define CAS_NO_INHERIT 0x20
+#define CAS_DISABLE 0x40
#define parser_yyerror(msg) scanner_yyerror(msg, yyscanner)
@@ -161,7 +162,7 @@
core_yyscan_t yyscanner);
static void processCASbits(int cas_bits, int location, const char *constrType,
bool *deferrable, bool *initdeferred, bool *not_valid,
- bool *no_inherit, core_yyscan_t yyscanner);
+ bool *no_inherit, bool *isdisabled, core_yyscan_t yyscanner);
static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%}
@@ -1955,7 +1956,31 @@
processCASbits($4, @4, "ALTER CONSTRAINT statement",
&c->deferrable,
&c->initdeferred,
- NULL, NULL, yyscanner);
+ NULL, NULL, NULL, yyscanner);
+ $$ = (Node *)n;
+ }
+ /* ALTER TABLE <name> ENABLE CONSTRAINT ... */
+ | ENABLE_P CONSTRAINT name
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_EnableConstraint;
+ n->name = $3;
+ $$ = (Node *)n;
+ }
+ /* ALTER TABLE <name> ENABLE CONSTRAINT ... NOT VALID */
+ | ENABLE_P CONSTRAINT name NOT VALID
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_EnablenotvalidConstraint;
+ n->name = $3;
+ $$ = (Node *)n;
+ }
+ /* ALTER TABLE <name> DISABLE CONSTRAINT ... */
+ | DISABLE_P CONSTRAINT name
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_DisableConstraint;
+ n->name = $3;
$$ = (Node *)n;
}
/* ALTER TABLE <name> VALIDATE CONSTRAINT ... */
@@ -2068,7 +2093,7 @@
AlterTableCmd *n = makeNode(AlterTableCmd);
n->subtype = AT_DisableTrigAll;
$$ = (Node *)n;
- }
+ }
/* ALTER TABLE <name> DISABLE TRIGGER USER */
| DISABLE_P TRIGGER USER
{
@@ -2978,7 +3003,7 @@
n->cooked_expr = NULL;
processCASbits($5, @5, "CHECK",
NULL, NULL, &n->skip_validation,
- &n->is_no_inherit, yyscanner);
+ &n->is_no_inherit, &n->isdisabled, yyscanner);
n->initially_valid = !n->skip_validation;
$$ = (Node *)n;
}
@@ -2994,7 +3019,7 @@
n->indexspace = $6;
processCASbits($7, @7, "UNIQUE",
&n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| UNIQUE ExistingIndex ConstraintAttributeSpec
@@ -3008,7 +3033,7 @@
n->indexspace = NULL;
processCASbits($3, @3, "UNIQUE",
&n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY '(' columnList ')' opt_definition OptConsTableSpace
@@ -3023,7 +3048,7 @@
n->indexspace = $7;
processCASbits($8, @8, "PRIMARY KEY",
&n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY ExistingIndex ConstraintAttributeSpec
@@ -3037,7 +3062,7 @@
n->indexspace = NULL;
processCASbits($4, @4, "PRIMARY KEY",
&n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| EXCLUDE access_method_clause '(' ExclusionConstraintList ')'
@@ -3055,7 +3080,7 @@
n->where_clause = $8;
processCASbits($9, @9, "EXCLUDE",
&n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| FOREIGN KEY '(' columnList ')' REFERENCES qualified_name
@@ -3073,7 +3098,7 @@
processCASbits($11, @11, "FOREIGN KEY",
&n->deferrable, &n->initdeferred,
&n->skip_validation, NULL,
- yyscanner);
+ &n->isdisabled, yyscanner);
n->initially_valid = !n->skip_validation;
$$ = (Node *)n;
}
@@ -4317,7 +4342,7 @@
n->isconstraint = TRUE;
processCASbits($10, @10, "TRIGGER",
&n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ NULL, NULL, yyscanner);
n->constrrel = $9;
$$ = (Node *)n;
}
@@ -4455,6 +4480,7 @@
| INITIALLY DEFERRED { $$ = CAS_INITIALLY_DEFERRED; }
| NOT VALID { $$ = CAS_NOT_VALID; }
| NO INHERIT { $$ = CAS_NO_INHERIT; }
+ | DISABLE_P { $$ = CAS_DISABLE; }
;
@@ -4570,7 +4596,7 @@
n->isconstraint = TRUE;
processCASbits($8, @8, "ASSERTION",
&n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ NULL, NULL, yyscanner);
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -13491,7 +13517,7 @@
static void
processCASbits(int cas_bits, int location, const char *constrType,
bool *deferrable, bool *initdeferred, bool *not_valid,
- bool *no_inherit, core_yyscan_t yyscanner)
+ bool *no_inherit, bool *isdisabled, core_yyscan_t yyscanner)
{
/* defaults */
if (deferrable)
@@ -13500,6 +13526,8 @@
*initdeferred = false;
if (not_valid)
*not_valid = false;
+ if (isdisabled)
+ *isdisabled = false;
if (cas_bits & (CAS_DEFERRABLE | CAS_INITIALLY_DEFERRED))
{
@@ -13552,6 +13580,18 @@
constrType),
parser_errposition(location)));
}
+ if (cas_bits & CAS_DISABLE)
+ {
+ if (isdisabled)
+ *isdisabled = true;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ /* translator: %s is CHECK, UNIQUE, or similar */
+ errmsg("%s constraints cannot be marked DISABLE",
+ constrType),
+ parser_errposition(location)));
+ }
}
/*----------
diff -uNr b/src/backend/tcop/utility.c a/src/backend/tcop/utility.c
--- b/src/backend/tcop/utility.c 2013-08-27 13:39:47.977485206 +0800
+++ a/src/backend/tcop/utility.c 2013-08-29 15:24:46.690233221 +0800
@@ -1263,7 +1263,7 @@
case T_CreateTrigStmt:
(void) CreateTrigger((CreateTrigStmt *) parsetree, queryString,
- InvalidOid, InvalidOid, false);
+ InvalidOid, InvalidOid, false, true);
break;
case T_CreatePLangStmt:
diff -uNr b/src/backend/utils/adt/ruleutils.c a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c 2013-08-27 13:39:48.002485918 +0800
+++ a/src/backend/utils/adt/ruleutils.c 2013-08-29 16:39:57.185480657 +0800
@@ -1568,8 +1568,10 @@
appendStringInfo(&buf, " DEFERRABLE");
if (conForm->condeferred)
appendStringInfo(&buf, " INITIALLY DEFERRED");
- if (!conForm->convalidated)
+ if (!conForm->convalidated && conForm->conenabled)
appendStringInfoString(&buf, " NOT VALID");
+ if (!conForm->conenabled)
+ appendStringInfoString(&buf, " NOT VALID DISABLE");
/* Cleanup */
ReleaseSysCache(tup);
diff -uNr b/src/backend/utils/cache/relcache.c a/src/backend/utils/cache/relcache.c
--- b/src/backend/utils/cache/relcache.c 2013-08-27 13:39:48.014486260 +0800
+++ a/src/backend/utils/cache/relcache.c 2013-08-27 11:31:21.751406490 +0800
@@ -3401,6 +3401,7 @@
RelationGetRelationName(relation));
check[found].ccvalid = conform->convalidated;
+ check[found].ccenabled = conform->conenabled;
check[found].ccnoinherit = conform->connoinherit;
check[found].ccname = MemoryContextStrdup(CacheMemoryContext,
NameStr(conform->conname));
diff -uNr b/src/include/access/tupdesc.h a/src/include/access/tupdesc.h
--- b/src/include/access/tupdesc.h 2013-08-27 13:39:48.214491960 +0800
+++ a/src/include/access/tupdesc.h 2013-08-27 11:31:21.751406490 +0800
@@ -31,6 +31,7 @@
char *ccbin; /* nodeToString representation of expr */
bool ccvalid;
bool ccnoinherit; /* this is a non-inheritable constraint */
+ bool ccenabled; /* the constraint's status */
} ConstrCheck;
/* This structure contains constraints of a tuple */
diff -uNr b/src/include/catalog/pg_constraint.h a/src/include/catalog/pg_constraint.h
--- b/src/include/catalog/pg_constraint.h 2013-08-27 13:39:48.218492074 +0800
+++ a/src/include/catalog/pg_constraint.h 2013-08-29 15:28:49.486036144 +0800
@@ -47,6 +47,7 @@
bool condeferrable; /* deferrable constraint? */
bool condeferred; /* deferred by default? */
bool convalidated; /* constraint has been validated? */
+ bool conenabled; /* constraint is enable? */
/*
* conrelid and conkey are only meaningful if the constraint applies to a
@@ -152,31 +153,32 @@
* compiler constants for pg_constraint
* ----------------
*/
-#define Natts_pg_constraint 24
+#define Natts_pg_constraint 25
#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_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_connoinherit 16
-#define Anum_pg_constraint_conkey 17
-#define Anum_pg_constraint_confkey 18
-#define Anum_pg_constraint_conpfeqop 19
-#define Anum_pg_constraint_conppeqop 20
-#define Anum_pg_constraint_conffeqop 21
-#define Anum_pg_constraint_conexclop 22
-#define Anum_pg_constraint_conbin 23
-#define Anum_pg_constraint_consrc 24
+#define Anum_pg_constraint_conenabled 7
+#define Anum_pg_constraint_conrelid 8
+#define Anum_pg_constraint_contypid 9
+#define Anum_pg_constraint_conindid 10
+#define Anum_pg_constraint_confrelid 11
+#define Anum_pg_constraint_confupdtype 12
+#define Anum_pg_constraint_confdeltype 13
+#define Anum_pg_constraint_confmatchtype 14
+#define Anum_pg_constraint_conislocal 15
+#define Anum_pg_constraint_coninhcount 16
+#define Anum_pg_constraint_connoinherit 17
+#define Anum_pg_constraint_conkey 18
+#define Anum_pg_constraint_confkey 19
+#define Anum_pg_constraint_conpfeqop 20
+#define Anum_pg_constraint_conppeqop 21
+#define Anum_pg_constraint_conffeqop 22
+#define Anum_pg_constraint_conexclop 23
+#define Anum_pg_constraint_conbin 24
+#define Anum_pg_constraint_consrc 25
/* Valid values for contype */
@@ -212,6 +214,7 @@
bool isDeferrable,
bool isDeferred,
bool isValidated,
+ bool isenabled,
Oid relId,
const int16 *constraintKey,
int constraintNKeys,
diff -uNr b/src/include/commands/trigger.h a/src/include/commands/trigger.h
--- b/src/include/commands/trigger.h 2013-08-27 13:39:48.228492359 +0800
+++ a/src/include/commands/trigger.h 2013-08-29 15:30:20.256582137 +0800
@@ -110,7 +110,7 @@
extern Oid CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Oid constraintOid, Oid indexOid,
- bool isInternal);
+ bool isInternal, bool conisenabled);
extern void RemoveTriggerById(Oid trigOid);
extern Oid get_trigger_oid(Oid relid, const char *name, bool missing_ok);
@@ -120,6 +120,8 @@
extern void EnableDisableTrigger(Relation rel, const char *tgname,
char fires_when, bool skip_system);
+extern void EnableDisableFkey(Relation rel, char fires_when, bool skip_system, char * conname);
+
extern void RelationBuildTriggers(Relation relation);
extern TriggerDesc *CopyTriggerDesc(TriggerDesc *trigdesc);
diff -uNr b/src/include/nodes/parsenodes.h a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h 2013-08-27 13:39:48.236492587 +0800
+++ a/src/include/nodes/parsenodes.h 2013-08-29 15:32:37.289443004 +0800
@@ -1249,6 +1249,12 @@
AT_AddConstraintRecurse, /* internal to commands/tablecmds.c */
AT_ReAddConstraint, /* internal to commands/tablecmds.c */
AT_AlterConstraint, /* alter constraint */
+ AT_EnableConstraint, /* enable constraint */
+ AT_EnableConstraintRecurse, /* internal to commands/tablecmds.c */
+ AT_EnablenotvalidConstraint, /* enable constraint but not valid*/
+ AT_EnablenotvalidConstraintRecurse, /* internal to commands/tablecmds.c */
+ AT_DisableConstraint, /* disable constraint */
+ AT_DisableConstraintRecurse, /* internal to commands/tablecmds.c */
AT_ValidateConstraint, /* validate constraint */
AT_ValidateConstraintRecurse, /* internal to commands/tablecmds.c */
AT_ProcessedConstraint, /* pre-processed add constraint (local in
@@ -1617,6 +1623,9 @@
/* Fields used for constraints that allow a NOT VALID specification */
bool skip_validation; /* skip validation of existing rows? */
bool initially_valid; /* mark the new constraint as valid? */
+
+ /* Fields used for constraints that allow be disabled */
+ bool isdisabled;
} Constraint;
/* ----------------------
diff -uNr b/src/test/regress/expected/enabled_constraint.out a/src/test/regress/expected/enabled_constraint.out
--- b/src/test/regress/expected/enabled_constraint.out 1970-01-01 08:00:00.000000000 +0800
+++ a/src/test/regress/expected/enabled_constraint.out 2013-08-30 09:34:21.925039741 +0800
@@ -0,0 +1,131 @@
+--
+--ENABLE/DISABLE CONSTRAINT TEST
+--
+--
+--CHECK CONSTRAINT TEST
+--
+--
+--CREATE TABLE WITH CHECK CONSTRAINT
+--
+CREATE TABLE aa
+(
+ a1 INT CHECK(a1>4),
+ a2 INT
+);
+--
+--CREATE CONSTRAINT WITH DISABLE
+--
+ALTER TABLE aa ADD CONSTRAINT aa_a2_check CHECK(a2>10) DISABLE;
+--
+--INSERT THE RIGHT DATA AND DELETE
+--
+INSERT INTO aa VALUES (10,1);
+DELETE FROM aa;
+ALTER TABLE aa DROP CONSTRAINT aa_a2_check;
+--
+--INSERT THE WRONG DATA AND UPDATE THE RIGHT DATA
+--
+INSERT INTO aa VALUES (1,1);
+ERROR: new row for relation "aa" violates check constraint "aa_a1_check"
+DETAIL: Failing row contains (1, 1).
+INSERT INTO aa VALUES (5,2);
+UPDATE aa SET a1=2 WHERE a2=2;
+ERROR: new row for relation "aa" violates check constraint "aa_a1_check"
+DETAIL: Failing row contains (2, 2).
+--
+--DISABLE CONSTRAINT
+--
+ALTER TABLE aa DISABLE CONSTRAINT aa_a1_check;
+--
+--INSERT AND UPDATE TEST WITH THE DISBLE CONSTRAINT
+--
+INSERT INTO aa VALUES (1,1);
+UPDATE aa SET a1 = 2 WHERE a2 = 2;
+--
+--TEST ENABLE CONSRTAINT WITH THE WRONG DATA
+--
+ALTER TABLE aa ENABLE CONSTRAINT aa_a1_check;
+ERROR: check constraint "aa_a1_check" is violated by some row
+--
+--TEST ENABLE CONSRTAINT name NOT VALID
+--
+ALTER TABLE aa ENABLE CONSTRAINT aa_a1_check NOT VALID;
+--
+--INSERT TEST WITH THE ENABLE CONSTRAINT
+--
+INSERT INTO aa VALUES (1,1);
+ERROR: new row for relation "aa" violates check constraint "aa_a1_check"
+DETAIL: Failing row contains (1, 1).
+--
+--DIABLE CONSTRAINT AND DELETE THE DATA
+--
+ALTER TABLE aa DISABLE CONSTRAINT aa_a1_check;
+DELETE FROM aa;
+--
+--TEST ENABLE CONSRTAINT
+--
+ALTER TABLE aa ENABLE CONSTRAINT aa_a1_check;
+DROP TABLE aa;
+--
+--FOREIGN KEY CONSTRAINT TEST
+--
+--
+--CREATE TABLE WITH FOREIGN KEY CONSTRAINT
+--
+CREATE TABLE bb
+(
+ b1 INT PRIMARY KEY,
+ b2 INT
+);
+CREATE TABLE cc
+(
+ c1 INT REFERENCES bb(b1),
+ c2 INT
+);
+--
+--INSERT THE WRONG DATA AND UPDATE THE RIGHT DATA
+--
+INSERT INTO cc VALUES (1,1);
+ERROR: insert or update on table "cc" violates foreign key constraint "cc_c1_fkey"
+DETAIL: Key (c1)=(1) is not present in table "bb".
+INSERT INTO bb VALUES (2,2);
+INSERT INTO cc VALUES (2,2);
+UPDATE cc SET c1=1 WHERE c2=2;
+ERROR: insert or update on table "cc" violates foreign key constraint "cc_c1_fkey"
+DETAIL: Key (c1)=(1) is not present in table "bb".
+--
+--DISABLE CONSTRAINT
+--
+ALTER TABLE cc DISABLE CONSTRAINT cc_c1_fkey;
+--
+--INSERT AND UPDATE TEST WITH THE DISBLE CONSTRAINT
+--
+INSERT INTO cc VALUES (1,1);
+UPDATE cc SET c1 = 1 WHERE c2 = 2;
+--
+--TEST ENABLE CONSRTAINT WITH THE WRONG DATA
+--
+ALTER TABLE cc ENABLE CONSTRAINT cc_c1_fkey;
+ERROR: insert or update on table "cc" violates foreign key constraint "cc_c1_fkey"
+DETAIL: Key (c1)=(1) is not present in table "bb".
+--
+--TEST ENABLE CONSRTAINT name NOT VALID
+--
+ALTER TABLE cc ENABLE CONSTRAINT cc_c1_fkey NOT VALID;
+--
+--INSERT TEST WITH THE ENABLE CONSTRAINT
+--
+INSERT INTO cc VALUES (1,1);
+ERROR: insert or update on table "cc" violates foreign key constraint "cc_c1_fkey"
+DETAIL: Key (c1)=(1) is not present in table "bb".
+--
+--DIABLE CONSTRAINT AND DELETE THE DATA
+--
+ALTER TABLE cc DISABLE CONSTRAINT cc_c1_fkey;
+DELETE FROM cc;
+--
+--TEST ENABLE CONSRTAINT
+--
+ALTER TABLE cc ENABLE CONSTRAINT cc_c1_fkey;
+DROP TABLE cc;
+DROP TABLE bb;
diff -uNr b/src/test/regress/parallel_schedule a/src/test/regress/parallel_schedule
--- b/src/test/regress/parallel_schedule 2013-08-27 13:39:48.407497460 +0800
+++ a/src/test/regress/parallel_schedule 2013-08-27 11:31:21.756406627 +0800
@@ -13,7 +13,7 @@
# ----------
# The first group of parallel tests
# ----------
-test: boolean char name varchar text int2 int4 int8 oid float4 float8 bit numeric txid uuid enum money rangetypes
+test: boolean char name varchar text int2 int4 int8 oid float4 float8 bit numeric txid uuid enum money rangetypes enabled_constraint
# Depends on things setup during char, varchar and text
test: strings
diff -uNr b/src/test/regress/sql/enabled_constraint.sql a/src/test/regress/sql/enabled_constraint.sql
--- b/src/test/regress/sql/enabled_constraint.sql 1970-01-01 08:00:00.000000000 +0800
+++ a/src/test/regress/sql/enabled_constraint.sql 2013-08-29 15:38:37.683646065 +0800
@@ -0,0 +1,159 @@
+--
+--ENABLE/DISABLE CONSTRAINT TEST
+--
+
+--
+--CHECK CONSTRAINT TEST
+--
+
+--
+--CREATE TABLE WITH CHECK CONSTRAINT
+--
+
+CREATE TABLE aa
+(
+ a1 INT CHECK(a1>4),
+ a2 INT
+);
+
+--
+--CREATE CONSTRAINT WITH DISABLE
+--
+
+ALTER TABLE aa ADD CONSTRAINT aa_a2_check CHECK(a2>10) DISABLE;
+
+--
+--INSERT THE RIGHT DATA AND DELETE
+--
+
+INSERT INTO aa VALUES (10,1);
+DELETE FROM aa;
+ALTER TABLE aa DROP CONSTRAINT aa_a2_check;
+
+--
+--INSERT THE WRONG DATA AND UPDATE THE RIGHT DATA
+--
+
+INSERT INTO aa VALUES (1,1);
+INSERT INTO aa VALUES (5,2);
+UPDATE aa SET a1=2 WHERE a2=2;
+
+--
+--DISABLE CONSTRAINT
+--
+
+ALTER TABLE aa DISABLE CONSTRAINT aa_a1_check;
+
+--
+--INSERT AND UPDATE TEST WITH THE DISBLE CONSTRAINT
+--
+
+INSERT INTO aa VALUES (1,1);
+UPDATE aa SET a1 = 2 WHERE a2 = 2;
+
+--
+--TEST ENABLE CONSRTAINT WITH THE WRONG DATA
+--
+
+ALTER TABLE aa ENABLE CONSTRAINT aa_a1_check;
+
+--
+--TEST ENABLE CONSRTAINT name NOT VALID
+--
+
+ALTER TABLE aa ENABLE CONSTRAINT aa_a1_check NOT VALID;
+
+--
+--INSERT TEST WITH THE ENABLE CONSTRAINT
+--
+
+INSERT INTO aa VALUES (1,1);
+
+--
+--DIABLE CONSTRAINT AND DELETE THE DATA
+--
+
+ALTER TABLE aa DISABLE CONSTRAINT aa_a1_check;
+DELETE FROM aa;
+
+--
+--TEST ENABLE CONSRTAINT
+--
+
+ALTER TABLE aa ENABLE CONSTRAINT aa_a1_check;
+DROP TABLE aa;
+
+--
+--FOREIGN KEY CONSTRAINT TEST
+--
+
+--
+--CREATE TABLE WITH FOREIGN KEY CONSTRAINT
+--
+
+CREATE TABLE bb
+(
+ b1 INT PRIMARY KEY,
+ b2 INT
+);
+
+CREATE TABLE cc
+(
+ c1 INT REFERENCES bb(b1),
+ c2 INT
+);
+
+--
+--INSERT THE WRONG DATA AND UPDATE THE RIGHT DATA
+--
+
+INSERT INTO cc VALUES (1,1);
+INSERT INTO bb VALUES (2,2);
+INSERT INTO cc VALUES (2,2);
+UPDATE cc SET c1=1 WHERE c2=2;
+
+--
+--DISABLE CONSTRAINT
+--
+
+ALTER TABLE cc DISABLE CONSTRAINT cc_c1_fkey;
+
+--
+--INSERT AND UPDATE TEST WITH THE DISBLE CONSTRAINT
+--
+
+INSERT INTO cc VALUES (1,1);
+UPDATE cc SET c1 = 1 WHERE c2 = 2;
+
+--
+--TEST ENABLE CONSRTAINT WITH THE WRONG DATA
+--
+
+ALTER TABLE cc ENABLE CONSTRAINT cc_c1_fkey;
+
+--
+--TEST ENABLE CONSRTAINT name NOT VALID
+--
+
+ALTER TABLE cc ENABLE CONSTRAINT cc_c1_fkey NOT VALID;
+
+--
+--INSERT TEST WITH THE ENABLE CONSTRAINT
+--
+
+INSERT INTO cc VALUES (1,1);
+
+--
+--DIABLE CONSTRAINT AND DELETE THE DATA
+--
+
+ALTER TABLE cc DISABLE CONSTRAINT cc_c1_fkey;
+DELETE FROM cc;
+
+--
+--TEST ENABLE CONSRTAINT
+--
+
+ALTER TABLE cc ENABLE CONSTRAINT cc_c1_fkey;
+DROP TABLE cc;
+DROP TABLE bb;
On Fri, 2013-08-30 at 09:57 +0800, wangshuo@highgo.com.cn wrote:
Hi hackers,
In order to achieve enable/disable constraint name，I made a few
modifications to the code.First, someone used to build the constraints while building
table. Then inserting data must follow a certain order.
And people usually like to insert the data but not affected by
foreign keys or check.
Is there any semantic difference between marking a constraint as
DISABLED and simply dropping it? Or does it just make it easier to
re-add it later?
Regards,
Jeff Davis
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Jeff Davis-8 wrote
Is there any semantic difference between marking a constraint as
DISABLED and simply dropping it? Or does it just make it easier to
re-add it later?
I cannot answer the question but if there is none then the main concern I'd
have is capturing "meta-information" about WHY such a constraint has been
disabled instead of dropped.
I guess this whole feature extends from the trigger disable feature that
already exists. Given we have the one adding this seems symmetrical...
I cannot really see using either feature on a production system (if
following best practices) but I can imagine where they could both be helpful
during development. Note with this usage pattern the meta-information about
"why" becomes considerably less important.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/ENABLE-DISABLE-CONSTRAINT-NAME-tp5769136p5769337.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
于 2013-09-03 08:15, David Johnston 回复:
Jeff Davis-8 wrote
Is there any semantic difference between marking a constraint as
DISABLED and simply dropping it? Or does it just make it easier to
re-add it later?
David Johnston wrote:
I cannot answer the question but if there is none then the main
concern I'd
have is capturing "meta-information" about WHY such a constraint has
been
disabled instead of dropped.
Drop/build and disable/enable constraint has no fundamental difference,
and could achieve the same purpose.What I do also more convenient for
the user.
Recording the disabled constraints is easier than recoding all the
constrains.
What's more, a lot of people ever asked about turing off constraint and
The sql2008 support this.So I think it's necessary in some ways.
I guess this whole feature extends from the trigger disable feature
that
already exists. Given we have the one adding this seems
symmetrical...I cannot really see using either feature on a production system (if
following best practices) but I can imagine where they could both be
helpful
during development. Note with this usage pattern the
meta-information about
"why" becomes considerably less important.David J.
Wang Shuo
HighGo Software Co.,Ltd.
September 3, 2013
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Sep 3, 2013 at 3:13 AM, <wangshuo@highgo.com.cn> wrote:
于 2013-09-03 08:15, David Johnston 回复:
Jeff Davis-8 wrote
Is there any semantic difference between marking a constraint as
DISABLED and simply dropping it? Or does it just make it easier to
re-add it later?David Johnston wrote:
I cannot answer the question but if there is none then the main concern
I'd
have is capturing "meta-information" about WHY such a constraint has been
disabled instead of dropped.Drop/build and disable/enable constraint has no fundamental difference,
and could achieve the same purpose.What I do also more convenient for the
user.
Recording the disabled constraints is easier than recoding all the
constrains.
What's more, a lot of people ever asked about turing off constraint and
The sql2008 support this.So I think it's necessary in some ways.
Please add your patch to the upcoming CommitFest so we don't forget about it.
https://commitfest.postgresql.org/action/commitfest_view/open
Please see also https://wiki.postgresql.org/wiki/Submitting_a_Patch
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
于 2013-09-05 01:56, Robert Haas 回复:
On Tue, Sep 3, 2013 at 3:13 AM, <wangshuo@highgo.com.cn> wrote:
于 2013-09-03 08:15, David Johnston 回复:
Jeff Davis-8 wrote
Is there any semantic difference between marking a constraint as
DISABLED and simply dropping it? Or does it just make it easier to
re-add it later?David Johnston wrote:
I cannot answer the question but if there is none then the main
concern
I'd
have is capturing "meta-information" about WHY such a constraint
has been
disabled instead of dropped.Drop/build and disable/enable constraint has no fundamental
difference,
and could achieve the same purpose.What I do also more convenient
for the
user.
Recording the disabled constraints is easier than recoding all the
constrains.
What's more, a lot of people ever asked about turing off constraint
and
The sql2008 support this.So I think it's necessary in some ways.Please add your patch to the upcoming CommitFest so we don't forget
about it.https://commitfest.postgresql.org/action/commitfest_view/open
Please see also https://wiki.postgresql.org/wiki/Submitting_a_Patch
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
I had committed the patch to the Server Features
(https://commitfest.postgresql.org/action/commitfest_view/open).
Is this right ? If not, please give me more advice,thanks !
Wang Shuo
HighGo Software Co.,Ltd.
September 5, 2013
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Sep 5, 2013 at 12:27 PM, <wangshuo@highgo.com.cn> wrote:
I had committed the patch to the Server Features
(https://commitfest.postgresql.org/action/commitfest_view/open).
Is this right ? If not, please give me more advice,thanks !
Yes this category is fine don't worry.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 9/3/13 3:13 AM, wangshuo@highgo.com.cn wrote:
Drop/build and disable/enable constraint has no fundamental difference,
and could achieve the same purpose.What I do also more convenient for
the user.
Recording the disabled constraints is easier than recoding all the
constrains.
Note that other schema objects can depend on the existence of
constraints. For example, the validity of a view might depend on the
existence of a primary key constraint. What would you do with the view
if the primary key constraint is temporarily disabled?
What's more, a lot of people ever asked about turing off constraint and
The sql2008 support this.So I think it's necessary in some ways.
I don't see this in the SQL standard. There is [NOT] ENFORCED, but
that's something different. Implementing that instead might actually
address the above concern.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
于 2013-09-09 20:54, Peter Eisentraut 回复:
On 9/3/13 3:13 AM, wangshuo@highgo.com.cn wrote:
Drop/build and disable/enable constraint has no fundamental
difference,
and could achieve the same purpose.What I do also more convenient
for
the user.
Recording the disabled constraints is easier than recoding all the
constrains.
Peter Eisentraut wrote:
Note that other schema objects can depend on the existence of
constraints. For example, the validity of a view might depend on the
existence of a primary key constraint. What would you do with the
view
if the primary key constraint is temporarily disabled?
Thanks for your reply.
I could't clearly understand your opinion, could you give me more
information or example？
What's more, a lot of people ever asked about turing off constraint
and
The sql2008 support this.So I think it's necessary in some ways.I don't see this in the SQL standard. There is [NOT] ENFORCED, but
that's something different. Implementing that instead might actually
address the above concern.
You are right. I had checked the SQL standard. There is not
ENABLE/DISABLE.
Sorry.
I misunderstood the former discussion about the constraint and the SQL
standard.
Thanks ,again.
Wang Shuo
HighGo Software Co.,Ltd.
September 11, 2013
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 9/11/13 1:09 AM, wangshuo@highgo.com.cn wrote:
Peter Eisentraut wrote:
Note that other schema objects can depend on the existence of
constraints. For example, the validity of a view might depend on the
existence of a primary key constraint. What would you do with the view
if the primary key constraint is temporarily disabled?Thanks for your reply.
I could't clearly understand your opinion, could you give me more
information or example？
=> create table test1 (a int constraint pk primary key, b text);
=> create view test2 as select a, b from test1 group by a;
=> alter table test1 drop constraint pk;
ERROR: 2BP01: cannot drop constraint pk on table test1 because other
objects depend on it
DETAIL: view test2 depends on constraint pk on table test1
HINT: Use DROP ... CASCADE to drop the dependent objects too.
(This has to do with whether ungrouped columns are allowed in the select
list when the presence of constraints ensures well-defined results.)
When trying to drop the constraint, the choice is to abort the drop or
to drop dependent objects. When you are talking about
enabling/disabling the constraint, it's not clear what to do.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 09/13/2013 05:23, Peter Eisentraut wrote:
=> create table test1 (a int constraint pk primary key, b text);
=> create view test2 as select a, b from test1 group by a;
=> alter table test1 drop constraint pk;
ERROR: 2BP01: cannot drop constraint pk on table test1 because other
objects depend on it
DETAIL: view test2 depends on constraint pk on table test1
HINT: Use DROP ... CASCADE to drop the dependent objects too.(This has to do with whether ungrouped columns are allowed in the
select
list when the presence of constraints ensures well-defined results.)When trying to drop the constraint, the choice is to abort the drop
or
to drop dependent objects. When you are talking about
enabling/disabling the constraint, it's not clear what to do.
Thanks for your reply.
First, I had said that I I only made a few modifications to the check
and the
foreign key constraint, and did nothing with primary key constraint.
On 08/30/2013 02:03 PM, I wrote:
Due to the above reasons，I realized this command.
I add a field named 'conenabled' to pg_constraint, identifying whether
a constraint is enable or not;
I enable or disable a foreign key constraint, by enable or disable the
triggers of the foreign key;
Our database will depend on the value of 'conenabled' to use the check
constrint or not;
In the alter_table.sgml, I wrote:
This form enables or disables a foreign key or check constraint.
Second, I tested the check and the foreign key constraint as your test
above.
And no error found, as fellow:
postgres=# create table a1 (a1 int check(a1>4));
CREATE TABLE
postgres=# create view a11 as select * from a1;
CREATE VIEW
postgres=# alter table a1 disable constraint a1_a1_check;
ALTER TABLE
postgres=# insert into a1 values (3);
INSERT 0 1
postgres=# select * from a11;
a1
----
3
(1 row)
postgres=# alter table a1 drop constraint a1_a1_check;
ALTER TABLE
postgres=# create table bb(b1 int primary key);
CREATE TABLE
postgres=# create table cc(c1 int references bb(b1));
CREATE TABLE
postgres=# create view c11 as select * from cc;
CREATE VIEW
postgres=# alter table cc disable constraint cc_c1_fkey;
ALTER TABLE
postgres=# insert into cc values (1);
INSERT 0 1
postgres=# select * from c11;
c1
----
1
(1 row)
postgres=# alter table cc drop constraint cc_c1_fkey;
ALTER TABLE
Wang Shuo
HighGo Software Co.,Ltd.
September 13, 2013
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Sep 12, 2013 at 10:03 PM, <wangshuo@highgo.com.cn> wrote:
Second, I tested the check and the foreign key constraint as your test
above.
And no error found, as fellow:
You're missing the point. Peter wasn't worried that your patch throws
an error; he's concerned about the fact that it doesn't.
In PostgreSQL, you can only create the following view because test1
has a primary key over column a:
=> create table test1 (a int constraint pk primary key, b text);
=> create view test2 as select a, b from test1 group by a;
=> alter table test1 drop constraint pk;
The reason that, if the primary key weren't there, it would be
ambiguous which row should be returned as among multiple values where
a is equal and b is not. If you can disable the constraint, then you
can create precisely that problem.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--On 13. September 2013 20:17:19 -0400 Robert Haas <robertmhaas@gmail.com>
wrote:
You're missing the point. Peter wasn't worried that your patch throws
an error; he's concerned about the fact that it doesn't.In PostgreSQL, you can only create the following view because test1
has a primary key over column a:=> create table test1 (a int constraint pk primary key, b text);
=> create view test2 as select a, b from test1 group by a;
=> alter table test1 drop constraint pk;The reason that, if the primary key weren't there, it would be
ambiguous which row should be returned as among multiple values where
a is equal and b is not. If you can disable the constraint, then you
can create precisely that problem.
Hmm not sure i understand this argument either: this patch doesn't allow
disabling a primary key. It only supports FKs and CHECK constraints
explicitly.
--
Thanks
Bernd
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Sep 24, 2013 at 5:58 AM, Bernd Helmle <mailings@oopsware.de> wrote:
--On 13. September 2013 20:17:19 -0400 Robert Haas <robertmhaas@gmail.com>
wrote:You're missing the point. Peter wasn't worried that your patch throws
an error; he's concerned about the fact that it doesn't.In PostgreSQL, you can only create the following view because test1
has a primary key over column a:=> create table test1 (a int constraint pk primary key, b text);
=> create view test2 as select a, b from test1 group by a;
=> alter table test1 drop constraint pk;The reason that, if the primary key weren't there, it would be
ambiguous which row should be returned as among multiple values where
a is equal and b is not. If you can disable the constraint, then you
can create precisely that problem.Hmm not sure i understand this argument either: this patch doesn't allow
disabling a primary key. It only supports FKs and CHECK constraints
explicitly.
Well, that is certainly one way of skating around the specific concern
Peter raised.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, 2013-09-24 at 11:58 +0200, Bernd Helmle wrote:
Hmm not sure i understand this argument either: this patch doesn't
allow disabling a primary key. It only supports FKs and CHECK
constraints explicitly.
Well, as soon as the patch for cataloging not-null constraints as check
constraints is available, it will be possible to create views that
depend functionally on check constraints. Then you'll have the same
problem there.
It's also not clear why this patch only supports foreign keys and check
constraints. Maybe that's what was convenient to implement, but it's
not a principled solution to the general issue that constraints can be
involved in dependencies.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Sep 24, 2013 at 10:40 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
On Tue, 2013-09-24 at 11:58 +0200, Bernd Helmle wrote:
Hmm not sure i understand this argument either: this patch doesn't
allow disabling a primary key. It only supports FKs and CHECK
constraints explicitly.Well, as soon as the patch for cataloging not-null constraints as check
constraints is available, it will be possible to create views that
depend functionally on check constraints. Then you'll have the same
problem there.It's also not clear why this patch only supports foreign keys and check
constraints. Maybe that's what was convenient to implement, but it's
not a principled solution to the general issue that constraints can be
involved in dependencies.
I agree with these concerns, as well as those raised by Tom Lane and
Fabien COELHO, and I think they indicate that we shouldn't accept this
patch. So I'm marking this as Rejected.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 10/9/13 1:10 PM, Robert Haas wrote:
On Tue, Sep 24, 2013 at 10:40 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
On Tue, 2013-09-24 at 11:58 +0200, Bernd Helmle wrote:
Hmm not sure i understand this argument either: this patch doesn't
allow disabling a primary key. It only supports FKs and CHECK
constraints explicitly.Well, as soon as the patch for cataloging not-null constraints as check
constraints is available, it will be possible to create views that
depend functionally on check constraints. Then you'll have the same
problem there.It's also not clear why this patch only supports foreign keys and check
constraints. Maybe that's what was convenient to implement, but it's
not a principled solution to the general issue that constraints can be
involved in dependencies.I agree with these concerns, as well as those raised by Tom Lane and
Fabien COELHO, and I think they indicate that we shouldn't accept this
patch. So I'm marking this as Rejected.
I see a use case for disabling FKs and CHECKS but not PKs or UNIQUE constraints: FKs and CHECKS don't depend on additional state information (namely an index), so it's easy to just disable them temporarily and then re-enable them. The same isn't true about a PK or UNIQUE constraint.
Of course we could decide to do something more complex to handle disabling PK/UNIQUE... though at that point it'd be better to just allow temporarily disabling any index. But I think there's an argument to be made for that being beyond the scope of disabling "simple" constraints... it's a pretty high bar to set that we won't accept a patch that disables simple constraints but not those involving indexes.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2013-10-10 02:10, Robert Haas wrote:
I agree with these concerns, as well as those raised by Tom Lane and
Fabien COELHO, and I think they indicate that we shouldn't accept
this
patch. So I'm marking this as Rejected.
On 2013-10-11 06:48, Jim Nasby wrote:
I see a use case for disabling FKs and CHECKS but not PKs or UNIQUE
constraints: FKs and CHECKS don't depend on additional state
information (namely an index), so >it's easy to just disable them
temporarily and then re-enable them. The same isn't true about a PK or
UNIQUE constraint.Of course we could decide to do something more complex to handle
disabling PK/UNIQUE... though at that point it'd be better to just
allow temporarily disabling >any index. But I think there's an argument
to be made for that being beyond the scope of disabling "simple"
constraints... it's a pretty high bar to set that we ?>won't accept a
patch that disables simple constraints but not those involving indexes.
Thanks for your reply.
I found my patch's weakness.I think the DISABLE/ENABLE patch is
necessary.
I will pack a new patch for all the constraints to commit.
Thanks again.
Yours,
Wang Shuo
HighGo Software Co.,Ltd.
October 11, 2013
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers