Hi,
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:
Example:
Database 1:
CREATE TABLE parent (id int, content text);
CREATE TABLE child () INHERITS (parent);
CREATE OR REPLACE FUNCTION redirect_func () RETURNS trigger AS $$
BEGIN INSERT INTO child VALUES (NEW.*); RETURN NULL;
END; $$ language plpgsql;
CREATE TRIGGER parent_trig BEFORE INSERT ON parent FOR EACH ROW EXECUTE PROCEDURE redirect_func();
Database 2:
CREATE FOREIGN TABLE foreign_parent (id int, content text) SERVER local_pg_db OPTIONS (table_name 'parent');
Then...
postgres=# INSERT INTO foreign_parent VALUES (2, 'test2');
ERROR: relation "child" does not exist
CONTEXT: Remote SQL command: INSERT INTO public.parent(id, content)
VALUES ($1, $2)
PL/pgSQL function public.redirect_func() line 3 at SQL statement
I've run that remote SQL command in isolation on database 1 and it
completes successfully.
It appears that this is caused by the relation reference in the
trigger function not being explicit about the schema, as if I remove
"public" from the search_path, I can generate this issue on database 1
with the same statement. The search_path only contains 'pg_catalog'
on the foreign table connection.
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?
Thom
