Re: Triggers on foreign tables

Le dimanche 6 octobre 2013 22:33:23 Kohei KaiGai a écrit :
> 2013/9/10 Ronan Dunklau <rdunklau(at)gmail(dot)com>:
> > For row-level triggers, it seems more complicated. From what I understand,
> > OLD/NEW tuples are fetched from the heap using their ctid (except for
> > BEFORE INSERT triggers). How could this be adapted for foreign tables ?
>
> It seems to me the origin of difficulty to support row-level trigger
> is that foreign table
> does not have a back-door to see the older tuple to be updated, unlike
> regular tables.
> The core-PostgreSQL knows on-disk format to store tuples within
> regular tables, thus,
> GetTupleForTrigger() can fetch a tuple being identified by tupleid.
> On the other hand, writable foreign table is also designed to identify
> a remote tuple
> with tupleid, as long as FDW module is responsible.

It is my understanding that the tupleid is one way for a FDW to identify a
tuple, but the AddForeignUpdateTargets hook allows for arbitrary resjunks
columns to be added. It is these columns that are then used to identify the
tuple to update. I don't think the tupleid itself can be used to identify a
tuple for the trigger.

> So, a straightforward idea is adding a new FDW interface to get an
> older image of
> the tuple to be updated. It makes possible to implement something similar to
> GetTupleForTrigger() on foreign tables, even though it takes a
> secondary query to
> fetch a record from the remote server. Probably, it is an headache for us

> One thing we pay attention is, the tuple to be updated is already
> fetched from the
> remote server and the tuple being fetched latest is (always?) the
> target of update
> or delete. It is not a heavy job for ForeignScanState node to hold a
> copy of the latest
> tuple. Isn't it an available way to reference relevant
> ForeignScanState to get the older
> image?

It is however possible to have only an incomplete tuple.

The FDW may have only fetched the tupleid-equivalent, and we would have to
ensure that the reltargetlist contains every attribute that the trigger could
need. Or, perform a second round-trip to the foreign data store to fetch the
whole tuple.

> If my assumption is right, all we need to enhance are (1) add a store on
> ForeignScanState to hold the last tuple on the scan stage, (2) add
> GetForeignTupleForTrigger() to reference the store above on the relevant
> ForeignScanState.

I would add a 3) have a GetTupleForTrigger() hook that would get called with
the stored tuple.

I personnally don't like this approach: there is already (n+1) round trips to
the foreign store (one during the scan phase, and one per tuple during the
update/delete phase), we don't need another one per tuple for the triggers.