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