postgresql_fdw issues with triggers on the foreign tables

[Eelke Klein]

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.

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.

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.


The "local" database is version 9.3.3 x64 windows from EnterpriseDB
The foreign database is version 9.3.2 x64 freebsd 10 clang3.3

Regards, Eelke

[Albe Laurenz]

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/***@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

[Igor Neyman]

From: pgsql-general-***@postgresql.org [mailto:pgsql-general-***@postgresql.org] On Behalf Of Eelke Klein
Sent: Wednesday, July 23, 2014 10:31 AM
To: pgsql-***@postgresql.org
Subject: [GENERAL] postgresql_fdw issues with triggers on the foreign tables


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.
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.
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.

The "local" database is version 9.3.3 x64 windows from EnterpriseDB
The foreign database is version 9.3.2 x64 freebsd 10 clang3.3

Regards, Eelke


So, as a workaround, if you already catch “NEW.id IS NULL” in BEFORE INSRT trigger, you could assign NEW.id value nextval('...'::regclass) right then and there explicitly in such cases.

Regards,
Igor Neyman