Re: BUG #18621: postgres_fdw cannot work with tables that use schemaless functions in triggers

Yes, but as you can see above, the same insert works when connected
directly and does not work when connected via postgres_fdw.

Moreover, if I set search_path to the default value "$user", public ,
then everything starts working fine.

вт, 17 сент. 2024 г. в 14:19, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>:
>
> On Tue, 2024-09-17 at 05:28 +0000, PG Bug reporting form wrote:
> > Let's create a trigger on a remote server that uses different functions for
> > different users. postgres_fdw cannot perform an insert in this case. And the
> > remote server may be a production server, where there is no access to change
> > the trigger code so that it uses an explicit schema specification!
> >
> > [...]
> > create function multiply(i int) returns int language plpgsql as $body$
> > begin
> > return i * 1;
> > end;$body$;
> > create schema alice;
> > create function alice.multiply(i int) returns int language plpgsql as $body$
> > begin
> > return i * 2;
> > end;$body$;
> > create schema bob;
> > create function bob.multiply(i int) returns int language plpgsql as $body$
> > begin
> > return i * 3;
> > end;$body$;
> > create function remote_trigger() returns trigger language plpgsql as $body$
> > begin
> > if tg_when = 'BEFORE' and tg_op in ('INSERT', 'UPDATE') then
> > new.i = multiply(new.i);
> > new.t = current_user;
> > end if;
> > return case when tg_op = 'DELETE' then old else new end;
> > end;$body$;
> > create trigger remote_before_trigger before insert or update or delete on
> > remote for each row execute procedure remote_trigger();
> >
> > [...]
> >
> > create extension postgres_fdw schema fdw;
> > create server remote foreign data wrapper postgres_fdw options (dbname
> > 'remote');
> > create user mapping for current_user server remote options (user
> > 'remote');
> > create user mapping for alice server remote options (user 'remote');
> > create user mapping for bob server remote options (user 'remote');
> > create foreign table remote (i int, t text) server remote options
> > (schema_name 'public', table_name 'remote');
> > select * from remote;
> >
> > \connect "user=bob dbname=local"
> > insert into remote select i from generate_series(1, 10) i;
> >
> > ERROR: function multiply(integer) does not exist
> > HINT: No function matches the given name and argument types. You might need
> > to add explicit type casts.
> > CONTEXT: PL/pgSQL function remote.remote_trigger() line 3 at assignment
> > remote SQL command: INSERT INTO public.remote(i, t) VALUES ($1, $2)
>
> That is documented
> (https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-REMOTE-QUERY-EXECUTION-ENVIRONMENT)
>
> "In the remote sessions opened by postgres_fdw, the search_path parameter
> is set to just pg_catalog, so that only built-in objects are visible without
> schema qualification. This is not an issue for queries generated by
> postgres_fdw itself, because it always supplies such qualification.
> However, this can pose a hazard for functions that are executed on the
> remote server via triggers or rules on remote tables. For example, if a
> remote table is actually a view, any functions used in that view will be
> executed with the restricted search path. It is recommended to schema-qualify
> all names in such functions, or else attach SET search_path options (see
> CREATE FUNCTION) to such functions to establish their expected search path
> environment."
>
> So I don't see a bug here.
>
> Yours,
> Laurenz Albe