Index: src/backend/commands/tablecmds.c =================================================================== RCS file: /var/lib/cvs/pgsql/src/backend/commands/tablecmds.c,v retrieving revision 1.157 diff -c -r1.157 tablecmds.c *** src/backend/commands/tablecmds.c 10 May 2005 13:16:26 -0000 1.157 --- src/backend/commands/tablecmds.c 30 May 2005 02:50:45 -0000 *************** *** 156,167 **** } NewColumnValue; - /* Used by attribute and relation renaming routines: */ - #define RI_TRIGGER_PK 1 /* is a trigger on the PK relation */ - #define RI_TRIGGER_FK 2 /* is a trigger on the FK relation */ - #define RI_TRIGGER_NONE 0 /* is not an RI trigger function */ - - static List *MergeAttributes(List *schema, List *supers, bool istemp, List **supOids, List **supconstr, int *supOidCount); static bool change_varattnos_of_a_node(Node *node, const AttrNumber *newattno); --- 156,161 ---- *************** *** 246,252 **** char *tablespacename); static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace); static void copy_relation_data(Relation rel, SMgrRelation dst); - static int ri_trigger_type(Oid tgfoid); static void update_ri_trigger_args(Oid relid, const char *oldname, const char *newname, --- 240,245 ---- *************** *** 1571,1609 **** relation_close(targetrelation, NoLock); } - - /* - * Given a trigger function OID, determine whether it is an RI trigger, - * and if so whether it is attached to PK or FK relation. - * - * XXX this probably doesn't belong here; should be exported by - * ri_triggers.c - */ - static int - ri_trigger_type(Oid tgfoid) - { - switch (tgfoid) - { - case F_RI_FKEY_CASCADE_DEL: - case F_RI_FKEY_CASCADE_UPD: - case F_RI_FKEY_RESTRICT_DEL: - case F_RI_FKEY_RESTRICT_UPD: - case F_RI_FKEY_SETNULL_DEL: - case F_RI_FKEY_SETNULL_UPD: - case F_RI_FKEY_SETDEFAULT_DEL: - case F_RI_FKEY_SETDEFAULT_UPD: - case F_RI_FKEY_NOACTION_DEL: - case F_RI_FKEY_NOACTION_UPD: - return RI_TRIGGER_PK; - - case F_RI_FKEY_CHECK_INS: - case F_RI_FKEY_CHECK_UPD: - return RI_TRIGGER_FK; - } - - return RI_TRIGGER_NONE; - } - /* * Scan pg_trigger for RI triggers that are on the specified relation * (if fk_scan is false) or have it as the tgconstrrel (if fk_scan --- 1564,1569 ---- *************** *** 1663,1669 **** const char *arga[RI_MAX_ARGUMENTS]; const char *argp; ! tg_type = ri_trigger_type(pg_trigger->tgfoid); if (tg_type == RI_TRIGGER_NONE) { /* Not an RI trigger, forget it */ --- 1623,1629 ---- const char *arga[RI_MAX_ARGUMENTS]; const char *argp; ! tg_type = RI_FKey_trigger_type(pg_trigger->tgfoid); if (tg_type == RI_TRIGGER_NONE) { /* Not an RI trigger, forget it */ Index: src/backend/commands/trigger.c =================================================================== RCS file: /var/lib/cvs/pgsql/src/backend/commands/trigger.c,v retrieving revision 1.188 diff -c -r1.188 trigger.c *** src/backend/commands/trigger.c 6 May 2005 17:24:53 -0000 1.188 --- src/backend/commands/trigger.c 30 May 2005 02:47:03 -0000 *************** *** 2994,3047 **** continue; /* ! * If it is an RI UPDATE trigger, and the referenced keys have ! * not changed, short-circuit queuing of the event; there's no ! * need to fire the trigger. */ if ((event & TRIGGER_EVENT_OPMASK) == TRIGGER_EVENT_UPDATE) { ! bool is_ri_trigger; ! ! switch (trigger->tgfoid) { ! case F_RI_FKEY_NOACTION_UPD: ! case F_RI_FKEY_CASCADE_UPD: ! case F_RI_FKEY_RESTRICT_UPD: ! case F_RI_FKEY_SETNULL_UPD: ! case F_RI_FKEY_SETDEFAULT_UPD: ! is_ri_trigger = true; break; ! default: ! is_ri_trigger = false; break; - } - - if (is_ri_trigger) - { - TriggerData LocTriggerData; - - LocTriggerData.type = T_TriggerData; - LocTriggerData.tg_event = - TRIGGER_EVENT_UPDATE | TRIGGER_EVENT_ROW; - LocTriggerData.tg_relation = rel; - LocTriggerData.tg_trigtuple = oldtup; - LocTriggerData.tg_newtuple = newtup; - LocTriggerData.tg_trigger = trigger; - /* - * We do not currently know which buffers the passed tuples - * are in, but it does not matter because RI_FKey_keyequal_upd - * does not care. We could expand the API of this function - * if it becomes necessary to set these fields accurately. - */ - LocTriggerData.tg_trigtuplebuf = InvalidBuffer; - LocTriggerData.tg_newtuplebuf = InvalidBuffer; ! if (RI_FKey_keyequal_upd(&LocTriggerData)) ! { ! /* key unchanged, so skip queuing this event */ ! continue; ! } } } --- 2994,3040 ---- continue; /* ! * If this is an UPDATE of a PK table or FK table that does ! * not change the PK or FK respectively, we can skip queuing ! * the event: there is no need to fire the trigger. */ if ((event & TRIGGER_EVENT_OPMASK) == TRIGGER_EVENT_UPDATE) { ! switch (RI_FKey_trigger_type(trigger->tgfoid)) { ! case RI_TRIGGER_PK: ! /* Update on PK table */ ! if (RI_FKey_keyequal_upd_pk(trigger, rel, oldtup, newtup)) ! { ! /* key unchanged, so skip queuing this event */ ! continue; ! } break; ! case RI_TRIGGER_FK: ! /* ! * Update on FK table ! * ! * There is one exception when updating FK tables: ! * if the updated row was inserted by our own ! * transaction and the FK is deferred, we still ! * need to fire the trigger. This is because our ! * UPDATE will invalidate the INSERT so the ! * end-of-transaction INSERT RI trigger will not ! * do anything, so we have to do the check for the ! * UPDATE anyway. ! */ ! if (HeapTupleHeaderGetXmin(oldtup->t_data) != ! GetCurrentTransactionId() && ! RI_FKey_keyequal_upd_fk(trigger, rel, oldtup, newtup)) ! { ! continue; ! } break; ! case RI_TRIGGER_NONE: ! /* Not an FK trigger */ ! break; } } Index: src/backend/utils/adt/ri_triggers.c =================================================================== RCS file: /var/lib/cvs/pgsql/src/backend/utils/adt/ri_triggers.c,v retrieving revision 1.78 diff -c -r1.78 ri_triggers.c *** src/backend/utils/adt/ri_triggers.c 29 May 2005 04:23:05 -0000 1.78 --- src/backend/utils/adt/ri_triggers.c 30 May 2005 02:43:26 -0000 *************** *** 38,47 **** #include "optimizer/planmain.h" #include "parser/parse_oper.h" #include "rewrite/rewriteHandler.h" - #include "utils/lsyscache.h" - #include "utils/typcache.h" #include "utils/acl.h" #include "utils/guc.h" #include "miscadmin.h" --- 38,48 ---- #include "optimizer/planmain.h" #include "parser/parse_oper.h" #include "rewrite/rewriteHandler.h" #include "utils/acl.h" + #include "utils/fmgroids.h" #include "utils/guc.h" + #include "utils/lsyscache.h" + #include "utils/typcache.h" #include "miscadmin.h" *************** *** 375,396 **** break; } - /* - * No need to check anything if old and new references are the same on - * UPDATE. - */ - if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) - { - if (HeapTupleHeaderGetXmin(old_row->t_data) != - GetCurrentTransactionId() && - ri_KeysEqual(fk_rel, old_row, new_row, &qkey, - RI_KEYPAIR_FK_IDX)) - { - heap_close(pk_rel, RowShareLock); - return PointerGetDatum(NULL); - } - } - if (SPI_connect() != SPI_OK_CONNECT) elog(ERROR, "SPI_connect failed"); --- 376,381 ---- *************** *** 2005,2012 **** * corresponding to changed columns in pk_rel's key */ if (match_type == RI_MATCH_TYPE_FULL || ! !ri_OneKeyEqual(pk_rel, i, old_row, new_row, &qkey, ! RI_KEYPAIR_PK_IDX)) { snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), "%s %s = NULL", querysep, attname); --- 1990,1997 ---- * corresponding to changed columns in pk_rel's key */ if (match_type == RI_MATCH_TYPE_FULL || ! !ri_OneKeyEqual(pk_rel, i, old_row, new_row, &qkey, ! RI_KEYPAIR_PK_IDX)) { snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), "%s %s = NULL", querysep, attname); *************** *** 2016,2022 **** qualsep, attname, i + 1); qualsep = "AND"; queryoids[i] = SPI_gettypeid(pk_rel->rd_att, ! qkey.keypair[i][RI_KEYPAIR_PK_IDX]); } strcat(querystr, qualstr); --- 2001,2007 ---- qualsep, attname, i + 1); qualsep = "AND"; queryoids[i] = SPI_gettypeid(pk_rel->rd_att, ! qkey.keypair[i][RI_KEYPAIR_PK_IDX]); } strcat(querystr, qualstr); *************** *** 2451,2480 **** /* ---------- ! * RI_FKey_keyequal_upd - ! * ! * Check if we have a key change on update. * ! * This is not a real trigger procedure. It is used by the AFTER ! * trigger queue manager to detect "triggered data change violation". * ---------- */ bool ! RI_FKey_keyequal_upd(TriggerData *trigdata) { int tgnargs; char **tgargs; Relation fk_rel; - Relation pk_rel; - HeapTuple new_row; - HeapTuple old_row; RI_QueryKey qkey; /* * Check for the correct # of call arguments */ ! tgnargs = trigdata->tg_trigger->tgnargs; ! tgargs = trigdata->tg_trigger->tgargs; if (tgnargs < 4 || tgnargs > RI_MAX_ARGUMENTS || (tgnargs % 2) != 0) --- 2436,2461 ---- /* ---------- ! * RI_FKey_keyequal_upd_pk - * ! * Check if we have a key change on an update to a PK relation. This is ! * used by the AFTER trigger queue manager to detect "triggered data ! * change violation". * ---------- */ bool ! RI_FKey_keyequal_upd_pk(Trigger *trigger, Relation pk_rel, HeapTuple old_row, HeapTuple new_row) { int tgnargs; char **tgargs; Relation fk_rel; RI_QueryKey qkey; /* * Check for the correct # of call arguments */ ! tgnargs = trigger->tgnargs; ! tgargs = trigger->tgargs; if (tgnargs < 4 || tgnargs > RI_MAX_ARGUMENTS || (tgnargs % 2) != 0) *************** *** 2489,2536 **** if (tgnargs == 4) return true; ! /* ! * Get the relation descriptors of the FK and PK tables and the new ! * and old tuple. ! * ! * Use minimal locking for fk_rel here. ! */ ! if (!OidIsValid(trigdata->tg_trigger->tgconstrrelid)) ereport(ERROR, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), ! errmsg("no target table given for trigger \"%s\" on table \"%s\"", ! trigdata->tg_trigger->tgname, ! RelationGetRelationName(trigdata->tg_relation)), ! errhint("Remove this referential integrity trigger and its mates, then do ALTER TABLE ADD CONSTRAINT."))); ! fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, AccessShareLock); ! pk_rel = trigdata->tg_relation; ! new_row = trigdata->tg_newtuple; ! old_row = trigdata->tg_trigtuple; switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO])) { - /* - * MATCH - */ case RI_MATCH_TYPE_UNSPECIFIED: case RI_MATCH_TYPE_FULL: ! ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid, RI_PLAN_KEYEQUAL_UPD, fk_rel, pk_rel, tgnargs, tgargs); - heap_close(fk_rel, AccessShareLock); ! /* ! * Return if key's are equal ! */ return ri_KeysEqual(pk_rel, old_row, new_row, &qkey, RI_KEYPAIR_PK_IDX); ! /* ! * Handle MATCH PARTIAL set null delete. ! */ case RI_MATCH_TYPE_PARTIAL: ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), --- 2470,2501 ---- if (tgnargs == 4) return true; ! if (!OidIsValid(trigger->tgconstrrelid)) ereport(ERROR, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), ! errmsg("no target table given for trigger \"%s\" on table \"%s\"", ! trigger->tgname, ! RelationGetRelationName(pk_rel)), ! errhint("Remove this referential integrity trigger and its mates, " ! "then do ALTER TABLE ADD CONSTRAINT."))); ! fk_rel = heap_open(trigger->tgconstrrelid, AccessShareLock); switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO])) { case RI_MATCH_TYPE_UNSPECIFIED: case RI_MATCH_TYPE_FULL: ! ri_BuildQueryKeyFull(&qkey, trigger->tgoid, RI_PLAN_KEYEQUAL_UPD, fk_rel, pk_rel, tgnargs, tgargs); heap_close(fk_rel, AccessShareLock); ! /* Return if key's are equal */ return ri_KeysEqual(pk_rel, old_row, new_row, &qkey, RI_KEYPAIR_PK_IDX); ! /* Handle MATCH PARTIAL set null delete. */ case RI_MATCH_TYPE_PARTIAL: ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), *************** *** 2538,2551 **** break; } /* ! * Never reached */ elog(ERROR, "invalid match_type"); return false; } - /* ---------- * RI_Initial_Check - * --- 2503,2586 ---- break; } + /* Never reached */ + elog(ERROR, "invalid match_type"); + return false; + } + + /* ---------- + * RI_FKey_keyequal_upd_fk - + * + * Check if we have a key change on an update to an FK relation. This is + * used by the AFTER trigger queue manager to detect "triggered data + * change violation". + * ---------- + */ + bool + RI_FKey_keyequal_upd_fk(Trigger *trigger, Relation fk_rel, HeapTuple old_row, HeapTuple new_row) + { + int tgnargs; + char **tgargs; + Relation pk_rel; + RI_QueryKey qkey; + /* ! * Check for the correct # of call arguments */ + tgnargs = trigger->tgnargs; + tgargs = trigger->tgargs; + if (tgnargs < 4 || + tgnargs > RI_MAX_ARGUMENTS || + (tgnargs % 2) != 0) + ereport(ERROR, + (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), + errmsg("function \"%s\" called with wrong number of trigger arguments", + "RI_FKey_keyequal_upd"))); + + /* + * Nothing to do if no column names to compare given + */ + if (tgnargs == 4) + return true; + + if (!OidIsValid(trigger->tgconstrrelid)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("no target table given for trigger \"%s\" on table \"%s\"", + trigger->tgname, + RelationGetRelationName(fk_rel)), + errhint("Remove this referential integrity trigger and its mates, " + "then do ALTER TABLE ADD CONSTRAINT."))); + + pk_rel = heap_open(trigger->tgconstrrelid, AccessShareLock); + + switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO])) + { + case RI_MATCH_TYPE_UNSPECIFIED: + case RI_MATCH_TYPE_FULL: + ri_BuildQueryKeyFull(&qkey, trigger->tgoid, + RI_PLAN_KEYEQUAL_UPD, + fk_rel, pk_rel, + tgnargs, tgargs); + heap_close(pk_rel, AccessShareLock); + + /* Return if key's are equal */ + return ri_KeysEqual(fk_rel, old_row, new_row, &qkey, + RI_KEYPAIR_FK_IDX); + + /* Handle MATCH PARTIAL set null delete. */ + case RI_MATCH_TYPE_PARTIAL: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("MATCH PARTIAL not yet implemented"))); + break; + } + + /* Never reached */ elog(ERROR, "invalid match_type"); return false; } /* ---------- * RI_Initial_Check - * *************** *** 2871,2877 **** /* * Initialize the key and fill in type, oid's and number of keypairs */ ! memset((void *) key, 0, sizeof(RI_QueryKey)); key->constr_type = RI_MATCH_TYPE_FULL; key->constr_id = constr_id; key->constr_queryno = constr_queryno; --- 2906,2912 ---- /* * Initialize the key and fill in type, oid's and number of keypairs */ ! memset(key, 0, sizeof(RI_QueryKey)); key->constr_type = RI_MATCH_TYPE_FULL; key->constr_id = constr_id; key->constr_queryno = constr_queryno; *************** *** 3489,3495 **** for (i = 0; i < key->nkeypairs; i++) { /* ! * Get one attributes oldvalue. If it is NULL - they're not equal. */ oldvalue = SPI_getbinval(oldtup, rel->rd_att, key->keypair[i][pairidx], &isnull); --- 3524,3530 ---- for (i = 0; i < key->nkeypairs; i++) { /* ! * Get one attribute's oldvalue. If it is NULL - they're not equal. */ oldvalue = SPI_getbinval(oldtup, rel->rd_att, key->keypair[i][pairidx], &isnull); *************** *** 3497,3503 **** return false; /* ! * Get one attributes oldvalue. If it is NULL - they're not equal. */ newvalue = SPI_getbinval(newtup, rel->rd_att, key->keypair[i][pairidx], &isnull); --- 3532,3538 ---- return false; /* ! * Get one attribute's oldvalue. If it is NULL - they're not equal. */ newvalue = SPI_getbinval(newtup, rel->rd_att, key->keypair[i][pairidx], &isnull); *************** *** 3505,3511 **** return false; /* ! * Get the attributes type OID and call the '=' operator to * compare the values. */ typeid = SPI_gettypeid(rel->rd_att, key->keypair[i][pairidx]); --- 3540,3546 ---- return false; /* ! * Get the attribute's type OID and call the '=' operator to * compare the values. */ typeid = SPI_gettypeid(rel->rd_att, key->keypair[i][pairidx]); *************** *** 3644,3646 **** --- 3679,3710 ---- return DatumGetBool(FunctionCall2(&(typentry->eq_opr_finfo), oldvalue, newvalue)); } + + /* + * Given a trigger function OID, determine whether it is an RI trigger, + * and if so whether it is attached to PK or FK relation. + */ + int + RI_FKey_trigger_type(Oid tgfoid) + { + switch (tgfoid) + { + case F_RI_FKEY_CASCADE_DEL: + case F_RI_FKEY_CASCADE_UPD: + case F_RI_FKEY_RESTRICT_DEL: + case F_RI_FKEY_RESTRICT_UPD: + case F_RI_FKEY_SETNULL_DEL: + case F_RI_FKEY_SETNULL_UPD: + case F_RI_FKEY_SETDEFAULT_DEL: + case F_RI_FKEY_SETDEFAULT_UPD: + case F_RI_FKEY_NOACTION_DEL: + case F_RI_FKEY_NOACTION_UPD: + return RI_TRIGGER_PK; + + case F_RI_FKEY_CHECK_INS: + case F_RI_FKEY_CHECK_UPD: + return RI_TRIGGER_FK; + } + + return RI_TRIGGER_NONE; + } Index: src/include/commands/trigger.h =================================================================== RCS file: /var/lib/cvs/pgsql/src/include/commands/trigger.h,v retrieving revision 1.53 diff -c -r1.53 trigger.h *** src/include/commands/trigger.h 11 Apr 2005 19:51:15 -0000 1.53 --- src/include/commands/trigger.h 30 May 2005 02:48:37 -0000 *************** *** 168,176 **** /* * in utils/adt/ri_triggers.c */ ! extern bool RI_FKey_keyequal_upd(TriggerData *trigdata); extern bool RI_Initial_Check(FkConstraint *fkconstraint, Relation rel, Relation pkrel); #endif /* TRIGGER_H */ --- 168,185 ---- /* * in utils/adt/ri_triggers.c */ ! extern bool RI_FKey_keyequal_upd_pk(Trigger *trigger, Relation pk_rel, ! HeapTuple old_row, HeapTuple new_row); ! extern bool RI_FKey_keyequal_upd_fk(Trigger *trigger, Relation fk_rel, ! HeapTuple old_row, HeapTuple new_row); extern bool RI_Initial_Check(FkConstraint *fkconstraint, Relation rel, Relation pkrel); + #define RI_TRIGGER_PK 1 /* is a trigger on the PK relation */ + #define RI_TRIGGER_FK 2 /* is a trigger on the FK relation */ + #define RI_TRIGGER_NONE 0 /* is not an RI trigger function */ + + extern int RI_FKey_trigger_type(Oid tgfoid); + #endif /* TRIGGER_H */ Index: src/test/regress/expected/foreign_key.out =================================================================== RCS file: /var/lib/cvs/pgsql/src/test/regress/expected/foreign_key.out,v retrieving revision 1.38 diff -c -r1.38 foreign_key.out *** src/test/regress/expected/foreign_key.out 13 Oct 2004 01:22:31 -0000 1.38 --- src/test/regress/expected/foreign_key.out 30 May 2005 02:38:06 -0000 *************** *** 1061,1066 **** --- 1061,1068 ---- COMMIT; ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey" DETAIL: Key (fk)=(200) is not present in table "pktable". + DROP TABLE pktable, fktable CASCADE; + NOTICE: drop cascades to constraint fktable_fk_fkey on table fktable -- test notice about expensive referential integrity checks, -- where the index cannot be used because of type incompatibilities. CREATE TEMP TABLE pktable ( *************** *** 1128,1130 **** --- 1130,1174 ---- DETAIL: Key columns "x2" and "id1" are of different types: character varying and integer. WARNING: foreign key constraint "fk_241_132" will require costly sequential scans DETAIL: Key columns "x4" and "id3" are of different types: text and real. + DROP TABLE pktable, fktable CASCADE; + NOTICE: drop cascades to constraint fk_241_132 on table fktable + NOTICE: drop cascades to constraint fk_123_231 on table fktable + NOTICE: drop cascades to constraint fk_253_213 on table fktable + NOTICE: drop cascades to constraint fk_213_213 on table fktable + NOTICE: drop cascades to constraint fk_123_123 on table fktable + NOTICE: drop cascades to constraint fk_5_1 on table fktable + NOTICE: drop cascades to constraint fk_3_1 on table fktable + NOTICE: drop cascades to constraint fk_2_1 on table fktable + NOTICE: drop cascades to constraint fktable_x1_fkey on table fktable + NOTICE: drop cascades to constraint fk_4_2 on table fktable + NOTICE: drop cascades to constraint fk_1_2 on table fktable + NOTICE: drop cascades to constraint fktable_x2_fkey on table fktable + NOTICE: drop cascades to constraint fk_1_3 on table fktable + NOTICE: drop cascades to constraint fk_2_3 on table fktable + NOTICE: drop cascades to constraint fktable_x3_fkey on table fktable + -- test a tricky case: we can elide firing the FK check trigger during + -- an UPDATE if the UPDATE did not change the foreign key + -- field. However, we can't do this if our transaction was the one that + -- created the updated row and the trigger is deferred, since our UPDATE + -- will have invalidated the original newly-inserted tuple, and therefore + -- cause the on-INSERT RI trigger not to be fired. + CREATE TEMP TABLE pktable ( + id int primary key, + other int + ); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" + CREATE TEMP TABLE fktable ( + id int primary key, + fk int references pktable deferrable initially deferred + ); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable" + INSERT INTO pktable VALUES (5, 10); + BEGIN; + -- doesn't match PK, but no error yet + INSERT INTO fktable VALUES (0, 20); + -- don't change FK + UPDATE fktable SET id = id + 1; + -- should catch error from initial INSERT + COMMIT; + ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey" + DETAIL: Key (fk)=(20) is not present in table "pktable". Index: src/test/regress/sql/foreign_key.sql =================================================================== RCS file: /var/lib/cvs/pgsql/src/test/regress/sql/foreign_key.sql,v retrieving revision 1.15 diff -c -r1.15 foreign_key.sql *** src/test/regress/sql/foreign_key.sql 4 Aug 2004 21:34:34 -0000 1.15 --- src/test/regress/sql/foreign_key.sql 30 May 2005 02:38:06 -0000 *************** *** 705,710 **** --- 705,712 ---- -- error here on commit COMMIT; + DROP TABLE pktable, fktable CASCADE; + -- test notice about expensive referential integrity checks, -- where the index cannot be used because of type incompatibilities. *************** *** 774,776 **** --- 776,810 ---- ALTER TABLE fktable ADD CONSTRAINT fk_241_132 FOREIGN KEY (x2,x4,x1) REFERENCES pktable(id1,id3,id2); + + DROP TABLE pktable, fktable CASCADE; + + -- test a tricky case: we can elide firing the FK check trigger during + -- an UPDATE if the UPDATE did not change the foreign key + -- field. However, we can't do this if our transaction was the one that + -- created the updated row and the trigger is deferred, since our UPDATE + -- will have invalidated the original newly-inserted tuple, and therefore + -- cause the on-INSERT RI trigger not to be fired. + + CREATE TEMP TABLE pktable ( + id int primary key, + other int + ); + + CREATE TEMP TABLE fktable ( + id int primary key, + fk int references pktable deferrable initially deferred + ); + + INSERT INTO pktable VALUES (5, 10); + + BEGIN; + + -- doesn't match PK, but no error yet + INSERT INTO fktable VALUES (0, 20); + + -- don't change FK + UPDATE fktable SET id = id + 1; + + -- should catch error from initial INSERT + COMMIT;