Re: Triggers on foreign tables

> Sorry, I might call it something like primary key, instead of 'tupleid'.
> Apart from whether we can uniquely identify a particular remote record with
> an attribute, what FDW needs here is "something to identify a remote
> record". So, we were talking about same concept with different names.

Ah, that makes sense: I was understanding tupleid as a synonym for ctid.

> >> Does the incomplete tuple mean a tuple image but some of columns
> >> are replaced with NULL due to optimization, as postgres_fdw is doing,
> >> doesn't it?
> >> If so, a solution is to enforce FDW driver to fetch all the columns if
> >> its
> >> managing foreign table has row level triggers for update / delete.
> >
> > What I meant is that a DELETE statement, for example, does not build a
> > scan- stage reltargetlist consisting of the whole set of attributes: the
> > only attributes that are fetched are the ones built by
> > addForeignUpdateTargets, and whatever additional attributes are involved
> > in the DELETE statement (in the WHERE clause, for example).
>
> DELETE statement indeed does not (need to) construct a complete tuple
> image on scan stage, however, it does not prohibit FDW driver to keep the
> latest record being fetched from remote server.
> FDW driver easily knows whether relation has row-level trigger preliminary,
> so here is no matter to keep a complete image internally if needed.
> Or, it is perhaps available to add additional target-entries that is
> needed to construct a complete tuple using AddForeignUpdateTargets()
> callback.

I think that the additional target-entries should be added in core, because
that would require additional work from FDW drivers, and the code would be
duplicated amongst all FDW drivers that wish to support triggers.

> > I like this idea, but I don't really see all the implications of such a
> > design.
> > Where would this tuple be stored ?
> > From what I understand, after-triggers are queued for later execution,
> > using the old/new tupleid for later retrieval (in the
> > AfterTriggerEventData struct). This would need a major change to work
> > with foreign tables. Would that involve a special path for executing
> > those triggers ASAP ?
>
> Ops, I oversight here. AfterTriggerSaveEvent() indeed saves only ctid of
> tuples in regular tables, because it can re-construct a complete tuple
> image from a particular ctid any time.
> On the other hand, it is not available on foreign table due to its nature.
> All we can do seems to me, probably, to save copy of before/after tuple
> image on local memory, even though it consumes much memory than
> regular tables.

Or, as suggested above, add a special code path for foreign tables which would
execute the trigger as soon as possible instead of queuing it.

> The feature we need here might become a bit clear little by little.
> A complete tuple image shall be fetched on the scan stage, if foreign
> table has row-level trigger. The planSlot may make sense if it contains
> (junk) attributes that is sufficient to re-construct an old tuple image.
> Target-list of DELETE statement contains a junk ctid attribute. Here
> is no reason why we cannot add junk attributes that reference each
> regular attribute, isn't it? Also, target-list of UPDATE statement
> contains new tuple image, however, it is available to add junk attributes
> that just reference old value, as a carrier of old values from scan stage
> to modify stage.
> I want core PostgreSQL to support a part of these jobs. For example,
> it may be able to add junk attribute to re-construct old tuple image on
> rewriteTargetListUD(), if target relation has row-level triggers. Also, it
> may be able to extract these old values from planSlot to construct
> an old tuple image on GetTupleForTrigger(), if relation is foreign table.

So, if I understand you, the target list would be built as follow:

- core builds the target list, including every attribute
- this target list is updated by the FDW to add any junk attributes it wishes
to use
- in the case of an UPDATE, core duplicates the whole list of attributes
(including the added junk attributes), as another set of junk attributes.
Maybe we could duplicate only the updated attributes.

> Unfortunately, I have no special idea to handle old/new remote tuple
> on AfterTriggerSaveEvent(), except for keeping it as copy, but it is
> straightforward.

Maybe avoid it altogether in the case of a trigger on a remote foreign table ?

>
> >> And, I also want some comments from committers, not only from mine.
> >
> > +1
>
> +1
>
> Thanks,