Triggers on foreign tables

From: Ronan Dunklau <ronan(dot)dunklau(at)dalibo(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>
Subject: Triggers on foreign tables
Date: 2014-01-07 11:11:28
Message-ID: 4633963.4vozB4uK9G@ronan_laptop
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello.

Since the last discussion about it (http://www.postgresql.org/message-id/CADyhKSUGP6oJb1pybTiMOP3s5fg_yOkgUTo-7RBcLTNVaJ57Pw@mail.gmail.com) I
finally managed to implement row triggers for foreign tables.

The attached patch does not contain regression tests or documentation yet, a
revised patch will include those sometime during the week. I'll add it to the
commitfest then.

A simple test scenario using postgres_fdw is however included as an
attachment.

The attached patch implements triggers for foreign tables according to the
design discussed in the link above.

For statement-level triggers, nothing has changed since the last patch.
Statement-level triggers are just allowed in the command checking.

For row-level triggers, it works as follows:

- during rewrite phase, every attribute of the foreign table is duplicated as
a resjunk entry if a trigger is defined on the relation. These entries are
then used to store the old values for a tuple. There is room for improvement
here: we could check if any trigger is in fact a row-level trigger
- during execution phase, the before triggers are fired exactly like triggers
on regular tables, except that old tuples are not fetched using their ctid,
but rebuilt using the previously-stored resjunk attributes.
- for after triggers, the whole queuing mechanism is bypassed for foreign
tables. This is IMO acceptable, since foreign tables cannot have constraints
or constraints triggers, and thus have not need for deferrable execution. This
design avoids the need for storing and retrieving/identifying remote tuples
until the query or transaction end.
- the duplicated resjunk attributes are identified by being:
- marked as resjunk in the targetlist
- not being system or whole-row attributes (varno > 0)

There is still one small issue with the attached patch: modifications to the
tuple performed by the foreign data wrapper (via the returned TupleTableSlot
in ExecForeignUpdate and ExecForeignInsert hooks) are not visible to the AFTER
trigger. This could be fixed by merging the planslot containing the resjunk
columns with the returned slot before calling the trigger, but I'm not really
sure how to safely perform that. Any advice ?

Many thanks to Kohei Kaigai for taking the time to help with the design.

--
Ronan Dunklau
http://dalibo.com - http://dalibo.org

Attachment Content-Type Size
test_with_postgres_fdw.sql application/sql 2.2 KB
foreign_triggers_v3.patch text/x-patch 18.5 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2014-01-07 11:12:26 Re: truncating pg_multixact/members
Previous Message Dan Ports 2014-01-07 10:30:54 Re: Re: How to reproduce serialization failure for a read only transaction.