Eelke Klein wrote:
> I'm experimenting with using foreign data wrappers to get data from one database to another. Most
> things work perfectly but I am encountering two issues with triggers on the foreign tables.
>
> The first one is when a query triggers a trigger on the foreign table the trigger doesn't have any
> search_path. Not even a select on a table in public will work from the trigger unless I specify the
> schema name.
Wouldn't a trigger that depends on the current search_path setting be
pretty dangerous anyway? Anybody can change the setting.
I'd suggest that you make the trigger more robust.
> The second problem has to do with DEFAULT values. One of the tables i'm inserting data on has a column
> called id of type bigserial. This column is not filled by the INSERT statement I'm using so should use
> it's default which is nextval('...'::regclass); However in the BEFORE INSERT trigger the value of
> NEW.id IS NULL while normally it would already be assigned a value from the sequence.
That is actually working as intended, see this thread:
http://www.postgresql.org/message-id/24107.1363027848@sss.pgh.pa.us
Nobody could come up with a better solution.
> The issues with the triggers do not occur when they are triggered by statements executed directly on
> the database. Only when the statements come from the FDW I have these issues.
Can you reproduce the trigger problem without postgres_fdw by setting "search_path=pg_catalog" ?
Yours,
Laurenz Albe