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