Thread: BUG #15263: pg_dump / psql failure. When loading,psql does not see function-based constraints or indices
BUG #15263: pg_dump / psql failure. When loading,psql does not see function-based constraints or indices
From
PG Bug reporting form
Date:
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.
Re: BUG #15263: pg_dump / psql failure. When loading, psql does notsee function-based constraints or indices
From
Peter Eisentraut
Date:
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
Re: BUG #15263: pg_dump / psql failure. When loading, psql does not see function-based constraints or indices
From
Tom Lane
Date:
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
Re: BUG #15263: pg_dump / psql failure. When loading, psql does not see function-based constraints or indices
From
"ceccareb@talusmusic.com"
Date:
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
Re: BUG #15263: pg_dump / psql failure. When loading, psql does notsee function-based constraints or indices
From
Peter Eisentraut
Date:
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
Re: BUG #15263: pg_dump / psql failure. When loading, psql does notsee function-based constraints or indices
From
firstname lastname
Date:
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
Attachment
Re: BUG #15263: pg_dump / psql failure. When loading, psql does notsee function-based constraints or indices
From
Alvaro Herrera
Date:
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
Re: BUG #15263: pg_dump / psql failure. When loading, psql does notsee function-based constraints or indices
From
"David G. Johnston"
Date:
In the end, a person should be able to copy his objects a different schema; therefore, one would not want to hard-code a schema name to every object.
This is basically the issue. By allowing schema to be flexible you expose your system to masquerading. A more secure, yet still reasonable, posture is to treat the namespace layout of the database as a fundamental property of the model. Where that starts to break down is dealing with multi-tenant situations and trying to have shared and not-shared schema components. Not sure what the good solutions are if your application wants to operate under that model - but that affects a minority of people and doesn't seem to be the case here.
David J.