skip FK trigger on UPDATE - Mailing list pgsql-patches
| From | Neil Conway |
|---|---|
| Subject | skip FK trigger on UPDATE |
| Date | |
| Msg-id | 4299C345.6060705@samurai.com Whole thread Raw |
| Responses |
Re: skip FK trigger on UPDATE
|
| List | pgsql-patches |
This patch implements an idea discussed on -hackers recently: if an
UPDATE on a table with a foreign key does not modify any of the table's
foreign key columns, we can avoid enqueueing the foreign queue check
trigger.
I basically just moved the logic for the "are the keys equal?" test from
the FK trigger itself into the code that enqueues the trigger. I then
removed the keys-are-equal check from the FK trigger. I also had to
change (somewhat awkwardly) RI_FKey_keyequal_upd() to work for updates
on either the PK table or the FK table. I also removed the bogus
TriggerData argument from RI_FKey_keyequal_upd(), since AFAICS it is no
needed and merely adds confusion.
This patch does cause one change to the regression test output:
*** ./expected/foreign_key.out Fri May 27 23:58:54 2005
--- ./results/foreign_key.out Sat May 28 00:46:20 2005
***************
*** 911,918 ****
DETAIL: Key (base1,ptest1)=(2,2) is still referenced from table
"pktable".
-- fails (1,1) is being referenced (twice)
update pktable set base1=3 where base1=1;
! ERROR: insert or update on table "pktable" violates foreign key
constraint "pktable_base2_fkey"
! DETAIL: Key (base2,ptest2)=(1,1) is not present in table "pktable".
-- this sequence of two deletes will work, since after the first
there will be no (2,*) references
delete from pktable where base2=2;
delete from pktable where base1=2;
--- 911,918 ----
DETAIL: Key (base1,ptest1)=(2,2) is still referenced from table
"pktable".
-- fails (1,1) is being referenced (twice)
update pktable set base1=3 where base1=1;
! ERROR: update or delete on "pktable" violates foreign key constraint
"pktable_base2_fkey" on "pktable"
! DETAIL: Key (base1,ptest1)=(1,1) is still referenced from table
"pktable".
-- this sequence of two deletes will work, since after the first
there will be no (2,*) references
delete from pktable where base2=2;
delete from pktable where base1=2;
I found this a bit strange: on the one hand I think the new error
message is actually more sensible, but I'm not sure what caused the
change in behavior. I'll have more of a think about this tomorrow.
-Neil
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /Users/neilc/local/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 27 May 2005 13:58:54 -0000
***************
*** 1596,1603 ****
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;
}
--- 1596,1602 ----
case F_RI_FKEY_NOACTION_UPD:
return RI_TRIGGER_PK;
! case F_RI_FKEY_CHECK:
return RI_TRIGGER_FK;
}
***************
*** 4305,4313 ****
return;
/*
! * Scan through each tuple, calling RI_FKey_check_ins (insert trigger)
! * as if that tuple had just been inserted. If any of those fail, it
! * should ereport(ERROR) and that's that.
*/
MemSet(&trig, 0, sizeof(trig));
trig.tgoid = InvalidOid;
--- 4304,4312 ----
return;
/*
! * Scan through each tuple, calling RI_FKey_check (insert trigger)
! * as if that tuple had just been inserted. If any of those fail,
! * it should ereport(ERROR) and that's that.
*/
MemSet(&trig, 0, sizeof(trig));
trig.tgoid = InvalidOid;
***************
*** 4359,4365 ****
MemSet(&fcinfo, 0, sizeof(fcinfo));
/*
! * We assume RI_FKey_check_ins won't look at flinfo...
*/
trigdata.type = T_TriggerData;
trigdata.tg_event = TRIGGER_EVENT_INSERT | TRIGGER_EVENT_ROW;
--- 4358,4364 ----
MemSet(&fcinfo, 0, sizeof(fcinfo));
/*
! * We assume RI_FKey_check won't look at flinfo...
*/
trigdata.type = T_TriggerData;
trigdata.tg_event = TRIGGER_EVENT_INSERT | TRIGGER_EVENT_ROW;
***************
*** 4372,4378 ****
fcinfo.context = (Node *) &trigdata;
! RI_FKey_check_ins(&fcinfo);
}
heap_endscan(scan);
--- 4371,4377 ----
fcinfo.context = (Node *) &trigdata;
! RI_FKey_check(&fcinfo);
}
heap_endscan(scan);
***************
*** 4420,4426 ****
fk_trigger = makeNode(CreateTrigStmt);
fk_trigger->trigname = fkconstraint->constr_name;
fk_trigger->relation = myRel;
! fk_trigger->funcname = SystemFuncName("RI_FKey_check_ins");
fk_trigger->before = false;
fk_trigger->row = true;
fk_trigger->actions[0] = 'i';
--- 4419,4425 ----
fk_trigger = makeNode(CreateTrigStmt);
fk_trigger->trigname = fkconstraint->constr_name;
fk_trigger->relation = myRel;
! fk_trigger->funcname = SystemFuncName("RI_FKey_check");
fk_trigger->before = false;
fk_trigger->row = true;
fk_trigger->actions[0] = 'i';
Index: src/backend/commands/trigger.c
===================================================================
RCS file: /Users/neilc/local/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 29 May 2005 13:10:25 -0000
***************
*** 111,117 ****
bool needconstrrelid = false;
void *elem = NULL;
! if (strncmp(strVal(lfirst(list_tail((stmt->funcname)))), "RI_FKey_check_", 14) == 0)
{
/* A trigger on FK table. */
needconstrrelid = true;
--- 111,117 ----
bool needconstrrelid = false;
void *elem = NULL;
! if (strncmp(strVal(lfirst(list_tail((stmt->funcname)))), "RI_FKey_check", 13) == 0)
{
/* A trigger on FK table. */
needconstrrelid = true;
***************
*** 2994,3009 ****
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:
--- 2994,3010 ----
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)
{
! bool is_ri_trigger = false;
switch (trigger->tgfoid)
{
+ case F_RI_FKEY_CHECK:
case F_RI_FKEY_NOACTION_UPD:
case F_RI_FKEY_CASCADE_UPD:
case F_RI_FKEY_RESTRICT_UPD:
***************
*** 3011,3043 ****
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;
--- 3012,3035 ----
case F_RI_FKEY_SETDEFAULT_UPD:
is_ri_trigger = true;
break;
}
+ /*
+ * 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 (trigger->tgfoid == F_RI_FKEY_CHECK &&
+ HeapTupleHeaderGetXmin(oldtup->t_data) == GetCurrentTransactionId())
+ is_ri_trigger = false;
+
if (is_ri_trigger)
{
! if (RI_FKey_keyequal_upd(trigger, rel, oldtup, newtup))
{
/* key unchanged, so skip queuing this event */
continue;
Index: src/backend/utils/adt/ri_triggers.c
===================================================================
RCS file: /Users/neilc/local/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 29 May 2005 13:22:09 -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"
***************
*** 176,182 ****
* Check foreign key existence (combined for INSERT and UPDATE).
* ----------
*/
! static Datum
RI_FKey_check(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
--- 177,183 ----
* Check foreign key existence (combined for INSERT and UPDATE).
* ----------
*/
! Datum
RI_FKey_check(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
***************
*** 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 ----
***************
*** 455,486 ****
/* ----------
- * RI_FKey_check_ins -
- *
- * Check foreign key existence at insert event on FK table.
- * ----------
- */
- Datum
- RI_FKey_check_ins(PG_FUNCTION_ARGS)
- {
- return RI_FKey_check(fcinfo);
- }
-
-
- /* ----------
- * RI_FKey_check_upd -
- *
- * Check foreign key existence at update event on FK table.
- * ----------
- */
- Datum
- RI_FKey_check_upd(PG_FUNCTION_ARGS)
- {
- return RI_FKey_check(fcinfo);
- }
-
-
- /* ----------
* ri_Check_Pk_Match
*
* Check for matching value of old pk row in current state for
--- 440,445 ----
***************
*** 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);
--- 1964,1971 ----
* 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);
--- 1975,1981 ----
qualsep, attname, i + 1);
qualsep = "AND";
queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
! qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
}
strcat(querystr, qualstr);
***************
*** 2453,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)
--- 2412,2437 ----
/* ----------
* RI_FKey_keyequal_upd -
*
! * Check if we have a key change on update. It is used by the AFTER
* trigger queue manager to detect "triggered data change violation".
* ----------
*/
bool
! RI_FKey_keyequal_upd(Trigger *trigger, Relation rel, HeapTuple old_row, HeapTuple new_row)
{
int tgnargs;
char **tgargs;
Relation fk_rel;
Relation pk_rel;
RI_QueryKey qkey;
+ bool fk_update;
+ bool result;
/*
* Check for the correct # of call arguments
*/
! tgnargs = trigger->tgnargs;
! tgargs = trigger->tgargs;
if (tgnargs < 4 ||
tgnargs > RI_MAX_ARGUMENTS ||
(tgnargs % 2) != 0)
***************
*** 2490,2548 ****
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 <UNSPECIFIED>
! */
! 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),
! errmsg("MATCH PARTIAL not yet implemented")));
! break;
! }
/*
! * Never reached
*/
! elog(ERROR, "invalid match_type");
! return false;
}
--- 2447,2499 ----
return true;
/*
! * This can be invoked for both UPDATEs to a PK table or an FK
! * table. "rel" always refers to the relation on which the UPDATE
! * occurred.
*/
! 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(rel)),
! errhint("Remove this referential integrity trigger and its mates, "
! "then do ALTER TABLE ADD CONSTRAINT.")));
!
! if (trigger->tgfoid == F_RI_FKEY_CHECK)
! {
! /* Update on FK table */
! fk_update = true;
! fk_rel = rel;
! pk_rel = heap_open(trigger->tgconstrrelid, AccessShareLock);
! }
! else
{
! /* Update on PK table */
! fk_update = false;
! pk_rel = rel;
! fk_rel = heap_open(trigger->tgconstrrelid, AccessShareLock);
! }
! if (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]) == RI_MATCH_TYPE_PARTIAL)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("MATCH PARTIAL not yet implemented")));
! /* MATCH <UNSPECIFIED> and MATCH <FULL> */
! ri_BuildQueryKeyFull(&qkey, trigger->tgoid, RI_PLAN_KEYEQUAL_UPD,
! fk_rel, pk_rel, tgnargs, tgargs);
/*
! * Return if keys are equal
*/
! result = ri_KeysEqual(rel, old_row, new_row, &qkey,
! fk_update ? RI_KEYPAIR_FK_IDX : RI_KEYPAIR_PK_IDX);
! if (fk_update)
! heap_close(pk_rel, AccessShareLock);
! else
! heap_close(fk_rel, AccessShareLock);
! return result;
}
***************
*** 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;
--- 2822,2828 ----
/*
* 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);
--- 3440,3446 ----
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);
--- 3448,3454 ----
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]);
--- 3456,3462 ----
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]);
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.363
diff -c -r1.363 pg_proc.h
*** src/include/catalog/pg_proc.h 20 May 2005 01:29:55 -0000 1.363
--- src/include/catalog/pg_proc.h 27 May 2005 13:58:54 -0000
***************
*** 2261,2269 ****
/* Generic referential integrity constraint triggers */
! DATA(insert OID = 1644 ( RI_FKey_check_ins PGNSP PGUID 12 f f t f v 0 2279 "" _null_ _null_ _null_
RI_FKey_check_ins- _null_ ));
! DESCR("referential integrity FOREIGN KEY ... REFERENCES");
! DATA(insert OID = 1645 ( RI_FKey_check_upd PGNSP PGUID 12 f f t f v 0 2279 "" _null_ _null_ _null_
RI_FKey_check_upd- _null_ ));
DESCR("referential integrity FOREIGN KEY ... REFERENCES");
DATA(insert OID = 1646 ( RI_FKey_cascade_del PGNSP PGUID 12 f f t f v 0 2279 "" _null_ _null_ _null_
RI_FKey_cascade_del- _null_ ));
DESCR("referential integrity ON DELETE CASCADE");
--- 2261,2267 ----
/* Generic referential integrity constraint triggers */
! DATA(insert OID = 1644 ( RI_FKey_check PGNSP PGUID 12 f f t f v 0 2279 "" _null_ _null_ _null_
RI_FKey_check- _null_ ));
DESCR("referential integrity FOREIGN KEY ... REFERENCES");
DATA(insert OID = 1646 ( RI_FKey_cascade_del PGNSP PGUID 12 f f t f v 0 2279 "" _null_ _null_ _null_
RI_FKey_cascade_del- _null_ ));
DESCR("referential integrity ON DELETE CASCADE");
Index: src/include/commands/trigger.h
===================================================================
RCS file: /Users/neilc/local/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 27 May 2005 13:58:54 -0000
***************
*** 168,174 ****
/*
* 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);
--- 168,175 ----
/*
* in utils/adt/ri_triggers.c
*/
! extern bool RI_FKey_keyequal_upd(Trigger *trigger, Relation rel,
! HeapTuple old_row, HeapTuple new_row);
extern bool RI_Initial_Check(FkConstraint *fkconstraint,
Relation rel,
Relation pkrel);
Index: src/include/utils/builtins.h
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.257
diff -c -r1.257 builtins.h
*** src/include/utils/builtins.h 27 May 2005 00:57:49 -0000 1.257
--- src/include/utils/builtins.h 27 May 2005 13:58:54 -0000
***************
*** 782,789 ****
extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
/* ri_triggers.c */
! extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
! extern Datum RI_FKey_check_upd(PG_FUNCTION_ARGS);
extern Datum RI_FKey_noaction_del(PG_FUNCTION_ARGS);
extern Datum RI_FKey_noaction_upd(PG_FUNCTION_ARGS);
extern Datum RI_FKey_cascade_del(PG_FUNCTION_ARGS);
--- 782,788 ----
extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
/* ri_triggers.c */
! extern Datum RI_FKey_check(PG_FUNCTION_ARGS);
extern Datum RI_FKey_noaction_del(PG_FUNCTION_ARGS);
extern Datum RI_FKey_noaction_upd(PG_FUNCTION_ARGS);
extern Datum RI_FKey_cascade_del(PG_FUNCTION_ARGS);
Index: src/test/regress/expected/foreign_key.out
===================================================================
RCS file: /Users/neilc/local/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 27 May 2005 13:58:54 -0000
***************
*** 911,918 ****
DETAIL: Key (base1,ptest1)=(2,2) is still referenced from table "pktable".
-- fails (1,1) is being referenced (twice)
update pktable set base1=3 where base1=1;
! ERROR: update or delete on "pktable" violates foreign key constraint "pktable_base2_fkey" on "pktable"
! DETAIL: Key (base1,ptest1)=(1,1) is still referenced from table "pktable".
-- this sequence of two deletes will work, since after the first there will be no (2,*) references
delete from pktable where base2=2;
delete from pktable where base1=2;
--- 911,918 ----
DETAIL: Key (base1,ptest1)=(2,2) is still referenced from table "pktable".
-- fails (1,1) is being referenced (twice)
update pktable set base1=3 where base1=1;
! ERROR: insert or update on table "pktable" violates foreign key constraint "pktable_base2_fkey"
! DETAIL: Key (base2,ptest2)=(1,1) is not present in table "pktable".
-- this sequence of two deletes will work, since after the first there will be no (2,*) references
delete from pktable where base2=2;
delete from pktable where base1=2;
***************
*** 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: /Users/neilc/local/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 27 May 2005 13:58:54 -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;
pgsql-patches by date: