Thread: [BUGS] BUG #14649: Function Namespace Resolution Bug
The following bug has been logged on the website:
Bug reference: 14649
Logged by: Jeremy Cowgar
Email address: jeremy@cowgar.com
PostgreSQL version: 9.6.1
Operating system: Debian
Description:
We ran across an issue restoring our database recently and have tracked it
down. The following SQL exposes the bug. It appears to deal with a confusion
of schema namespaces, thus the "create schema g” line is vital to expose
this problem.
In short, when a CHECK on a column in a different schema references a
function in public that references another function in public implicitly,
there is confusion. The workaround is to prefix the function calls with the
schema.
Here is the SQL to expose the problem. Below I have included the commands
necessary to create the database, import the data, then dump and restore.
The restore is where we are seeing the bug.
—— SQL ——
create or replace function is_blank(data text) returns boolean language plpgsql as $$
begin return false;
end $$;
create or replace function has_content(data text) returns boolean language plpgsql as $$
begin return not is_blank(data);
end $$;
create schema g;
create table g.people ( id serial, name varchar(50) not null, check (has_content(name))
);
insert into g.people (name) values ('John’);
—— END SQL ——
—— TERMINAL OUTPUT ——
$ createdb -O postgres -U postgres dummy && psql -U postgres dummy <
dummy.sql
CREATE FUNCTION
CREATE FUNCTION
CREATE SCHEMA
CREATE TABLE
INSERT 0 1
$ createdb -U postgres dummy_restore && pg_dump -U postgres -Fc -Z9 dummy |
pg_restore -U postgres -d dummy_restore
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2367; 0 1055467702 TABLE
DATA people postgres
pg_restore: [archiver (db)] COPY failed for table "people": ERROR: function
is_blank(text) does not exist
LINE 1: SELECT not is_blank(data)
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
QUERY: SELECT not is_blank(data)
CONTEXT: PL/pgSQL function public.has_content(text) line 3 at RETURN
COPY people, line 1: "1 John"
WARNING: errors ignored on restore: 1
—— END TERMINAL OUTPUT ——
Thank you,
Jeremy Cowgar
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
jeremy@cowgar.com writes:
> In short, when a CHECK on a column in a different schema references a
> function in public that references another function in public implicitly,
> there is confusion. The workaround is to prefix the function calls with the
> schema.
I don't see any PG bug here. If you don't schema-qualify the function
reference, then it is dependent on the current search_path, and pg_dump/
pg_restore have their own ideas about how to set search_path. Even if
those two somehow magically intuited what search_path you're expecting,
this coding would still be fragile since some other user might use a
different search_path than you while accessing the table. The schema
qualification isn't a "workaround", it's just good coding practice.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Why does it find the initial function just fine in the public namespace, but the method it calls it can not find, which isin the same namespace as the calling function? Jeremy Cowgar > On May 12, 2017, at 2:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > jeremy@cowgar.com writes: >> In short, when a CHECK on a column in a different schema references a >> function in public that references another function in public implicitly, >> there is confusion. The workaround is to prefix the function calls with the >> schema. > > I don't see any PG bug here. If you don't schema-qualify the function > reference, then it is dependent on the current search_path, and pg_dump/ > pg_restore have their own ideas about how to set search_path. Even if > those two somehow magically intuited what search_path you're expecting, > this coding would still be fragile since some other user might use a > different search_path than you while accessing the table. The schema > qualification isn't a "workaround", it's just good coding practice. > > regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Jeremy Cowgar <jeremy@cowgar.com> writes:
> Why does it find the initial function just fine in the public namespace, but the method it calls it can not find,
whichis in the same namespace as the calling function?
pg_dump is smart enough to schema-qualify that one. If you look at
the actual dump output for the table, it looks like
SET search_path = g, pg_catalog;
...
CREATE TABLE people ( id integer NOT NULL, name character varying(50) NOT NULL, CONSTRAINT people_name_check
CHECK(public.has_content((name)::text))
);
However, editing the body text of your function is beyond pg_dump's
competence.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
I didn’t realize that pg_dump alters the search path. Couldn’t pg_dump simply append the default search path to the end ofit’s custom setting? That should solve the problem as well? We have not changed our search path and it seems the defaultis “$user”, public. Jeremy Cowgar > On May 12, 2017, at 2:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Jeremy Cowgar <jeremy@cowgar.com> writes: >> Why does it find the initial function just fine in the public namespace, but the method it calls it can not find, whichis in the same namespace as the calling function? > > pg_dump is smart enough to schema-qualify that one. If you look at > the actual dump output for the table, it looks like > > SET search_path = g, pg_catalog; > ... > CREATE TABLE people ( > id integer NOT NULL, > name character varying(50) NOT NULL, > CONSTRAINT people_name_check CHECK (public.has_content((name)::text)) > ); > > However, editing the body text of your function is beyond pg_dump's > competence. > > regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Jeremy Cowgar <jeremy@cowgar.com> writes:
> I didn’t realize that pg_dump alters the search path. Couldn’t pg_dump
> simply append the default search path to the end of it’s custom setting?
There's no particularly good reason to believe that there's a single
"default" search_path that would apply to all usages in a given database.
Even if there were, sticking another schema in front of it wouldn't
produce the same results, ie if you have code that fails when the
search_path isn't "f, g, h", it may well fail when the search_path is
"g, f, g, h", because schema g would win ambiguous references over
schema f.
We could maybe make this work if pg_dump never touched the search_path
at all, but that would be a quite subtle change creating its own set of
hazards.
The long and the short of it is that code in CHECK constraints (or
triggers) is well advised not to depend on any particular search_path;
blaming pg_dump for exposing that is just shooting the messenger.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs