postgres_fdw, remote triggers and schemas

From: Thom Brown <thom(at)linux(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: postgres_fdw, remote triggers and schemas
Date: 2013-11-15 20:44:59
Message-ID: CAA-aLv49F0ZAMm4mwJk4NrQ9Rf=NhnvQgfe+vEOon5ZWueRRUw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Merlin Moncure 2013-11-15 20:50:04 Re: additional json functionality
Previous Message Andrew Dunstan 2013-11-15 20:37:25 Re: additional json functionality