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/[email protected]
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
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
