Thread: BUG #15263: pg_dump / psql failure. When loading,psql does not see function-based constraints or indices

The following bug has been logged on the website:

Bug reference:      15263
Logged by:          Brian Ceccarelli
Email address:      ceccareb@talusmusic.com
PostgreSQL version: 10.4
Operating system:   Red Hat Enterprise Linux 7.5
Description:

This problem is easy to duplicate.

I have a database with a table with a function-based check constraint.
The database has another table with a function-based index.
I dump with COPY statements (the default).

When I load the database from the dump I just created, loading (via psql)
logs error messages.  Postgres cannot find functions inside the function the
constraint or index invokes.   You will see.   The following zip file
contains a schema that caused the problem.   Just create a database and run
dumpfailureexample.sql.    pg_dump the database.   psql to load the
database.   The load log is dumpfailureexample.log.

http://talussoftware.com/docs/dumpfailure.zip

------------------------------

I discovered this problem when I dumped a Postgres 9.6.2 DB to a 10.4 DB.
When I dump using 9's version of pg_dump, the subsequent load works.    When
I dump using 10's pg_dump, the subsequent load fails.

On 06.07.18 18:19, PG Bug reporting form wrote:
> I have a database with a table with a function-based check constraint.
> The database has another table with a function-based index.
> I dump with COPY statements (the default).
> 
> When I load the database from the dump I just created, loading (via psql)
> logs error messages.  Postgres cannot find functions inside the function the
> constraint or index invokes.

I think this and your subsequent report are all instances of the problem
that pg_dump cannot see into the function body to check what database
objects it depends on, so it cannot produce a working ordering of the
objects.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> On 06.07.18 18:19, PG Bug reporting form wrote:
>> When I load the database from the dump I just created, loading (via psql)
>> logs error messages.  Postgres cannot find functions inside the function the
>> constraint or index invokes.

> I think this and your subsequent report are all instances of the problem
> that pg_dump cannot see into the function body to check what database
> objects it depends on, so it cannot produce a working ordering of the
> objects.

That's one possible issue, but I think a more likely cause is the recent
security-driven changes in the search_path that the restore script runs
with.  If the function is relying on unqualified references to functions
that aren't in pg_catalog, it'll fail.  (This theory explains why things
were okay with old copies of pg_dump.)

            regards, tom lane

Seems like a reasonable diagnosis.   Apparently someone introduced this problem into 10.x.  9 works fine.

Is there anything I can do?   Or do know if someone is trying to fix this one?

Thanks!

Brian Ceccarelli
919-815-0126

> On Jul 7, 2018, at 3:14 AM, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:
>
>> On 06.07.18 18:19, PG Bug reporting form wrote:
>> I have a database with a table with a function-based check constraint.
>> The database has another table with a function-based index.
>> I dump with COPY statements (the default).
>>
>> When I load the database from the dump I just created, loading (via psql)
>> logs error messages.  Postgres cannot find functions inside the function the
>> constraint or index invokes.
>
> I think this and your subsequent report are all instances of the problem
> that pg_dump cannot see into the function body to check what database
> objects it depends on, so it cannot produce a working ordering of the
> objects.
>
> --
> Peter Eisentraut              http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 07.07.18 16:50, ceccareb@talusmusic.com wrote:
> Seems like a reasonable diagnosis.   Apparently someone introduced this problem into 10.x.  9 works fine.  
> 
> Is there anything I can do?   Or do know if someone is trying to fix this one?

It looks like you might need to fix up your functions to handle schema
search path changes.  Either do SET search_path inside the function or
attached to the function definition, or explicitly schema-qualify the
function calls.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Peter,

Both of your suggestions work.    I opted to change the search path in the dump file.  The alternative is is to prefix
"public"to hundreds of functions.   I do not want to do that.  Too much work.    In the end, a person should be able to
copyhis objects a different schema; therefore, one would not want to hard-code a schema name to every object.
 

Do you consider #15263 a bug?    Or starting with 10.x, is this expected behavior?

I attached a zip file.   runtest.sh duplicates the problem automatically for you.   And you are correct, when I create
thedatabase with schema_with_explicit_public.sql, psql loads the dump correctly.
 


Thanks for your help,

Brian

> On July 13, 2018 at 3:19 AM Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:
> 
> 
> On 07.07.18 16:50, ceccareb@talusmusic.com wrote:
> > Seems like a reasonable diagnosis.   Apparently someone introduced this problem into 10.x.  9 works fine.  
> > 
> > Is there anything I can do?   Or do know if someone is trying to fix this one?
> 
> It looks like you might need to fix up your functions to handle schema
> search path changes.  Either do SET search_path inside the function or
> attached to the function definition, or explicitly schema-qualify the
> function calls.
> 
> -- 
> Peter Eisentraut              http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2018-Jul-18, firstname lastname wrote:

> Peter,
> 
> Both of your suggestions work.    I opted to change the search path in
> the dump file.  The alternative is is to prefix "public" to hundreds
> of functions.   I do not want to do that.  Too much work.

Hmm, surely you can just do "ALTER FUNCTION ... SET search_path" in a DO
block that processes all functions, or something to that effect.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services