BUG #15122: can't import data if table has a constraint with a function calling another function

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: asier(dot)lostale(at)openbravo(dot)com
Subject: BUG #15122: can't import data if table has a constraint with a function calling another function
Date: 2018-03-20 09:31:03
Message-ID: 152153826367.11956.8092048336300020216@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

The following bug has been logged on the website:

Bug reference: 15122
Logged by: Asier Lostalé
Email address: asier(dot)lostale(at)openbravo(dot)com
PostgreSQL version: 9.3.22
Operating system: ubuntu
Description:

Using only public schema, data in tables that use in check constraints
functions that invoke other functions does not get imported with pg_restore
after it was dumped with pg_dump. But if functions in check constraints do
not invoke other functions, data is correctly imported.

I have noted this behavior after minor upgrading from 9.3.22 to 9.3.23 and
from 9.4.16 to 9.4.17; in 9.3.22 and 9.3.17 it worked fine.

I understand is due to changes in search_path
(https://bucardo.org/postgres_all_versions.html#version_9.3.22).

But it's unclear to me why having one level public functions is allowed but
it is not those functions to invoke other ones. It looks inconsistent.

For example, having this structure and data:

CREATE OR REPLACE FUNCTION is_even(n integer) RETURNS boolean AS $BODY$
BEGIN
return n%2 = 0;
END ; $BODY$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION is_even_positive(n integer) RETURNS boolean AS
$BODY$
BEGIN
return is_even(n) and n > 0;
END ; $BODY$ LANGUAGE plpgsql;

CREATE TABLE test_check (
n integer
CONSTRAINT even_chk CHECK (is_even(n)));

CREATE TABLE test_check2(
n integer
CONSTRAINT even_positive_chk CHECK (is_even_positive(n)));

insert into test_check values (2);
insert into test_check values (-2);
insert into test_check2 values (2);
Exporting it with:

pg_dump -h localhost -p 5432 -F c -b -v -f test.dmp test -U test
And importing it in a new database:

$ pg_restore -d test2 -U test -v test.dmp -h localhost
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA public"
pg_restore: creating EXTENSION "plpgsql"
pg_restore: creating COMMENT "EXTENSION plpgsql"
pg_restore: creating FUNCTION "public.is_even(integer)"
pg_restore: creating FUNCTION "public.is_even_positive(integer)"
pg_restore: creating TABLE "public.test_check"
pg_restore: creating TABLE "public.test_check2"
pg_restore: processing data for table "public.test_check"
pg_restore: processing data for table "public.test_check2"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2035; 0 7784774 TABLE DATA
test_check2 tad
pg_restore: [archiver (db)] COPY failed for table "test_check2": ERROR:
function is_even(integer) does not exist
LINE 1: SELECT is_even(n) and n > 0
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
QUERY: SELECT is_even(n) and n > 0
CONTEXT: PL/pgSQL function public.is_even_positive(integer) line 3 at
RETURN
COPY test_check2, line 1: "2"
pg_restore: creating ACL "SCHEMA public"
WARNING: errors ignored on restore: 1

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andrew Gierth 2018-03-20 10:13:53 Re: BUG #15122: can't import data if table has a constraint with a function calling another function
Previous Message Martin Liška 2018-03-19 21:41:11 Re: BUG #15121: Multiple UBSAN errors

Browse pgsql-hackers by date

  From Date Subject
Next Message Rushabh Lathia 2018-03-20 09:36:29 Re: INOUT parameters in procedures
Previous Message Andrey Borodin 2018-03-20 09:29:41 Re: Online enabling of checksums