From 6fdaf679c3bc72f4b680d539f07790f3fa3d2129 Mon Sep 17 00:00:00 2001 From: amitlan Date: Fri, 13 Nov 2020 18:24:48 +0900 Subject: [PATCH v1 2/2] Enforce foreign key correctly during cross-partition updates When an update on a partitioned primary key table referenced in a foreign key constraint causes a row to move from one partition to another, instead of firing an UPDATE after-trigger event, DELETE and INSERT events are fired on the source and the destination leaf partition, respectively, which can result in pretty surprising outcomes. To be fair, it would be wrong to fire the UPDATE event on the source leaf partition itself, because the new row is not inserted into it. This commit teaches trigger.c to skip queuing the aforementioned DELETE and INSERT events on the leaf partitions in favor of an UPDATE event fired on the "root" target relation, which makes sense because both the old and new tuple "logically" belong to it. To make that possible, this adjusts AFTER trigger data strucutures to allow queuing and firing events containing partitioned table's tuples. Given that partitioned tables are only logical relations, meaning that its tuples have no physical identifiers, the only way to remember the event tuples seems to be to store them in a tuplestore, similar to what is currently done for foreign tables. The implementation currently has a limitation that only the foreign keys pointing into the query's target relation are considered, not those of its sub-partitioned partitions. The use case of distinct foreign keys pointing into sub-partitioned partitions, but not into the root partitioned table is perhaps of minor importance. --- src/backend/commands/trigger.c | 162 +++++++++++++++-------- src/backend/executor/execMain.c | 9 ++ src/backend/executor/execReplication.c | 4 +- src/backend/executor/nodeModifyTable.c | 212 ++++++++++++++++++++++++++++-- src/backend/utils/adt/ri_triggers.c | 17 ++- src/include/commands/trigger.h | 2 + src/include/nodes/execnodes.h | 4 + src/test/regress/expected/foreign_key.out | 146 +++++++++++++++++++- src/test/regress/sql/foreign_key.sql | 84 ++++++++++++ 9 files changed, 569 insertions(+), 71 deletions(-) diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index 7b2d0de..58277be 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -98,7 +98,9 @@ static HeapTuple ExecCallTriggerFunc(TriggerData *trigdata, FmgrInfo *finfo, Instrumentation *instr, MemoryContext per_tuple_context); -static void AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, +static void AfterTriggerSaveEvent(EState *estate, + ModifyTableState *mtstate, + ResultRelInfo *relinfo, int event, bool row_trigger, TupleTableSlot *oldtup, TupleTableSlot *newtup, List *recheckIndexes, Bitmapset *modifiedCols, @@ -2317,7 +2319,7 @@ ExecASInsertTriggers(EState *estate, ResultRelInfo *relinfo, TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->trig_insert_after_statement) - AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_INSERT, + AfterTriggerSaveEvent(estate, NULL, relinfo, TRIGGER_EVENT_INSERT, false, NULL, NULL, NIL, NULL, transition_capture); } @@ -2406,7 +2408,7 @@ ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo, if ((trigdesc && trigdesc->trig_insert_after_row) || (transition_capture && transition_capture->tcs_insert_new_table)) - AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_INSERT, + AfterTriggerSaveEvent(estate, NULL, relinfo, TRIGGER_EVENT_INSERT, true, NULL, slot, recheckIndexes, NULL, transition_capture); @@ -2531,7 +2533,7 @@ ExecASDeleteTriggers(EState *estate, ResultRelInfo *relinfo, TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->trig_delete_after_statement) - AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_DELETE, + AfterTriggerSaveEvent(estate, NULL, relinfo, TRIGGER_EVENT_DELETE, false, NULL, NULL, NIL, NULL, transition_capture); } @@ -2628,7 +2630,8 @@ ExecBRDeleteTriggers(EState *estate, EPQState *epqstate, } void -ExecARDeleteTriggers(EState *estate, ResultRelInfo *relinfo, +ExecARDeleteTriggers(EState *estate, ModifyTableState *mtstate, + ResultRelInfo *relinfo, ItemPointer tupleid, HeapTuple fdw_trigtuple, TransitionCaptureState *transition_capture) @@ -2651,7 +2654,7 @@ ExecARDeleteTriggers(EState *estate, ResultRelInfo *relinfo, else ExecForceStoreHeapTuple(fdw_trigtuple, slot, false); - AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_DELETE, + AfterTriggerSaveEvent(estate, mtstate, relinfo, TRIGGER_EVENT_DELETE, true, slot, NULL, NIL, NULL, transition_capture); } @@ -2766,7 +2769,7 @@ ExecASUpdateTriggers(EState *estate, ResultRelInfo *relinfo, TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->trig_update_after_statement) - AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_UPDATE, + AfterTriggerSaveEvent(estate, NULL, relinfo, TRIGGER_EVENT_UPDATE, false, NULL, NULL, NIL, GetAllUpdatedColumns(relinfo, estate), transition_capture); @@ -2913,7 +2916,8 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate, } void -ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo, +ExecARUpdateTriggers(EState *estate, ModifyTableState *mtstate, + ResultRelInfo *relinfo, ItemPointer tupleid, HeapTuple fdw_trigtuple, TupleTableSlot *newslot, @@ -2947,7 +2951,7 @@ ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo, else if (fdw_trigtuple != NULL) ExecForceStoreHeapTuple(fdw_trigtuple, oldslot, false); - AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_UPDATE, + AfterTriggerSaveEvent(estate, mtstate, relinfo, TRIGGER_EVENT_UPDATE, true, oldslot, newslot, recheckIndexes, GetAllUpdatedColumns(relinfo, estate), transition_capture); @@ -3073,7 +3077,7 @@ ExecASTruncateTriggers(EState *estate, ResultRelInfo *relinfo) TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->trig_truncate_after_statement) - AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_TRUNCATE, + AfterTriggerSaveEvent(estate, NULL, relinfo, TRIGGER_EVENT_TRUNCATE, false, NULL, NULL, NIL, NULL, NULL); } @@ -3361,19 +3365,21 @@ typedef SetConstraintStateData *SetConstraintState; * * For row-level triggers, we arrange not to waste storage on unneeded ctid * fields. Updates of regular tables use two; inserts and deletes of regular - * tables use one; foreign tables always use zero and save the tuple(s) to a - * tuplestore. AFTER_TRIGGER_FDW_FETCH directs AfterTriggerExecute() to - * retrieve a fresh tuple or pair of tuples from that tuplestore, while - * AFTER_TRIGGER_FDW_REUSE directs it to use the most-recently-retrieved - * tuple(s). This permits storing tuples once regardless of the number of - * row-level triggers on a foreign table. + * tables use one; foreign or partitioned tables always use zero and save the + * tuple(s) to a tuplestore. AFTER_TRIGGER_TS_FETCH directs + * AfterTriggerExecute() to retrieve a fresh tuple or pair of tuples from that + * tuplestore, while AFTER_TRIGGER_TS_REUSE directs it to use the + * most-recently-retrieved tuple(s). This permits storing tuples once + * regardless of the number of row-level triggers on a foreign or partitioned + * table. * - * Note that we need triggers on foreign tables to be fired in exactly the - * order they were queued, so that the tuples come out of the tuplestore in - * the right order. To ensure that, we forbid deferrable (constraint) - * triggers on foreign tables. This also ensures that such triggers do not - * get deferred into outer trigger query levels, meaning that it's okay to - * destroy the tuplestore at the end of the query level. + * Note that we need triggers on foreign and partitioned tables to be fired in + * exactly the order they were queued, so that the tuples come out of the + * tuplestore in the right order. To ensure that, we forbid deferrable + * (constraint) triggers on foreign tables. For partitioned tables, we never + * queue any events for its deferred triggers. This also ensures that such + * triggers do not get deferred into outer trigger query levels, meaning that + * it's okay to destroy the tuplestore at the end of the query level. * * Statement-level triggers always bear AFTER_TRIGGER_1CTID, though they * require no ctid field. We lack the flag bit space to neatly represent that @@ -3394,8 +3400,8 @@ typedef uint32 TriggerFlags; #define AFTER_TRIGGER_DONE 0x10000000 #define AFTER_TRIGGER_IN_PROGRESS 0x20000000 /* bits describing the size and tuple sources of this event */ -#define AFTER_TRIGGER_FDW_REUSE 0x00000000 -#define AFTER_TRIGGER_FDW_FETCH 0x80000000 +#define AFTER_TRIGGER_TS_REUSE 0x00000000 +#define AFTER_TRIGGER_TS_FETCH 0x80000000 #define AFTER_TRIGGER_1CTID 0x40000000 #define AFTER_TRIGGER_2CTID 0xC0000000 #define AFTER_TRIGGER_TUP_BITS 0xC0000000 @@ -3589,7 +3595,8 @@ typedef struct AfterTriggersData struct AfterTriggersQueryData { AfterTriggerEventList events; /* events pending from this query */ - Tuplestorestate *fdw_tuplestore; /* foreign tuples for said events */ + Tuplestorestate *tuplestore; /* foreign or partitioned table tuples for + * said events */ List *tables; /* list of AfterTriggersTableData, see below */ }; @@ -3638,15 +3645,15 @@ static void cancel_prior_stmt_triggers(Oid relid, CmdType cmdType, int tgevent); /* - * Get the FDW tuplestore for the current trigger query level, creating it + * Get the tuplestore for the current trigger query level, creating it * if necessary. */ static Tuplestorestate * -GetCurrentFDWTuplestore(void) +GetCurrentAfterTriggerTuplestore(void) { Tuplestorestate *ret; - ret = afterTriggers.query_stack[afterTriggers.query_depth].fdw_tuplestore; + ret = afterTriggers.query_stack[afterTriggers.query_depth].tuplestore; if (ret == NULL) { MemoryContext oldcxt; @@ -3665,7 +3672,7 @@ GetCurrentFDWTuplestore(void) CurrentResourceOwner = saveResourceOwner; MemoryContextSwitchTo(oldcxt); - afterTriggers.query_stack[afterTriggers.query_depth].fdw_tuplestore = ret; + afterTriggers.query_stack[afterTriggers.query_depth].tuplestore = ret; } return ret; @@ -3999,22 +4006,22 @@ AfterTriggerExecute(EState *estate, */ switch (event->ate_flags & AFTER_TRIGGER_TUP_BITS) { - case AFTER_TRIGGER_FDW_FETCH: + case AFTER_TRIGGER_TS_FETCH: { - Tuplestorestate *fdw_tuplestore = GetCurrentFDWTuplestore(); + Tuplestorestate *tuplestore = GetCurrentAfterTriggerTuplestore(); - if (!tuplestore_gettupleslot(fdw_tuplestore, true, false, + if (!tuplestore_gettupleslot(tuplestore, true, false, trig_tuple_slot1)) elog(ERROR, "failed to fetch tuple1 for AFTER trigger"); if ((evtshared->ats_event & TRIGGER_EVENT_OPMASK) == TRIGGER_EVENT_UPDATE && - !tuplestore_gettupleslot(fdw_tuplestore, true, false, + !tuplestore_gettupleslot(tuplestore, true, false, trig_tuple_slot2)) elog(ERROR, "failed to fetch tuple2 for AFTER trigger"); } /* fall through */ - case AFTER_TRIGGER_FDW_REUSE: + case AFTER_TRIGGER_TS_REUSE: /* * Store tuple in the slot so that tg_trigtuple does not reference @@ -4315,7 +4322,8 @@ afterTriggerInvokeEvents(AfterTriggerEventList *events, ExecDropSingleTupleTableSlot(slot2); slot1 = slot2 = NULL; } - if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) + if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE || + rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) { slot1 = MakeSingleTupleTableSlot(rel->rd_att, &TTSOpsMinimalTuple); @@ -4699,8 +4707,8 @@ AfterTriggerFreeQuery(AfterTriggersQueryData *qs) afterTriggerFreeEventList(&qs->events); /* Drop FDW tuplestore if any */ - ts = qs->fdw_tuplestore; - qs->fdw_tuplestore = NULL; + ts = qs->tuplestore; + qs->tuplestore = NULL; if (ts) tuplestore_end(ts); @@ -5032,7 +5040,7 @@ AfterTriggerEnlargeQueryState(void) qs->events.head = NULL; qs->events.tail = NULL; qs->events.tailfree = NULL; - qs->fdw_tuplestore = NULL; + qs->tuplestore = NULL; qs->tables = NIL; ++init_depth; @@ -5503,7 +5511,8 @@ AfterTriggerPendingOnRel(Oid relid) * ---------- */ static void -AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, +AfterTriggerSaveEvent(EState *estate, ModifyTableState *mtstate, + ResultRelInfo *relinfo, int event, bool row_trigger, TupleTableSlot *oldslot, TupleTableSlot *newslot, List *recheckIndexes, Bitmapset *modifiedCols, @@ -5517,7 +5526,7 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, int tgtype_event; int tgtype_level; int i; - Tuplestorestate *fdw_tuplestore = NULL; + Tuplestorestate *tuplestore = NULL; /* * Check state. We use a normal test not Assert because it is possible to @@ -5715,7 +5724,9 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, break; } - if (!(relkind == RELKIND_FOREIGN_TABLE && row_trigger)) + if (!row_trigger || + (relkind != RELKIND_FOREIGN_TABLE && + relkind != RELKIND_PARTITIONED_TABLE)) new_event.ate_flags = (row_trigger && event == TRIGGER_EVENT_UPDATE) ? AFTER_TRIGGER_2CTID : AFTER_TRIGGER_1CTID; /* else, we'll initialize ate_flags for each trigger */ @@ -5735,16 +5746,62 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, modifiedCols, oldslot, newslot)) continue; - if (relkind == RELKIND_FOREIGN_TABLE && row_trigger) + /* + * Some events fired during the UPDATEs of partitioned tables that + * are turned into DELETE+INSERT must be skipped. + */ + if (mtstate && mtstate->operation == CMD_UPDATE && + mtstate->rootResultRelInfo->ri_RelationDesc->rd_rel->relkind == + RELKIND_PARTITIONED_TABLE) + { + switch (RI_FKey_trigger_type(trigger->tgfoid)) + { + /* + * For UPDATEs of partitioned PK table, skip the events fired + * by the DELETEs unless the constraint originates in the + * relation on which it is fired (!tgisclone), because the + * UPDATE event fired on the root (partitioned) target table + * will be queued instead. + */ + case RI_TRIGGER_PK: + if (TRIGGER_FIRED_BY_DELETE(event) && trigger->tgisclone) + continue; + break; + + /* + * Skip events on the root partitione table if: 1) it's the FK + * table, because the events fired on the destination leaf + * partition suffice to do the checks necessary to enforce + * the FK relationship, 2) the trigger is unrelated to foreign + * keys, because the instance of the trigger in the leaf + * partitions will be fired instead. In fact, proceeding with + * firing the event on the partitioned table can be unsafe in + * both cases. For (1), RI_FKey_check() can't handle being + * handed a partitioned table. For (2), the trigger may be + * a INITIALLY DEFERRED constraint trigger, for which we + * can't ensure the event's tuples will be accessible when + * the trigger is fired. + */ + case RI_TRIGGER_FK: + case RI_TRIGGER_NONE: + if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) + continue; + break; + } + } + + if (row_trigger && + (relkind == RELKIND_FOREIGN_TABLE || + relkind == RELKIND_PARTITIONED_TABLE)) { - if (fdw_tuplestore == NULL) + if (tuplestore == NULL) { - fdw_tuplestore = GetCurrentFDWTuplestore(); - new_event.ate_flags = AFTER_TRIGGER_FDW_FETCH; + tuplestore = GetCurrentAfterTriggerTuplestore(); + new_event.ate_flags = AFTER_TRIGGER_TS_FETCH; } else /* subsequent event for the same tuple */ - new_event.ate_flags = AFTER_TRIGGER_FDW_REUSE; + new_event.ate_flags = AFTER_TRIGGER_TS_REUSE; } /* @@ -5818,16 +5875,17 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, } /* - * Finally, spool any foreign tuple(s). The tuplestore squashes them to - * minimal tuples, so this loses any system columns. The executor lost - * those columns before us, for an unrelated reason, so this is fine. + * Finally, spool any foreign or partitioned table tuple(s). The + * tuplestore squashes them to minimal tuples, so this loses any system + * columns. The executor lost those columns before us, for an unrelated + * reason, so this is fine. */ - if (fdw_tuplestore) + if (tuplestore) { if (oldslot != NULL) - tuplestore_puttupleslot(fdw_tuplestore, oldslot); + tuplestore_puttupleslot(tuplestore, oldslot); if (newslot != NULL) - tuplestore_puttupleslot(fdw_tuplestore, newslot); + tuplestore_puttupleslot(tuplestore, newslot); } } diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 7179f58..14f7680 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -1438,8 +1438,17 @@ ExecCloseResultRelations(EState *estate) foreach(l, estate->es_opened_result_relations) { ResultRelInfo *resultRelInfo = lfirst(l); + ListCell *lc; ExecCloseIndices(resultRelInfo); + foreach(lc, resultRelInfo->ri_ancestorResultRels) + { + ResultRelInfo *rInfo = lfirst(lc); + + /* Only close those we opened in GetAncestorResultRels(). */ + if (rInfo->ri_RangeTableIndex == 0) + table_close(rInfo->ri_RelationDesc, NoLock); + } } /* Close any relations that have been opened by ExecGetTriggerResultRel(). */ diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c index 01d2688..3f53754 100644 --- a/src/backend/executor/execReplication.c +++ b/src/backend/executor/execReplication.c @@ -516,7 +516,7 @@ ExecSimpleRelationUpdate(ResultRelInfo *resultRelInfo, NIL); /* AFTER ROW UPDATE Triggers */ - ExecARUpdateTriggers(estate, resultRelInfo, + ExecARUpdateTriggers(estate, NULL, resultRelInfo, tid, NULL, slot, recheckIndexes, NULL); @@ -556,7 +556,7 @@ ExecSimpleRelationDelete(ResultRelInfo *resultRelInfo, simple_table_tuple_delete(rel, tid, estate->es_snapshot); /* AFTER ROW DELETE Triggers */ - ExecARDeleteTriggers(estate, resultRelInfo, + ExecARDeleteTriggers(estate, NULL, resultRelInfo, tid, NULL, NULL); } } diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 29e07b7..eb695d7 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -42,6 +42,7 @@ #include "access/tableam.h" #include "access/xact.h" #include "catalog/catalog.h" +#include "catalog/partition.h" #include "commands/trigger.h" #include "executor/execPartition.h" #include "executor/executor.h" @@ -380,7 +381,9 @@ ExecInsert(ModifyTableState *mtstate, TupleTableSlot *slot, TupleTableSlot *planSlot, EState *estate, - bool canSetTag) + bool canSetTag, + TupleTableSlot **inserted_tuple, + ResultRelInfo **insert_destrel) { Relation resultRelationDesc; List *recheckIndexes = NIL; @@ -660,7 +663,7 @@ ExecInsert(ModifyTableState *mtstate, if (mtstate->operation == CMD_UPDATE && mtstate->mt_transition_capture && mtstate->mt_transition_capture->tcs_update_new_table) { - ExecARUpdateTriggers(estate, resultRelInfo, NULL, + ExecARUpdateTriggers(estate, mtstate, resultRelInfo, NULL, NULL, slot, NULL, @@ -698,6 +701,11 @@ ExecInsert(ModifyTableState *mtstate, if (resultRelInfo->ri_projectReturning) result = ExecProcessReturning(resultRelInfo, slot, planSlot); + if (inserted_tuple) + *inserted_tuple = slot; + if (insert_destrel) + *insert_destrel = resultRelInfo; + return result; } @@ -992,7 +1000,7 @@ ldelete:; if (mtstate->operation == CMD_UPDATE && mtstate->mt_transition_capture && mtstate->mt_transition_capture->tcs_update_old_table) { - ExecARUpdateTriggers(estate, resultRelInfo, + ExecARUpdateTriggers(estate, mtstate, resultRelInfo, tupleid, oldtuple, NULL, @@ -1007,7 +1015,7 @@ ldelete:; } /* AFTER ROW DELETE Triggers */ - ExecARDeleteTriggers(estate, resultRelInfo, tupleid, oldtuple, + ExecARDeleteTriggers(estate, mtstate, resultRelInfo, tupleid, oldtuple, ar_delete_trig_tcs); /* Process RETURNING if present and if requested */ @@ -1079,7 +1087,9 @@ ExecCrossPartitionUpdate(ModifyTableState *mtstate, TupleTableSlot *slot, TupleTableSlot *planSlot, EPQState *epqstate, bool canSetTag, TupleTableSlot **retry_slot, - TupleTableSlot **inserted_tuple) + TupleTableSlot **returning_slot, + TupleTableSlot **inserted_tuple, + ResultRelInfo **insert_destrel) { EState *estate = mtstate->ps.state; PartitionTupleRouting *proute = mtstate->mt_partition_tuple_routing; @@ -1168,8 +1178,9 @@ ExecCrossPartitionUpdate(ModifyTableState *mtstate, mtstate->mt_root_tuple_slot); /* Tuple routing starts from the root table. */ - *inserted_tuple = ExecInsert(mtstate, mtstate->rootResultRelInfo, slot, - planSlot, estate, canSetTag); + *returning_slot = ExecInsert(mtstate, mtstate->rootResultRelInfo, slot, + planSlot, estate, canSetTag, inserted_tuple, + insert_destrel); /* * Reset the transition state that may possibly have been written by @@ -1182,6 +1193,96 @@ ExecCrossPartitionUpdate(ModifyTableState *mtstate, return true; } +/* + * Returns tuple table slot that the caller can use to store the tuples in the + * the root target relation's format, creating it if not already done. + */ +static TupleTableSlot * +GetRootTupleSlot(ModifyTableState *mtstate) +{ + if (mtstate->mt_root_tuple_slot == NULL) + { + Relation rootrel = mtstate->rootResultRelInfo->ri_RelationDesc; + + mtstate->mt_root_tuple_slot = table_slot_create(rootrel, NULL); + } + + return mtstate->mt_root_tuple_slot; +} + +/* + * Returns a map to convert the tuples of a given leaf partition result + * relation into the tuples of the root target relation, creating it if not + * already done. + */ +static TupleConversionMap * +GetChildToRootMap(ResultRelInfo *resultRelInfo, ModifyTableState *mtstate) +{ + if (!resultRelInfo->ri_ChildToRootMapValid) + { + Relation relation = resultRelInfo->ri_RelationDesc; + Relation rootRel = mtstate->rootResultRelInfo->ri_RelationDesc; + + resultRelInfo->ri_ChildToRootMap = + convert_tuples_by_name(RelationGetDescr(relation), + RelationGetDescr(rootRel)); + resultRelInfo->ri_ChildToRootMapValid = true; + } + + return resultRelInfo->ri_ChildToRootMap; +} + +/* + * Return the ancestor relations of a given leaf partition result relation + * up to and including the query's root target relation. + */ +static List * +GetAncestorResultRels(ResultRelInfo *resultRelInfo, + ModifyTableState *mtstate) +{ + Relation partRel = resultRelInfo->ri_RelationDesc; + Relation rootRel = mtstate->rootResultRelInfo->ri_RelationDesc; + + if (!partRel->rd_rel->relispartition) + elog(ERROR, "cannot find ancestors of a non-partition result relation"); + if (resultRelInfo->ri_ancestorResultRels == NIL) + { + ListCell *lc; + List *oids = get_partition_ancestors(RelationGetRelid(partRel)); + Oid rootRelOid = RelationGetRelid(rootRel); + List *ancResultRels = NIL; + + foreach(lc, oids) + { + Oid ancOid = lfirst_oid(lc); + Relation ancRel; + ResultRelInfo *rInfo; + + /* We use mtstate->rootResultRelInfo for the root relation. */ + if (ancOid == rootRelOid) + break; + + /* + * All ancestors up to the root target relation must have been + * locked by the planner or AcquireExecutorLocks(). + */ + ancRel = table_open(ancOid, NoLock); + rInfo = makeNode(ResultRelInfo); + + /* + * Pass 0 for RangeTableIndex to distinguish the relations that + * are opened here. + */ + InitResultRelInfo(rInfo, ancRel, 0, NULL, 0); + ancResultRels = lappend(ancResultRels, rInfo); + } + ancResultRels = lappend(ancResultRels, mtstate->rootResultRelInfo); + resultRelInfo->ri_ancestorResultRels = ancResultRels; + } + + return resultRelInfo->ri_ancestorResultRels; +} + /* ---------------------------------------------------------------- * ExecUpdate * @@ -1335,9 +1436,12 @@ lreplace:; */ if (partition_constraint_failed) { - TupleTableSlot *inserted_tuple, + TupleTableSlot *oldslot = slot, + *inserted_tuple, + *returning_slot = NULL, *retry_slot; bool retry; + ResultRelInfo *insert_destrel = NULL; /* * ExecCrossPartitionUpdate will first DELETE the row from the @@ -1349,14 +1453,97 @@ lreplace:; retry = !ExecCrossPartitionUpdate(mtstate, resultRelInfo, tupleid, oldtuple, slot, planSlot, epqstate, canSetTag, - &retry_slot, &inserted_tuple); + &retry_slot, &returning_slot, + &inserted_tuple, + &insert_destrel); if (retry) { slot = retry_slot; goto lreplace; } - return inserted_tuple; + /* + * Enforce foreign key actions using the root relation's triggers. + * NULL insert_destrel means that the move failed to occur or that + * the update failed, so no need to anything in that case. + */ + if (insert_destrel && + resultRelInfo->ri_TrigDesc && + resultRelInfo->ri_TrigDesc->trig_update_after_row) + { + ListCell *lc; + TupleTableSlot *rootslot; + TupleConversionMap *map; + ResultRelInfo *rootInfo = mtstate->rootResultRelInfo; + List *ancestorRels = GetAncestorResultRels(resultRelInfo, + mtstate); + + /* + * There better not be any foreign keys that point into some + * non-root ancestor but not root (tgisclone is false), because + * we can't enforce them (for now) like we can those on the + * root parent. + */ + foreach(lc, ancestorRels) + { + ResultRelInfo *rInfo = lfirst(lc); + TriggerDesc *trigdesc = rInfo->ri_TrigDesc; + bool has_noncloned_fkey = false; + + if (rInfo == mtstate->rootResultRelInfo) + break; + + if (trigdesc && trigdesc->trig_update_after_row) + { + int i; + + for (i = 0; i < trigdesc->numtriggers; i++) + { + Trigger *trig = &trigdesc->triggers[i]; + + if (!trig->tgisclone && + RI_FKey_trigger_type(trig->tgfoid) == RI_TRIGGER_PK) + { + has_noncloned_fkey = true; + break; + } + } + } + + if (has_noncloned_fkey) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot move tuple across partitions when non-root ancestor is directly referenced in a foreign key"), + errdetail("A foreign key points to ancestor \"%s\", but not the root ancestor \"%s\".", + RelationGetRelationName(rInfo->ri_RelationDesc), + RelationGetRelationName(rootInfo->ri_RelationDesc)), + errhint("Consider defining the foreign key on \"%s\".", + RelationGetRelationName(rootInfo->ri_RelationDesc)))); + } + + /* + * Copy the inserted tuple ("new" tuple) into the root table's + * slot, possibly converting it. + */ + rootslot = GetRootTupleSlot(mtstate); + map = GetChildToRootMap(resultRelInfo, mtstate); + if (inserted_tuple != slot && map) + slot = execute_attr_map_slot(map->attrMap, inserted_tuple, + rootslot); + else + slot = ExecCopySlot(rootslot, inserted_tuple); + + /* Get "old" HeapTuple from the source partition. */ + if (!table_tuple_fetch_row_version(resultRelationDesc, + tupleid, + SnapshotAny, oldslot)) + elog(ERROR, "failed to fetch old tuple from source partition"); + oldtuple = ExecFetchSlotHeapTuple(oldslot, true, NULL); + ExecARUpdateTriggers(estate, mtstate, rootInfo, NULL, oldtuple, + slot, NIL, NULL); + } + + return returning_slot; } /* @@ -1522,7 +1709,8 @@ lreplace:; (estate->es_processed)++; /* AFTER ROW UPDATE Triggers */ - ExecARUpdateTriggers(estate, resultRelInfo, tupleid, oldtuple, slot, + ExecARUpdateTriggers(estate, mtstate, resultRelInfo, tupleid, oldtuple, + slot, recheckIndexes, mtstate->operation == CMD_INSERT ? mtstate->mt_oc_transition_capture : @@ -2127,7 +2315,7 @@ ExecModifyTable(PlanState *pstate) { case CMD_INSERT: slot = ExecInsert(node, resultRelInfo, slot, planSlot, - estate, node->canSetTag); + estate, node->canSetTag, NULL, NULL); break; case CMD_UPDATE: slot = ExecUpdate(node, resultRelInfo, tupleid, oldtuple, slot, diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c index 7e2b2e3..e2f9eb9 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src/backend/utils/adt/ri_triggers.c @@ -1259,11 +1259,20 @@ RI_FKey_fk_upd_check_required(Trigger *trigger, Relation fk_rel, * not do anything; so we had better do the UPDATE check. (We could skip * this if we knew the INSERT trigger already fired, but there is no easy * way to know that.) + * + * Skip the check and just ask to fire the trigger if the FK relation is + * a partitioned table, because we can't inspect system columns of the + * tuple in that case. */ - xminDatum = slot_getsysattr(oldslot, MinTransactionIdAttributeNumber, &isnull); - Assert(!isnull); - xmin = DatumGetTransactionId(xminDatum); - if (TransactionIdIsCurrentTransactionId(xmin)) + if (fk_rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE) + { + xminDatum = slot_getsysattr(oldslot, MinTransactionIdAttributeNumber, &isnull); + Assert(!isnull); + xmin = DatumGetTransactionId(xminDatum); + if (TransactionIdIsCurrentTransactionId(xmin)) + return true; + } + else return true; /* If all old and new key values are equal, no check is needed */ diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h index 67cdb2d..43bd043 100644 --- a/src/include/commands/trigger.h +++ b/src/include/commands/trigger.h @@ -205,6 +205,7 @@ extern bool ExecBRDeleteTriggers(EState *estate, HeapTuple fdw_trigtuple, TupleTableSlot **epqslot); extern void ExecARDeleteTriggers(EState *estate, + ModifyTableState *mtstate, ResultRelInfo *relinfo, ItemPointer tupleid, HeapTuple fdw_trigtuple, @@ -224,6 +225,7 @@ extern bool ExecBRUpdateTriggers(EState *estate, HeapTuple fdw_trigtuple, TupleTableSlot *slot); extern void ExecARUpdateTriggers(EState *estate, + ModifyTableState *mtstate, ResultRelInfo *relinfo, ItemPointer tupleid, HeapTuple fdw_trigtuple, diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 6c0a7d6..0922a3e 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -497,9 +497,13 @@ typedef struct ResultRelInfo * transition tuple capture or update partition row movement is active. */ TupleConversionMap *ri_ChildToRootMap; + bool ri_ChildToRootMapValid; /* for use by copy.c when performing multi-inserts */ struct CopyMultiInsertBuffer *ri_CopyMultiInsertBuffer; + + /* Used during cross-partition updates on partitioned tables. */ + List *ri_ancestorResultRels; } ResultRelInfo; /* ---------------- diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 07bd5b6..e2c2c3d 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -2407,7 +2407,7 @@ DELETE FROM pk WHERE a = 20; ERROR: update or delete on table "pk11" violates foreign key constraint "fk_a_fkey2" on table "fk" DETAIL: Key (a)=(20) is still referenced from table "fk". UPDATE pk SET a = 90 WHERE a = 30; -ERROR: update or delete on table "pk11" violates foreign key constraint "fk_a_fkey2" on table "fk" +ERROR: update or delete on table "pk" violates foreign key constraint "fk_a_fkey" on table "fk" DETAIL: Key (a)=(30) is still referenced from table "fk". SELECT tableoid::regclass, * FROM fk; tableoid | a @@ -2470,3 +2470,147 @@ DROP SCHEMA fkpart9 CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table fkpart9.pk drop cascades to table fkpart9.fk +-- verify foreign keys are enforced during cross-partition updates, +-- especially on the PK side +CREATE SCHEMA fkpart10 + CREATE TABLE pk (a INT PRIMARY KEY) PARTITION BY LIST (a) + CREATE TABLE fk ( + a INT, + CONSTRAINT fkey FOREIGN KEY (a) REFERENCES pk(a) ON UPDATE CASCADE ON DELETE CASCADE + ) + CREATE TABLE fk_parted ( + a INT PRIMARY KEY, + CONSTRAINT fkey FOREIGN KEY (a) REFERENCES pk(a) ON UPDATE CASCADE ON DELETE CASCADE + ) PARTITION BY LIST (a) + CREATE TABLE fk_another ( + a INT, + CONSTRAINT fkey FOREIGN KEY (a) REFERENCES fk_parted (a) ON UPDATE CASCADE ON DELETE CASCADE + ) + CREATE TABLE pk1 PARTITION OF pk FOR VALUES IN (1, 2) PARTITION BY LIST (a) + CREATE TABLE pk11 PARTITION OF pk1 FOR VALUES IN (1) + CREATE TABLE pk12 PARTITION OF pk1 FOR VALUES IN (2) + CREATE TABLE pk2 PARTITION OF pk FOR VALUES IN (3) + CREATE TABLE pk3 PARTITION OF pk FOR VALUES IN (4) + CREATE TABLE fk1 PARTITION OF fk_parted FOR VALUES IN (1, 2) + CREATE TABLE fk2 PARTITION OF fk_parted FOR VALUES IN (3) + CREATE TABLE fk3 PARTITION OF fk_parted FOR VALUES IN (4); +INSERT INTO fkpart10.pk VALUES (1), (3); +INSERT INTO fkpart10.fk VALUES (1), (3); +INSERT INTO fkpart10.fk_parted VALUES (1), (3); +INSERT INTO fkpart10.fk_another VALUES (1), (3); +-- moves 2 rows from one leaf partition to another, with both updates being +-- cascaded to fk and fk_parted. Updates of fk_parted, of which one is +-- cross-partition (3 -> 4), are further cascaded to fk_another. +UPDATE fkpart10.pk SET a = a + 1 RETURNING tableoid::pg_catalog.regclass, *; + tableoid | a +---------------+--- + fkpart10.pk12 | 2 + fkpart10.pk3 | 4 +(2 rows) + +SELECT tableoid::pg_catalog.regclass, * FROM fkpart10.fk; + tableoid | a +-------------+--- + fkpart10.fk | 2 + fkpart10.fk | 4 +(2 rows) + +SELECT tableoid::pg_catalog.regclass, * FROM fkpart10.fk_parted; + tableoid | a +--------------+--- + fkpart10.fk1 | 2 + fkpart10.fk3 | 4 +(2 rows) + +SELECT tableoid::pg_catalog.regclass, * FROM fkpart10.fk_another; + tableoid | a +---------------------+--- + fkpart10.fk_another | 2 + fkpart10.fk_another | 4 +(2 rows) + +-- let's try with the foreign key pointing at tables in the partition tree +-- that are not the same as the query's target table +-- 1. foreign key pointing into a non-root ancestor +-- +-- A cross-partition update on the root table will fail, because we currently +-- can't enforce the foreign keys pointing into a non-leaf partition +ALTER TABLE fkpart10.fk DROP CONSTRAINT fkey; +DELETE FROM fkpart10.fk WHERE a = 4; +ALTER TABLE fkpart10.fk ADD CONSTRAINT fkey FOREIGN KEY (a) REFERENCES fkpart10.pk1 (a) ON UPDATE CASCADE ON DELETE CASCADE; +UPDATE fkpart10.pk SET a = a - 1; +ERROR: cannot move tuple across partitions when non-root ancestor is directly referenced in a foreign key +DETAIL: A foreign key points to ancestor "pk1", but not the root ancestor "pk". +HINT: Consider defining the foreign key on "pk". +-- it's okay though if the non-leaf partition is updated directly +UPDATE fkpart10.pk1 SET a = a - 1; +SELECT tableoid::pg_catalog.regclass, * FROM fkpart10.pk; + tableoid | a +---------------+--- + fkpart10.pk11 | 1 + fkpart10.pk3 | 4 +(2 rows) + +SELECT tableoid::pg_catalog.regclass, * FROM fkpart10.fk; + tableoid | a +-------------+--- + fkpart10.fk | 1 +(1 row) + +SELECT tableoid::pg_catalog.regclass, * FROM fkpart10.fk_parted; + tableoid | a +--------------+--- + fkpart10.fk1 | 1 + fkpart10.fk3 | 4 +(2 rows) + +SELECT tableoid::pg_catalog.regclass, * FROM fkpart10.fk_another; + tableoid | a +---------------------+--- + fkpart10.fk_another | 4 + fkpart10.fk_another | 1 +(2 rows) + +-- 2. foreign key pointing into a single leaf partition +-- +-- A cross-partition update that deletes from the pointed-to leaf partition +-- is allowed to succeed +ALTER TABLE fkpart10.fk DROP CONSTRAINT fkey; +ALTER TABLE fkpart10.fk ADD CONSTRAINT fkey FOREIGN KEY (a) REFERENCES fkpart10.pk11 (a) ON UPDATE CASCADE ON DELETE CASCADE; +-- will delete (1) from p11 which is cascaded to fk +UPDATE fkpart10.pk SET a = a + 1 WHERE a = 1; +SELECT tableoid::pg_catalog.regclass, * FROM fkpart10.fk; + tableoid | a +----------+--- +(0 rows) + +DROP TABLE fkpart10.fk; +-- check that regular and deferrable AR triggers on the PK tables +-- still work as expected +CREATE FUNCTION fkpart10.print_row () RETURNS TRIGGER LANGUAGE plpgsql AS $$ + BEGIN + RAISE NOTICE 'TABLE: %, OP: %, OLD: %, NEW: %', TG_RELNAME, TG_OP, OLD, NEW; + RETURN NULL; + END; +$$; +CREATE TRIGGER trig_upd_pk AFTER UPDATE ON fkpart10.pk FOR EACH ROW EXECUTE FUNCTION fkpart10.print_row(); +CREATE TRIGGER trig_del_pk AFTER DELETE ON fkpart10.pk FOR EACH ROW EXECUTE FUNCTION fkpart10.print_row(); +CREATE TRIGGER trig_ins_pk AFTER INSERT ON fkpart10.pk FOR EACH ROW EXECUTE FUNCTION fkpart10.print_row(); +CREATE CONSTRAINT TRIGGER trig_upd_fk_parted AFTER UPDATE ON fkpart10.fk_parted INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION fkpart10.print_row(); +CREATE CONSTRAINT TRIGGER trig_del_fk_parted AFTER DELETE ON fkpart10.fk_parted INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION fkpart10.print_row(); +CREATE CONSTRAINT TRIGGER trig_ins_fk_parted AFTER INSERT ON fkpart10.fk_parted INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION fkpart10.print_row(); +UPDATE fkpart10.pk SET a = 3 WHERE a = 4; +NOTICE: TABLE: pk3, OP: DELETE, OLD: (4), NEW: +NOTICE: TABLE: pk2, OP: INSERT, OLD: , NEW: (3) +NOTICE: TABLE: fk3, OP: DELETE, OLD: (4), NEW: +NOTICE: TABLE: fk2, OP: INSERT, OLD: , NEW: (3) +UPDATE fkpart10.pk SET a = 1 WHERE a = 2; +NOTICE: TABLE: pk12, OP: DELETE, OLD: (2), NEW: +NOTICE: TABLE: pk11, OP: INSERT, OLD: , NEW: (1) +NOTICE: TABLE: fk1, OP: UPDATE, OLD: (2), NEW: (1) +DROP SCHEMA fkpart10 CASCADE; +NOTICE: drop cascades to 4 other objects +DETAIL: drop cascades to table fkpart10.pk +drop cascades to table fkpart10.fk_parted +drop cascades to table fkpart10.fk_another +drop cascades to function fkpart10.print_row() diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index c5c9011..5902b78 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -1738,3 +1738,87 @@ DELETE FROM fkpart9.pk WHERE a=35; SELECT * FROM fkpart9.pk; SELECT * FROM fkpart9.fk; DROP SCHEMA fkpart9 CASCADE; + +-- verify foreign keys are enforced during cross-partition updates, +-- especially on the PK side +CREATE SCHEMA fkpart10 + CREATE TABLE pk (a INT PRIMARY KEY) PARTITION BY LIST (a) + CREATE TABLE fk ( + a INT, + CONSTRAINT fkey FOREIGN KEY (a) REFERENCES pk(a) ON UPDATE CASCADE ON DELETE CASCADE + ) + CREATE TABLE fk_parted ( + a INT PRIMARY KEY, + CONSTRAINT fkey FOREIGN KEY (a) REFERENCES pk(a) ON UPDATE CASCADE ON DELETE CASCADE + ) PARTITION BY LIST (a) + CREATE TABLE fk_another ( + a INT, + CONSTRAINT fkey FOREIGN KEY (a) REFERENCES fk_parted (a) ON UPDATE CASCADE ON DELETE CASCADE + ) + CREATE TABLE pk1 PARTITION OF pk FOR VALUES IN (1, 2) PARTITION BY LIST (a) + CREATE TABLE pk11 PARTITION OF pk1 FOR VALUES IN (1) + CREATE TABLE pk12 PARTITION OF pk1 FOR VALUES IN (2) + CREATE TABLE pk2 PARTITION OF pk FOR VALUES IN (3) + CREATE TABLE pk3 PARTITION OF pk FOR VALUES IN (4) + CREATE TABLE fk1 PARTITION OF fk_parted FOR VALUES IN (1, 2) + CREATE TABLE fk2 PARTITION OF fk_parted FOR VALUES IN (3) + CREATE TABLE fk3 PARTITION OF fk_parted FOR VALUES IN (4); +INSERT INTO fkpart10.pk VALUES (1), (3); +INSERT INTO fkpart10.fk VALUES (1), (3); +INSERT INTO fkpart10.fk_parted VALUES (1), (3); +INSERT INTO fkpart10.fk_another VALUES (1), (3); +-- moves 2 rows from one leaf partition to another, with both updates being +-- cascaded to fk and fk_parted. Updates of fk_parted, of which one is +-- cross-partition (3 -> 4), are further cascaded to fk_another. +UPDATE fkpart10.pk SET a = a + 1 RETURNING tableoid::pg_catalog.regclass, *; +SELECT tableoid::pg_catalog.regclass, * FROM fkpart10.fk; +SELECT tableoid::pg_catalog.regclass, * FROM fkpart10.fk_parted; +SELECT tableoid::pg_catalog.regclass, * FROM fkpart10.fk_another; + +-- let's try with the foreign key pointing at tables in the partition tree +-- that are not the same as the query's target table + +-- 1. foreign key pointing into a non-root ancestor +-- +-- A cross-partition update on the root table will fail, because we currently +-- can't enforce the foreign keys pointing into a non-leaf partition +ALTER TABLE fkpart10.fk DROP CONSTRAINT fkey; +DELETE FROM fkpart10.fk WHERE a = 4; +ALTER TABLE fkpart10.fk ADD CONSTRAINT fkey FOREIGN KEY (a) REFERENCES fkpart10.pk1 (a) ON UPDATE CASCADE ON DELETE CASCADE; +UPDATE fkpart10.pk SET a = a - 1; +-- it's okay though if the non-leaf partition is updated directly +UPDATE fkpart10.pk1 SET a = a - 1; +SELECT tableoid::pg_catalog.regclass, * FROM fkpart10.pk; +SELECT tableoid::pg_catalog.regclass, * FROM fkpart10.fk; +SELECT tableoid::pg_catalog.regclass, * FROM fkpart10.fk_parted; +SELECT tableoid::pg_catalog.regclass, * FROM fkpart10.fk_another; + +-- 2. foreign key pointing into a single leaf partition +-- +-- A cross-partition update that deletes from the pointed-to leaf partition +-- is allowed to succeed +ALTER TABLE fkpart10.fk DROP CONSTRAINT fkey; +ALTER TABLE fkpart10.fk ADD CONSTRAINT fkey FOREIGN KEY (a) REFERENCES fkpart10.pk11 (a) ON UPDATE CASCADE ON DELETE CASCADE; +-- will delete (1) from p11 which is cascaded to fk +UPDATE fkpart10.pk SET a = a + 1 WHERE a = 1; +SELECT tableoid::pg_catalog.regclass, * FROM fkpart10.fk; +DROP TABLE fkpart10.fk; + +-- check that regular and deferrable AR triggers on the PK tables +-- still work as expected +CREATE FUNCTION fkpart10.print_row () RETURNS TRIGGER LANGUAGE plpgsql AS $$ + BEGIN + RAISE NOTICE 'TABLE: %, OP: %, OLD: %, NEW: %', TG_RELNAME, TG_OP, OLD, NEW; + RETURN NULL; + END; +$$; +CREATE TRIGGER trig_upd_pk AFTER UPDATE ON fkpart10.pk FOR EACH ROW EXECUTE FUNCTION fkpart10.print_row(); +CREATE TRIGGER trig_del_pk AFTER DELETE ON fkpart10.pk FOR EACH ROW EXECUTE FUNCTION fkpart10.print_row(); +CREATE TRIGGER trig_ins_pk AFTER INSERT ON fkpart10.pk FOR EACH ROW EXECUTE FUNCTION fkpart10.print_row(); +CREATE CONSTRAINT TRIGGER trig_upd_fk_parted AFTER UPDATE ON fkpart10.fk_parted INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION fkpart10.print_row(); +CREATE CONSTRAINT TRIGGER trig_del_fk_parted AFTER DELETE ON fkpart10.fk_parted INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION fkpart10.print_row(); +CREATE CONSTRAINT TRIGGER trig_ins_fk_parted AFTER INSERT ON fkpart10.fk_parted INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION fkpart10.print_row(); +UPDATE fkpart10.pk SET a = 3 WHERE a = 4; +UPDATE fkpart10.pk SET a = 1 WHERE a = 2; + +DROP SCHEMA fkpart10 CASCADE; -- 1.8.3.1