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
