Re: Triggers on foreign tables

On 10/6/13 3:33 PM, Kohei KaiGai wrote:
> 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.
> 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?
> 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.
>
> Any comment please.

What happens if someone changes the record on the foreign side between when we've read it and we do the UPDATE?
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net