Re: postgres_fdw, remote triggers and schemas

Thom Brown <thom(at)linux(dot)com> writes:
> I've observed an issue whereby a parent table with a trigger that
> redirects inserts to a child table fails to run the trigger
> successfully if written to using a foreign table:

That trigger is making unsafe assumptions about what search_path
it's run under. If you don't want to schema-qualify the reference
to "child", consider attaching a "SET search_path" clause to the
trigger function definition.

> Is this unintended, or is it something users should fix themselves by
> being explicit about relation schemas in trigger functions? Should
> the schema search path instead pick up whatever the default would be
> for the user being used for the connection?

postgres_fdw intentionally runs the remote session with a very minimal
search_path (I think just pg_catalog, in fact). I would argue that
any trigger that breaks because of that is broken anyway, since it
would fail --- possibly with security implications --- if some ordinary
user modified the search path.

regards, tom lane