Date: Mon, 18 Dec 2023 11:19:24 +0100
MIME-Version: 1.0
Message-ID: <de028f0738e33eae284befcd687b41e1@mail.eclipso.de>
From: " " <wilma.wantren@eclipso.de>
Subject: Re: BUG #18249: pg_dump/pg_restore single schema with function1
 calling
	function2
Reply-To: " " <wilma.wantren@eclipso.de>
To: "Wetmore, Matthew (CTR)" <Matthew.Wetmore@express-scripts.com>
Cc: <wilma.wantren@web.de>, <pgsql-bugs@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Archived-At: 
 <https://www.postgresql.org/message-id/de028f0738e33eae284befcd687b41e1%40mail.eclipso.de>
Precedence: bulk

Thanks a lot for your swift response!
In oder to make this call in function1=20
    begin return str || 'hello' || function2(str);
work independently of the session search_path, either the line must read
    begin return str || 'hello' || dumper.function2(str);
or the function must get a search_path:
    alter function dumper.function1 set search_path to dumper;

Neither of these will be changed when the schema is renamed. I am not criti=
cizing this, of course it will not be changed! But this also means that I h=
ave to know that I must change the search_path of function function1 (or ev=
en change the function body) after renaming the schema. This is unpleasant,=
 especially because renaming the schema is otherwise so wonderfully unprobl=
ematic and simple.
Is it possible to set the search_path of a function to "the schema of the f=
unction", e.g. like this:
    alter function dumper.function1 set search_path to function_schema();
Then the search_path would still be correct after renaming the schema.


--- Urspr=C3=BCngliche Nachricht ---
Von: "Wetmore, Matthew (CTR)" <Matthew.Wetmore@express-scripts.com>
Datum: 15.12.2023 15:29:38
An: "wilma.wantren@web.de" <wilma.wantren@web.de>,         "pgsql-bugs@list=
s.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Betreff: BUG #18249: pg_dump/pg_restore single schema with function1 callin=
g  function2

Not to be trite, but schema qualifying your function/tables would have prev=
ented
this.

create or replace function MYSCHEMA.function1(str varchar) returns varchar
as $$ begin return str || 'hallo' || function2(str); end; $$ language plpgs=
ql
strict immutable;

relying on search_path is a dangerous games.  Be specific in your SQL and
you'll get better long term results.

Then if the schema name changes, everything downstream changes too.  Or you
can edit the dump.sql


-----Original Message-----
From: PG Bug reporting form <noreply@postgresql.org>=20
Sent: Friday, December 15, 2023 1:09 AM
To: pgsql-bugs@lists.postgresql.org
Cc: wilma.wantren@web.de
Subject: [EXTERNAL] BUG #18249: pg_dump/pg_restore single schema with funct=
ion1
calling function2


The following bug has been logged on the website:

Bug reference:      18249
Logged by:          Wilma Wantren
Email address:      wilma.wantren@web.de
PostgreSQL version: 13.4
Operating system:   All
Description:       =20

The company I work for used to only use Oracle databases for their program
and now also supports PostgreSQL.
With Postgres, we create a database, a user and a schema, which all have
the same name. The name is chosen by the customers.
Nevertheless, the customers would like to export the data and definitions
and import them under a new name.
This works well by using the -O (no owner) option for pg_dump/pg_restore
and only exporting and importing the schema. After the import the schema
is renamed. So far so simple and easy!
But there is one problem: we have a database function function1, which call=
s
another function function2. The call is made without a schema prefix becaus=
e
these are just two functions that we make available to the customers and
we don't think an extra schema for these two functions is worthwhile. Howev=
er,

function1 is used in an index and the restore of the index fails if there
is data in the corresponding table column. This is because pg_dump/pg_resto=
re
set the search_path to '' when restoring, which means that function1 cannot
find the function2.
Is there any way to solve this? It is not a big problem and we can tell the
customer to ignore the error message and recreate the indices after the imp=
ort,
but it would be nicer without an error message.

Here are the steps to reproduce:
Contents of file "create_dumper.sql":
create user dumper;
create database dumper owner dumper;
alter user dumper set search_path to dumper; \c dumper dumper; create schem=
a
dumper; create or replace function function2(str varchar) returns varchar
as $$ begin return str || str; end; $$ language plpgsql strict immutable;


create or replace function function1(str varchar) returns varchar as $$ beg=
in
return str || 'hallo' || function2(str); end; $$ language plpgsql strict
immutable;

create table table1(str varchar not null); insert into table1 values('test'=
);
create index index1 on table1(function1(str));

Call with
psql -f create_dumper.sql

Export:
pg_dump -h localhost -p 5432 -U dumper --schema dumper -O -f dumper.sql

Contents of file "create_restorer.sql":
create user restorer;
create database restorer owner restorer;

Call with:
psql -f create_restorer.sql

Import:
psql restorer restorer -f dumper.sql


________________________________________________________
Your E-Mail. Your Cloud. Your Office. eclipso Mail & Cloud. https://www.ecl=
ipso.de