Attached is a WIP patch for implementing the capture of delta
relations for a DML statement, in the form of two tuplestores --
one for the old versions and one for the new versions. In the
short term it is intended to make these relations available in
trigger functions, although the patch so far doesn't touch any PLs
-- it just takes things as far as providing the relations as
tuplestores in the TriggerData structure when appropriate, for the
PLs to pick up from there. It seemed best to get agreement on the
overall approach before digging into all the PLs. This is
implemented only for INSERT, UPDATE, and DELETE since it wasn't
totally clear what the use cases and proper behavior was for other
triggers. Opinions on whether we should try to provide deltas for
other cases, and if so what the semantics are, are welcome.
Once triggers can access this delta data, it will also be used for
incremental maintenance of materialized views, although I don't
want get too sidetracked on any details of that until we have
proven delta data available in triggers. (One step at a time or
we'll never get there.)
I looked at the standard, and initially tried to implement the
standard syntax for this; however, it appeared that the reasons
given for not using standard syntax for the row variables also
apply to the transition relations (the term used by the standard).
There isn't an obvious way to tie that in to all the PLs we
support. It could be done, but it seems like it would intolerably
ugly, and more fragile than what we have done so far.
Some things which I *did* follow from the standard: these new
relations are only allowed within AFTER triggers, but are available
in both AFTER STATEMENT and AFTER ROW triggers. That is, an AFTER
UPDATE ... FOR EACH ROW trigger could use both the OLD and NEW row
variables as well as the delta relations (under whatever names we
pick). That probably won't be used very often, but I can imagine
some cases where it might be useful. I expect that these will
normally be used in FOR EACH STATEMENT triggers.
There are a couple things I would really like to get settled in
this round of review, so things don't need to be refactored in
major ways later:
(1) My first impulse was to capture this delta data in the form of
tuplestores of just TIDs, and fetching the tuples themselves from
the heap on reference. In earlier discussions others have argued
for using tuplestores of the actual rows themselves. I have taken
that advice here, but still don't feel 100% convinced. What I am
convinced of is that I don't want to write a lot of code based on
that decision and only then have people weigh in on the side of how
I had planned to do it in the first place. I hate it when that
happens.
(2) Do we want to just pick names for these in the PLs rather than
using the standard syntax? Implementing the standard syntax seemed
to require three new (unreserved) keywords, changes to the catalogs
to store the chosen relations names, and some way to tie the
specified relation names in to the various PLs. The way I have
gone here just adds two new fields to the TriggerData structure and
leaves it to each PL how to deal with that. Failure to do anything
in a PL just leaves it at the status quo with no real harm done --
it just won't have the new delta relations available.
Of course, any other comments on the approach taken or how it can
be improved are welcome.
At this point the only testing is that make check-world completes
without problems. If we can agree on this part of it I will look
at the PLs, and create regression tests. I would probably submit
each PL implementation as a separate patch.
I was surprised that the patch to this point was so small:
5 files changed, 170 insertions(+), 19 deletions(-)
Hopefully that's not due to having missed something.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
*** a/doc/src/sgml/ref/create_table.sgml
--- b/doc/src/sgml/ref/create_table.sgml
***************
*** 1062,1067 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
--- 1062,1079 ----
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>generate_deltas</literal> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ Declare that a table generates delta relations when modified. This
+ allows <literal>AFTER</> triggers to reference the set of rows modified
+ by a statement. See
+ <xref linkend="sql-createtrigger"> for details.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</refsect2>
*** a/src/backend/access/common/reloptions.c
--- b/src/backend/access/common/reloptions.c
***************
*** 85,90 **** static relopt_bool boolRelOpts[] =
--- 85,98 ----
},
false
},
+ {
+ {
+ "generate_deltas",
+ "Relation generates delta relations for use by AFTER triggers",
+ RELOPT_KIND_HEAP
+ },
+ false
+ },
/* list terminator */
{{NULL}}
};
***************
*** 1205,1211 **** default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
{"check_option", RELOPT_TYPE_STRING,
offsetof(StdRdOptions, check_option_offset)},
{"user_catalog_table", RELOPT_TYPE_BOOL,
! offsetof(StdRdOptions, user_catalog_table)}
};
options = parseRelOptions(reloptions, validate, kind, &numoptions);
--- 1213,1221 ----
{"check_option", RELOPT_TYPE_STRING,
offsetof(StdRdOptions, check_option_offset)},
{"user_catalog_table", RELOPT_TYPE_BOOL,
! offsetof(StdRdOptions, user_catalog_table)},
! {"generate_deltas", RELOPT_TYPE_BOOL,
! offsetof(StdRdOptions, generate_deltas)}
};
options = parseRelOptions(reloptions, validate, kind, &numoptions);
*** a/src/backend/commands/trigger.c
--- b/src/backend/commands/trigger.c
***************
*** 2060,2066 **** ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo,
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
! if (trigdesc && trigdesc->trig_insert_after_row)
AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_INSERT,
true, NULL, trigtuple, recheckIndexes, NULL);
}
--- 2060,2069 ----
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
! if (trigdesc &&
! (trigdesc->trig_insert_after_row ||
! (trigdesc->trig_insert_after_statement &&
! RelationGeneratesDeltas(relinfo->ri_RelationDesc))))
AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_INSERT,
true, NULL, trigtuple, recheckIndexes, NULL);
}
***************
*** 2263,2269 **** ExecARDeleteTriggers(EState *estate, ResultRelInfo *relinfo,
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
! if (trigdesc && trigdesc->trig_delete_after_row)
{
HeapTuple trigtuple;
--- 2266,2275 ----
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
! if (trigdesc &&
! (trigdesc->trig_delete_after_row ||
! (trigdesc->trig_delete_after_statement &&
! RelationGeneratesDeltas(relinfo->ri_RelationDesc))))
{
HeapTuple trigtuple;
***************
*** 2528,2534 **** ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
! if (trigdesc && trigdesc->trig_update_after_row)
{
HeapTuple trigtuple;
--- 2534,2543 ----
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
! if (trigdesc &&
! (trigdesc->trig_update_after_row ||
! (trigdesc->trig_update_after_statement &&
! RelationGeneratesDeltas(relinfo->ri_RelationDesc))))
{
HeapTuple trigtuple;
***************
*** 3160,3167 **** typedef struct AfterTriggerEventList
* fdw_tuplestores[query_depth] is a tuplestore containing the foreign tuples
* needed for the current query.
*
! * maxquerydepth is just the allocated length of query_stack and
! * fdw_tuplestores.
*
* state_stack is a stack of pointers to saved copies of the SET CONSTRAINTS
* state data; each subtransaction level that modifies that state first
--- 3169,3179 ----
* fdw_tuplestores[query_depth] is a tuplestore containing the foreign tuples
* needed for the current query.
*
! * old_tuplestores[query_depth] and new_tuplestores[query_depth] hold the
! * delta relations for the current query.
! *
! * maxquerydepth is just the allocated length of query_stack and the
! * tuplestores.
*
* state_stack is a stack of pointers to saved copies of the SET CONSTRAINTS
* state data; each subtransaction level that modifies that state first
***************
*** 3190,3196 **** typedef struct AfterTriggersData
AfterTriggerEventList events; /* deferred-event list */
int query_depth; /* current query list index */
AfterTriggerEventList *query_stack; /* events pending from each query */
! Tuplestorestate **fdw_tuplestores; /* foreign tuples from each query */
int maxquerydepth; /* allocated len of above array */
MemoryContext event_cxt; /* memory context for events, if any */
--- 3202,3210 ----
AfterTriggerEventList events; /* deferred-event list */
int query_depth; /* current query list index */
AfterTriggerEventList *query_stack; /* events pending from each query */
! Tuplestorestate **fdw_tuplestores; /* foreign tuples for one row from each query */
! Tuplestorestate **old_tuplestores; /* all old tuples from each query */
! Tuplestorestate **new_tuplestores; /* all new tuples from each query */
int maxquerydepth; /* allocated len of above array */
MemoryContext event_cxt; /* memory context for events, if any */
***************
*** 3224,3234 **** static SetConstraintState SetConstraintStateAddItem(SetConstraintState state,
* Gets the current query fdw tuplestore and initializes it if necessary
*/
static Tuplestorestate *
! GetCurrentFDWTuplestore()
{
Tuplestorestate *ret;
! ret = afterTriggers->fdw_tuplestores[afterTriggers->query_depth];
if (ret == NULL)
{
MemoryContext oldcxt;
--- 3238,3248 ----
* Gets the current query fdw tuplestore and initializes it if necessary
*/
static Tuplestorestate *
! GetCurrentTuplestore(Tuplestorestate **tss)
{
Tuplestorestate *ret;
! ret = tss[afterTriggers->query_depth];
if (ret == NULL)
{
MemoryContext oldcxt;
***************
*** 3255,3261 **** GetCurrentFDWTuplestore()
CurrentResourceOwner = saveResourceOwner;
MemoryContextSwitchTo(oldcxt);
! afterTriggers->fdw_tuplestores[afterTriggers->query_depth] = ret;
}
return ret;
--- 3269,3275 ----
CurrentResourceOwner = saveResourceOwner;
MemoryContextSwitchTo(oldcxt);
! tss[afterTriggers->query_depth] = ret;
}
return ret;
***************
*** 3515,3520 **** AfterTriggerExecute(AfterTriggerEvent event,
--- 3529,3535 ----
{
AfterTriggerShared evtshared = GetTriggerSharedData(event);
Oid tgoid = evtshared->ats_tgoid;
+ int event_op = evtshared->ats_event & TRIGGER_EVENT_OPMASK;
TriggerData LocTriggerData;
HeapTupleData tuple1;
HeapTupleData tuple2;
***************
*** 3552,3565 **** AfterTriggerExecute(AfterTriggerEvent event,
{
case AFTER_TRIGGER_FDW_FETCH:
{
! Tuplestorestate *fdw_tuplestore = GetCurrentFDWTuplestore();
if (!tuplestore_gettupleslot(fdw_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,
trig_tuple_slot2))
elog(ERROR, "failed to fetch tuple2 for AFTER trigger");
--- 3567,3580 ----
{
case AFTER_TRIGGER_FDW_FETCH:
{
! Tuplestorestate *fdw_tuplestore =
! GetCurrentTuplestore(afterTriggers->fdw_tuplestores);
if (!tuplestore_gettupleslot(fdw_tuplestore, true, false,
trig_tuple_slot1))
elog(ERROR, "failed to fetch tuple1 for AFTER trigger");
! if (event_op == TRIGGER_EVENT_UPDATE &&
!tuplestore_gettupleslot(fdw_tuplestore, true, false,
trig_tuple_slot2))
elog(ERROR, "failed to fetch tuple2 for AFTER trigger");
***************
*** 3580,3588 **** AfterTriggerExecute(AfterTriggerEvent event,
ExecMaterializeSlot(trig_tuple_slot1);
LocTriggerData.tg_trigtuplebuf = InvalidBuffer;
! LocTriggerData.tg_newtuple =
! ((evtshared->ats_event & TRIGGER_EVENT_OPMASK) ==
! TRIGGER_EVENT_UPDATE) ?
ExecMaterializeSlot(trig_tuple_slot2) : NULL;
LocTriggerData.tg_newtuplebuf = InvalidBuffer;
--- 3595,3601 ----
ExecMaterializeSlot(trig_tuple_slot1);
LocTriggerData.tg_trigtuplebuf = InvalidBuffer;
! LocTriggerData.tg_newtuple = (event_op == TRIGGER_EVENT_UPDATE) ?
ExecMaterializeSlot(trig_tuple_slot2) : NULL;
LocTriggerData.tg_newtuplebuf = InvalidBuffer;
***************
*** 3622,3627 **** AfterTriggerExecute(AfterTriggerEvent event,
--- 3635,3659 ----
}
/*
+ * Set up the tuplestore information.
+ */
+ if (RelationGeneratesDeltas(rel))
+ {
+ if (event_op == TRIGGER_EVENT_DELETE ||
+ event_op == TRIGGER_EVENT_UPDATE)
+ LocTriggerData.tg_olddelta =
+ GetCurrentTuplestore(afterTriggers->old_tuplestores);
+ else
+ LocTriggerData.tg_olddelta = NULL;
+ if (event_op == TRIGGER_EVENT_INSERT ||
+ event_op == TRIGGER_EVENT_UPDATE)
+ LocTriggerData.tg_newdelta =
+ GetCurrentTuplestore(afterTriggers->new_tuplestores);
+ else
+ LocTriggerData.tg_newdelta = NULL;
+ }
+
+ /*
* Setup the remaining trigger information
*/
LocTriggerData.type = T_TriggerData;
***************
*** 3921,3926 **** AfterTriggerBeginXact(void)
--- 3953,3964 ----
afterTriggers->fdw_tuplestores = (Tuplestorestate **)
MemoryContextAllocZero(TopTransactionContext,
8 * sizeof(Tuplestorestate *));
+ afterTriggers->old_tuplestores = (Tuplestorestate **)
+ MemoryContextAllocZero(TopTransactionContext,
+ 8 * sizeof(Tuplestorestate *));
+ afterTriggers->new_tuplestores = (Tuplestorestate **)
+ MemoryContextAllocZero(TopTransactionContext,
+ 8 * sizeof(Tuplestorestate *));
afterTriggers->maxquerydepth = 8;
/* Context for events is created only when needed */
***************
*** 3970,3978 **** AfterTriggerBeginQuery(void)
--- 4008,4026 ----
afterTriggers->fdw_tuplestores = (Tuplestorestate **)
repalloc(afterTriggers->fdw_tuplestores,
new_alloc * sizeof(Tuplestorestate *));
+ afterTriggers->old_tuplestores = (Tuplestorestate **)
+ repalloc(afterTriggers->old_tuplestores,
+ new_alloc * sizeof(Tuplestorestate *));
+ afterTriggers->new_tuplestores = (Tuplestorestate **)
+ repalloc(afterTriggers->new_tuplestores,
+ new_alloc * sizeof(Tuplestorestate *));
/* Clear newly-allocated slots for subsequent lazy initialization. */
memset(afterTriggers->fdw_tuplestores + old_alloc,
0, (new_alloc - old_alloc) * sizeof(Tuplestorestate *));
+ memset(afterTriggers->old_tuplestores + old_alloc,
+ 0, (new_alloc - old_alloc) * sizeof(Tuplestorestate *));
+ memset(afterTriggers->new_tuplestores + old_alloc,
+ 0, (new_alloc - old_alloc) * sizeof(Tuplestorestate *));
afterTriggers->maxquerydepth = new_alloc;
}
***************
*** 4001,4006 **** AfterTriggerEndQuery(EState *estate)
--- 4049,4056 ----
{
AfterTriggerEventList *events;
Tuplestorestate *fdw_tuplestore;
+ Tuplestorestate *old_tuplestore;
+ Tuplestorestate *new_tuplestore;
/* Must be inside a transaction */
Assert(afterTriggers != NULL);
***************
*** 4045,4057 **** AfterTriggerEndQuery(EState *estate)
break;
}
! /* Release query-local storage for events, including tuplestore if any */
fdw_tuplestore = afterTriggers->fdw_tuplestores[afterTriggers->query_depth];
if (fdw_tuplestore)
{
tuplestore_end(fdw_tuplestore);
afterTriggers->fdw_tuplestores[afterTriggers->query_depth] = NULL;
}
afterTriggerFreeEventList(&afterTriggers->query_stack[afterTriggers->query_depth]);
afterTriggers->query_depth--;
--- 4095,4119 ----
break;
}
! /* Release query-local storage for events, including tuplestores, if any */
fdw_tuplestore = afterTriggers->fdw_tuplestores[afterTriggers->query_depth];
if (fdw_tuplestore)
{
tuplestore_end(fdw_tuplestore);
afterTriggers->fdw_tuplestores[afterTriggers->query_depth] = NULL;
}
+ old_tuplestore = afterTriggers->old_tuplestores[afterTriggers->query_depth];
+ if (old_tuplestore)
+ {
+ tuplestore_end(old_tuplestore);
+ afterTriggers->old_tuplestores[afterTriggers->query_depth] = NULL;
+ }
+ new_tuplestore = afterTriggers->new_tuplestores[afterTriggers->query_depth];
+ if (new_tuplestore)
+ {
+ tuplestore_end(new_tuplestore);
+ afterTriggers->new_tuplestores[afterTriggers->query_depth] = NULL;
+ }
afterTriggerFreeEventList(&afterTriggers->query_stack[afterTriggers->query_depth]);
afterTriggers->query_depth--;
***************
*** 4283,4288 **** AfterTriggerEndSubXact(bool isCommit)
--- 4345,4362 ----
tuplestore_end(ts);
afterTriggers->fdw_tuplestores[afterTriggers->query_depth] = NULL;
}
+ ts = afterTriggers->old_tuplestores[afterTriggers->query_depth];
+ if (ts)
+ {
+ tuplestore_end(ts);
+ afterTriggers->old_tuplestores[afterTriggers->query_depth] = NULL;
+ }
+ ts = afterTriggers->new_tuplestores[afterTriggers->query_depth];
+ if (ts)
+ {
+ tuplestore_end(ts);
+ afterTriggers->new_tuplestores[afterTriggers->query_depth] = NULL;
+ }
afterTriggerFreeEventList(&afterTriggers->query_stack[afterTriggers->query_depth]);
afterTriggers->query_depth--;
***************
*** 4767,4773 **** AfterTriggerPendingOnRel(Oid relid)
*
* NOTE: this is called whenever there are any triggers associated with
* the event (even if they are disabled). This function decides which
! * triggers actually need to be queued.
* ----------
*/
static void
--- 4841,4854 ----
*
* NOTE: this is called whenever there are any triggers associated with
* the event (even if they are disabled). This function decides which
! * triggers actually need to be queued. It is also called after each row,
! * even if there are no triggers for that event, if the table has the
! * generate_deltas storage property set and there are any AFTER STATEMENT
! * triggers, so that the delta relations can be built.
! *
! * Delta tuplestores are built now, rather than when events are pulled off
! * of the queue because AFTER ROW triggers are allowed to select from the
! * delta relations for the statement.
* ----------
*/
static void
***************
*** 4777,4782 **** AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
--- 4858,4864 ----
List *recheckIndexes, Bitmapset *modifiedCols)
{
Relation rel = relinfo->ri_RelationDesc;
+ bool generate_deltas = RelationGeneratesDeltas(rel);
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
AfterTriggerEventData new_event;
AfterTriggerSharedData new_shared;
***************
*** 4797,4807 **** AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
--- 4879,4924 ----
elog(ERROR, "AfterTriggerSaveEvent() called outside of query");
/*
+ * If the relation has enabled generate_deltas, capture rows into delta
+ * tuplestores for this depth.
+ */
+ if (generate_deltas && row_trigger)
+ {
+ if (event == TRIGGER_EVENT_DELETE || event == TRIGGER_EVENT_UPDATE)
+ {
+ Tuplestorestate *old_tuplestore;
+
+ Assert(oldtup != NULL);
+ old_tuplestore =
+ GetCurrentTuplestore(afterTriggers->old_tuplestores);
+ tuplestore_puttuple(old_tuplestore, oldtup);
+ }
+ if (event == TRIGGER_EVENT_INSERT || event == TRIGGER_EVENT_UPDATE)
+ {
+ Tuplestorestate *new_tuplestore;
+
+ Assert(newtup != NULL);
+ new_tuplestore =
+ GetCurrentTuplestore(afterTriggers->new_tuplestores);
+ tuplestore_puttuple(new_tuplestore, newtup);
+ }
+
+ /* If deltas were the only reason we're here, return. */
+ if ((event == TRIGGER_EVENT_DELETE && !trigdesc->trig_delete_after_row) ||
+ (event == TRIGGER_EVENT_INSERT && !trigdesc->trig_insert_after_row) ||
+ (event == TRIGGER_EVENT_UPDATE && !trigdesc->trig_update_after_row))
+ return;
+ }
+
+ /*
* Validate the event code and collect the associated tuple CTIDs.
*
* The event code will be used both as a bitmask and an array offset, so
* validation is important to make sure we don't walk off the edge of our
* arrays.
+ *
+ * If we are only saving a row for statement level delta relations, return
+ * early.
*/
switch (event)
{
***************
*** 4893,4899 **** AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
{
if (fdw_tuplestore == NULL)
{
! fdw_tuplestore = GetCurrentFDWTuplestore();
new_event.ate_flags = AFTER_TRIGGER_FDW_FETCH;
}
else
--- 5010,5017 ----
{
if (fdw_tuplestore == NULL)
{
! fdw_tuplestore =
! GetCurrentTuplestore(afterTriggers->fdw_tuplestores);
new_event.ate_flags = AFTER_TRIGGER_FDW_FETCH;
}
else
*** a/src/include/commands/trigger.h
--- b/src/include/commands/trigger.h
***************
*** 36,41 **** typedef struct TriggerData
--- 36,43 ----
Trigger *tg_trigger;
Buffer tg_trigtuplebuf;
Buffer tg_newtuplebuf;
+ Tuplestorestate *tg_olddelta;
+ Tuplestorestate *tg_newdelta;
} TriggerData;
/*
*** a/src/include/utils/rel.h
--- b/src/include/utils/rel.h
***************
*** 220,225 **** typedef struct StdRdOptions
--- 220,226 ----
int check_option_offset; /* for views */
bool user_catalog_table; /* use as an additional catalog
* relation */
+ bool generate_deltas; /* generate delta relations */
} StdRdOptions;
#define HEAP_MIN_FILLFACTOR 10
***************
*** 298,303 **** typedef struct StdRdOptions
--- 299,312 ----
((StdRdOptions *) (relation)->rd_options)->user_catalog_table : false)
/*
+ * RelationGeneratesDeltas
+ * Returns whether the relation captures delta information when changed.
+ */
+ #define RelationGeneratesDeltas(relation) \
+ ((relation)->rd_options ? \
+ ((StdRdOptions *) (relation)->rd_options)->generate_deltas : false)
+
+ /*
* RelationIsValid
* True iff relation descriptor is valid.
*/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
