Index: doc/src/sgml/ref/set_constraints.sgml =================================================================== RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/ref/set_constraints.sgml,v retrieving revision 1.4 diff -c -r1.4 set_constraints.sgml *** doc/src/sgml/ref/set_constraints.sgml 3 Jun 2002 01:10:38 -0000 1.4 --- doc/src/sgml/ref/set_constraints.sgml 7 Aug 2002 23:45:40 -0000 *************** *** 22,31 **** SET CONSTRAINTS sets the behavior of constraint ! evaluation in the current transaction. In ! mode, constraints are checked at the end ! of each statement. In mode, constraints ! are not checked until transaction commit. --- 22,53 ---- SET CONSTRAINTS sets the behavior of constraint ! evaluation in the current transaction. In mode, constraints are checked at the end of each ! statement. In mode, constraints are not ! checked until transaction commit. ! ! ! ! ! This command only alters the behavior of constraints within the ! current transaction. Thus, if you execute this command outside ! of an explicit transaction block (such as one started with ! BEGIN), it will not appear to have any effect. ! If you wish to change the behavior of a constraint without needing ! to issue a SET CONSTRAINTS command in every ! transaction, specify or ! when you create the constraint. ! ! ! ! ! When you change the mode of a constraint to be , the new constraint mode takes effect retroactively: ! any outstanding data modifications that would have been checked ! at the end of the transaction (when using ! ) are instead checked during the ! execution of the SET CONSTRAINTS command. *************** *** 52,58 **** SET CONSTRAINTS is defined in ! SQL92 and SQL99. --- 74,84 ---- SET CONSTRAINTS is defined in ! SQL92 and SQL99. The ! implementation in PostgreSQL complies ! with the behavior defined in the standard, except for the ! PostgreSQL limitation that SET ! CONSTRAINTS cannot be applied to check or unique constraints. Index: src/backend/commands/trigger.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/commands/trigger.c,v retrieving revision 1.124 diff -c -r1.124 trigger.c *** src/backend/commands/trigger.c 5 Aug 2002 03:29:17 -0000 1.124 --- src/backend/commands/trigger.c 7 Aug 2002 23:45:40 -0000 *************** *** 1283,1294 **** * state IMMEDIATE or DEFERRED. * ---------- */ - static bool deftrig_dfl_all_isset = false; - static bool deftrig_dfl_all_isdeferred = false; static List *deftrig_dfl_trigstates = NIL; ! static bool deftrig_all_isset; ! static bool deftrig_all_isdeferred; static List *deftrig_trigstates; /* ---------- --- 1283,1292 ---- * state IMMEDIATE or DEFERRED. * ---------- */ static List *deftrig_dfl_trigstates = NIL; ! static bool deftrig_all_isset = false; ! static bool deftrig_all_isdeferred = false; static List *deftrig_trigstates; /* ---------- *************** *** 1719,1726 **** ALLOCSET_DEFAULT_MAXSIZE); oldcxt = MemoryContextSwitchTo(deftrig_cxt); ! deftrig_all_isset = deftrig_dfl_all_isset; ! deftrig_all_isdeferred = deftrig_dfl_all_isdeferred; deftrig_trigstates = NIL; foreach(l, deftrig_dfl_trigstates) --- 1717,1727 ---- ALLOCSET_DEFAULT_MAXSIZE); oldcxt = MemoryContextSwitchTo(deftrig_cxt); ! deftrig_all_isset = false; ! /* ! * If unspecified, constraints default to IMMEDIATE, per SQL ! */ ! deftrig_all_isdeferred = false; deftrig_trigstates = NIL; foreach(l, deftrig_dfl_trigstates) *************** *** 1810,1998 **** /* ---------- * DeferredTriggerSetState() * ! * Called for the users SET CONSTRAINTS ... utility command. * ---------- */ void DeferredTriggerSetState(ConstraintsSetStmt *stmt) { - Relation tgrel; List *l; ! List *ls; ! List *loid = NIL; ! MemoryContext oldcxt; ! bool found; ! DeferredTriggerStatus state; /* * Handle SET CONSTRAINTS ALL ... */ if (stmt->constraints == NIL) { ! if (!IsTransactionBlock()) { ! /* ! * ... outside of a transaction block ! * ! * Drop all information about individual trigger states per ! * session. ! */ ! l = deftrig_dfl_trigstates; ! while (l != NIL) ! { ! List *next = lnext(l); ! ! pfree(lfirst(l)); ! pfree(l); ! l = next; ! } ! deftrig_dfl_trigstates = NIL; ! ! /* ! * Set the session ALL state to known. ! */ ! deftrig_dfl_all_isset = true; ! deftrig_dfl_all_isdeferred = stmt->deferred; ! return; } ! else ! { ! /* ! * ... inside of a transaction block ! * ! * Drop all information about individual trigger states per ! * transaction. ! */ ! l = deftrig_trigstates; ! while (l != NIL) ! { ! List *next = lnext(l); ! ! pfree(lfirst(l)); ! pfree(l); ! l = next; ! } ! deftrig_trigstates = NIL; ! ! /* ! * Set the per transaction ALL state to known. ! */ ! deftrig_all_isset = true; ! deftrig_all_isdeferred = stmt->deferred; ! ! return; ! } ! } ! ! /* ---------- ! * Handle SET CONSTRAINTS constraint-name [, ...] ! * First lookup all trigger Oid's for the constraint names. ! * ---------- ! */ ! tgrel = heap_openr(TriggerRelationName, AccessShareLock); ! ! foreach(l, stmt->constraints) ! { ! char *cname = strVal(lfirst(l)); ! ScanKeyData skey; ! SysScanDesc tgscan; ! HeapTuple htup; ! ! /* ! * Check that only named constraints are set explicitly ! */ ! if (strlen(cname) == 0) ! elog(ERROR, "unnamed constraints cannot be set explicitly"); /* ! * Setup to scan pg_trigger by tgconstrname ... */ ! ScanKeyEntryInitialize(&skey, ! (bits16) 0x0, ! (AttrNumber) Anum_pg_trigger_tgconstrname, ! (RegProcedure) F_NAMEEQ, ! PointerGetDatum(cname)); ! ! tgscan = systable_beginscan(tgrel, TriggerConstrNameIndex, true, ! SnapshotNow, 1, &skey); ! ! /* ! * ... and search for the constraint trigger row */ ! found = false; ! while (HeapTupleIsValid(htup = systable_getnext(tgscan))) { ! Form_pg_trigger pg_trigger = (Form_pg_trigger) GETSTRUCT(htup); ! Oid constr_oid; /* ! * If we found some, check that they fit the deferrability but ! * skip ON RESTRICT ones, since they are silently ! * never deferrable. */ ! if (stmt->deferred && !pg_trigger->tgdeferrable && ! pg_trigger->tgfoid != F_RI_FKEY_RESTRICT_UPD && ! pg_trigger->tgfoid != F_RI_FKEY_RESTRICT_DEL) ! elog(ERROR, "Constraint '%s' is not deferrable", ! cname); ! ! AssertTupleDescHasOid(tgrel->rd_att); ! constr_oid = HeapTupleGetOid(htup); ! loid = lappendi(loid, constr_oid); ! found = true; ! } ! ! systable_endscan(tgscan); ! /* ! * Not found ? ! */ ! if (!found) ! elog(ERROR, "Constraint '%s' does not exist", cname); ! } ! heap_close(tgrel, AccessShareLock); ! if (!IsTransactionBlock()) ! { ! /* ! * Outside of a transaction block set the trigger states of ! * individual triggers on session level. ! */ ! oldcxt = MemoryContextSwitchTo(deftrig_gcxt); ! foreach(l, loid) ! { found = false; ! foreach(ls, deftrig_dfl_trigstates) ! { ! state = (DeferredTriggerStatus) lfirst(ls); ! if (state->dts_tgoid == (Oid) lfirsti(l)) ! { ! state->dts_tgisdeferred = stmt->deferred; ! found = true; ! break; ! } ! } ! if (!found) { ! state = (DeferredTriggerStatus) ! palloc(sizeof(DeferredTriggerStatusData)); ! state->dts_tgoid = (Oid) lfirsti(l); ! state->dts_tgisdeferred = stmt->deferred; ! deftrig_dfl_trigstates = ! lappend(deftrig_dfl_trigstates, state); } - } ! MemoryContextSwitchTo(oldcxt); - return; - } - else - { /* * Inside of a transaction block set the trigger states of * individual triggers on transaction level. --- 1811,1935 ---- /* ---------- * DeferredTriggerSetState() * ! * Called for the SET CONSTRAINTS ... utility command. * ---------- */ void DeferredTriggerSetState(ConstraintsSetStmt *stmt) { List *l; ! ! /* ! * If called outside a transaction block, we can safely return: this ! * command cannot effect any subsequent transactions, and there ! * are no "session-level" trigger settings. ! */ ! if (!IsTransactionBlock()) ! return; /* * Handle SET CONSTRAINTS ALL ... */ if (stmt->constraints == NIL) { ! /* ! * Drop all per-transaction information about individual trigger ! * states. ! */ ! l = deftrig_trigstates; ! while (l != NIL) { ! List *next = lnext(l); ! pfree(lfirst(l)); ! pfree(l); ! l = next; } ! deftrig_trigstates = NIL; /* ! * Set the per-transaction ALL state to known. */ ! deftrig_all_isset = true; ! deftrig_all_isdeferred = stmt->deferred; ! } ! else ! { ! Relation tgrel; ! MemoryContext oldcxt; ! bool found; ! DeferredTriggerStatus state; ! List *ls; ! List *loid = NIL; ! ! /* ---------- ! * Handle SET CONSTRAINTS constraint-name [, ...] ! * First lookup all trigger Oid's for the constraint names. ! * ---------- */ ! tgrel = heap_openr(TriggerRelationName, AccessShareLock); ! foreach(l, stmt->constraints) { ! char *cname = strVal(lfirst(l)); ! ScanKeyData skey; ! SysScanDesc tgscan; ! HeapTuple htup; /* ! * Check that only named constraints are set explicitly */ ! if (strlen(cname) == 0) ! elog(ERROR, "unnamed constraints cannot be set explicitly"); ! /* ! * Setup to scan pg_trigger by tgconstrname ... ! */ ! ScanKeyEntryInitialize(&skey, (bits16) 0x0, ! (AttrNumber) Anum_pg_trigger_tgconstrname, ! (RegProcedure) F_NAMEEQ, ! PointerGetDatum(cname)); ! tgscan = systable_beginscan(tgrel, TriggerConstrNameIndex, true, ! SnapshotNow, 1, &skey); ! /* ! * ... and search for the constraint trigger row ! */ found = false; ! ! while (HeapTupleIsValid(htup = systable_getnext(tgscan))) { ! Form_pg_trigger pg_trigger = (Form_pg_trigger) GETSTRUCT(htup); ! Oid constr_oid; ! /* ! * If we found some, check that they fit the deferrability but ! * skip ON RESTRICT ones, since they are silently ! * never deferrable. ! */ ! if (stmt->deferred && !pg_trigger->tgdeferrable && ! pg_trigger->tgfoid != F_RI_FKEY_RESTRICT_UPD && ! pg_trigger->tgfoid != F_RI_FKEY_RESTRICT_DEL) ! elog(ERROR, "Constraint '%s' is not deferrable", ! cname); ! ! AssertTupleDescHasOid(tgrel->rd_att); ! constr_oid = HeapTupleGetOid(htup); ! loid = lappendi(loid, constr_oid); ! found = true; } ! systable_endscan(tgscan); ! ! /* ! * Not found ? ! */ ! if (!found) ! elog(ERROR, "Constraint '%s' does not exist", cname); ! } ! heap_close(tgrel, AccessShareLock); /* * Inside of a transaction block set the trigger states of * individual triggers on transaction level. *************** *** 2025,2033 **** } MemoryContextSwitchTo(oldcxt); - - return; } } --- 1962,1980 ---- } MemoryContextSwitchTo(oldcxt); } + + /* + * SQL99 requires that when a constraint is set to IMMEDIATE, any + * deferred checks against that constraint must be made when the + * SET CONSTRAINTS command is executed -- i.e. the effects of the + * SET CONSTRAINTS command applies retroactively. + * + * Since we have already made the necessary modifications to the + * constraints, all we need to do is re-check them if necessary. + */ + if (!stmt->deferred) + deferredTriggerInvokeEvents(true); } Index: src/backend/tcop/utility.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/tcop/utility.c,v retrieving revision 1.168 diff -c -r1.168 utility.c *** src/backend/tcop/utility.c 4 Aug 2002 04:31:44 -0000 1.168 --- src/backend/tcop/utility.c 7 Aug 2002 23:45:40 -0000 *************** *** 214,222 **** BeginTransactionBlock(); /* ! * Currently, the only option that can be set is ! * the transaction isolation level by START ! * TRANSACTION. */ if (stmt->options) { --- 214,221 ---- BeginTransactionBlock(); /* ! * Currently, the only option that can be set by ! * START TRANSACTION is the isolation level. */ if (stmt->options) { Index: src/test/regress/expected/foreign_key.out =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/foreign_key.out,v retrieving revision 1.17 diff -c -r1.17 foreign_key.out *** src/test/regress/expected/foreign_key.out 12 Jul 2002 18:43:19 -0000 1.17 --- src/test/regress/expected/foreign_key.out 7 Aug 2002 23:45:40 -0000 *************** *** 945,947 **** --- 945,1024 ---- drop table pktable; ERROR: table "pktable" does not exist drop table pktable_base; + -- + -- Deferrable constraints + -- (right now, only FOREIGN KEY constraints can be deferred) + -- + -- deferrable, explicitely deferred + CREATE TABLE pktable ( + id INT4 PRIMARY KEY, + other INT4 + ); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' + CREATE TABLE fktable ( + id INT4 PRIMARY KEY, + fk INT4 REFERENCES pktable DEFERRABLE + ); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'fktable_pkey' for table 'fktable' + NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + -- default to immediate: should fail + INSERT INTO fktable VALUES (5, 10); + ERROR: $1 referential integrity violation - key referenced from fktable not found in pktable + -- explicitely defer the constraint + BEGIN; + SET CONSTRAINTS ALL DEFERRED; + INSERT INTO fktable VALUES (10, 15); + INSERT INTO pktable VALUES (15, 0); -- make the FK insert valid + COMMIT; + DROP TABLE fktable, pktable; + -- deferrable, initially deferred + CREATE TABLE pktable ( + id INT4 PRIMARY KEY, + other INT4 + ); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' + CREATE TABLE fktable ( + id INT4 PRIMARY KEY, + fk INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED + ); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'fktable_pkey' for table 'fktable' + NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + -- default to deferred, should succeed + BEGIN; + INSERT INTO fktable VALUES (100, 200); + INSERT INTO pktable VALUES (200, 500); -- make the FK insert valid + COMMIT; + -- default to deferred, explicitely make immediate + BEGIN; + SET CONSTRAINTS ALL IMMEDIATE; + -- should fail + INSERT INTO fktable VALUES (500, 1000); + ERROR: $1 referential integrity violation - key referenced from fktable not found in pktable + COMMIT; + DROP TABLE fktable, pktable; + -- tricky behavior: according to SQL99, if a deferred constraint is set + -- to 'immediate' mode, it should be checked for validity *immediately*, + -- not when the current transaction commits (i.e. the mode change applies + -- retroactively) + CREATE TABLE pktable ( + id INT4 PRIMARY KEY, + other INT4 + ); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' + CREATE TABLE fktable ( + id INT4 PRIMARY KEY, + fk INT4 REFERENCES pktable DEFERRABLE + ); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'fktable_pkey' for table 'fktable' + NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + BEGIN; + SET CONSTRAINTS ALL DEFERRED; + -- should succeed, for now + INSERT INTO fktable VALUES (1000, 2000); + -- should cause transaction abort, due to preceding error + SET CONSTRAINTS ALL IMMEDIATE; + ERROR: $1 referential integrity violation - key referenced from fktable not found in pktable + INSERT INTO pktable VALUES (2000, 3); -- too late + ERROR: current transaction is aborted, queries ignored until end of transaction block + COMMIT; + DROP TABLE fktable, pktable; Index: src/test/regress/sql/foreign_key.sql =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/foreign_key.sql,v retrieving revision 1.7 diff -c -r1.7 foreign_key.sql *** src/test/regress/sql/foreign_key.sql 12 Jul 2002 18:43:19 -0000 1.7 --- src/test/regress/sql/foreign_key.sql 7 Aug 2002 23:45:40 -0000 *************** *** 583,585 **** --- 583,676 ---- drop table pktable; drop table pktable_base; + -- + -- Deferrable constraints + -- (right now, only FOREIGN KEY constraints can be deferred) + -- + + -- deferrable, explicitely deferred + CREATE TABLE pktable ( + id INT4 PRIMARY KEY, + other INT4 + ); + + CREATE TABLE fktable ( + id INT4 PRIMARY KEY, + fk INT4 REFERENCES pktable DEFERRABLE + ); + + -- default to immediate: should fail + INSERT INTO fktable VALUES (5, 10); + + -- explicitely defer the constraint + BEGIN; + + SET CONSTRAINTS ALL DEFERRED; + + INSERT INTO fktable VALUES (10, 15); + INSERT INTO pktable VALUES (15, 0); -- make the FK insert valid + + COMMIT; + + DROP TABLE fktable, pktable; + + -- deferrable, initially deferred + CREATE TABLE pktable ( + id INT4 PRIMARY KEY, + other INT4 + ); + + CREATE TABLE fktable ( + id INT4 PRIMARY KEY, + fk INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED + ); + + -- default to deferred, should succeed + BEGIN; + + INSERT INTO fktable VALUES (100, 200); + INSERT INTO pktable VALUES (200, 500); -- make the FK insert valid + + COMMIT; + + -- default to deferred, explicitely make immediate + BEGIN; + + SET CONSTRAINTS ALL IMMEDIATE; + + -- should fail + INSERT INTO fktable VALUES (500, 1000); + + COMMIT; + + DROP TABLE fktable, pktable; + + -- tricky behavior: according to SQL99, if a deferred constraint is set + -- to 'immediate' mode, it should be checked for validity *immediately*, + -- not when the current transaction commits (i.e. the mode change applies + -- retroactively) + CREATE TABLE pktable ( + id INT4 PRIMARY KEY, + other INT4 + ); + + CREATE TABLE fktable ( + id INT4 PRIMARY KEY, + fk INT4 REFERENCES pktable DEFERRABLE + ); + + BEGIN; + + SET CONSTRAINTS ALL DEFERRED; + + -- should succeed, for now + INSERT INTO fktable VALUES (1000, 2000); + + -- should cause transaction abort, due to preceding error + SET CONSTRAINTS ALL IMMEDIATE; + + INSERT INTO pktable VALUES (2000, 3); -- too late + + COMMIT; + + DROP TABLE fktable, pktable;