Re: Triggers on foreign tables

Le samedi 12 octobre 2013 07:30:35 Kohei KaiGai a écrit :
> 2013/10/10 Ronan Dunklau <rdunklau(at)gmail(dot)com>:

> Sorry, I'm uncertain the point above.
> Are you saying FDW driver may be able to handle well a case when
> a remote tuple to be updated is different from a remote tuple being
> fetched on the first stage? Or, am I misunderstanding?

I was not clear in the point above: what I meant was that, from my
understanding, the FDW driver has no obligation to use the system-attribute
"tupleid" to identify the remote tuple. IIRC, one of the early API proposal
involved had the tupleid passed as an argument to the ExecForeign* hooks.
Now, these hooks receive the whole "planslot" instead. This slot can store
additional resjunks attributes (thanks to the "AddForeignUpdateTargets")
which shouldn't be altered during the execution and are carried on until
modify stage. So, these additional attributes can be used as identifiers
instead of the tupleid.

In fact, this is the approach I implemented for the multicorn fdw, and I
believe it to be used in other FDWs as well (HadoopFDW does that, if I
understand it correctly).

So, it doesn't make sense to me to assume that the tupleid will be filled as
valid remote identifier in the context of triggers.

>
> From another standpoint, I'd like to cancel the above my suggestion,
> because after-row update or delete trigger tries to fetch an older image
> of tuple next to the actual update / delete jobs.
> Even if FDW is responsible to identify a remote tuple using tupleid,
> the identified tuple we can fetch is the newer image because FDW
> driver already issues a remote query to update or delete the target
> record.
> So, soon or later, FDW shall be responsible to keep a complete tuple
> image when foreign table has row-level triggers, until writer operation
> is finished.

+1

> 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).

I apologize if this is not clear, since both my technical and english
vocabulary are maybe not precise enough to get my point accross.

> > Or, perform a second round-trip to the foreign data store to fetch the
> > whole tuple.
>
> It might be a solution for before-row trigger, but not for after-row
> trigger, unfortunately.

+1

> As I noted above, 2nd round trip is not a suitable design to support
> after-row trigger. Likely, GetTupleForTrigger() hook shall perform to
> return a cached older image being fetched on the first round of remote
> scan.
> So, I guess every FDW driver will have similar implementation to handle
> these the situation, thus it makes sense that PostgreSQL core has
> a feature to support this handling; that keeps a tuple being fetched last
> and returns older image for row-level triggers.
>
> How about your opinion?

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 ?

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

+1

Thank you for taking the time to think this through.

--
Ronan Dunklau