Thread: search_path in pg_dump output.
Hi,
we are using pg_dump output to compare the schema consistency. But after upgrade to postgres 11.5 we are seeing different behavior of pg_dump intermittently. As per the https://github.com/postgres/postgres/commit/582edc369cdbd348d68441fc50fa26a84afd0c1a pg_dump should reset the schema path to empty and generate fully qualified table names which is not happening in intermittent case. Same can be seen on multiple different servers and many users are impacted due to this. Please help me to understand this behavior difference. Also, If possible please let me know if it is bug or some configuration issue.
we have default search_path value in postgresql.conf file.
issue appear 1/5
Command used to dump schema:
pg_dump.exe --file=D:\ schmdmp_tmp --no-owner --no-tablespaces --schema=hpdpidb_app --schema-only --password --username=xyz --port=xxxx --host=localhost db_xyz
In normal cases:
-- Dumped from database version 11.5
-- Dumped by pg_dump version 11.5
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
CREATE VIEW hpdpidb_app.dp_cartridges AS
SELECT crt.uuid
FROM hpdpidb_app.dp_medmng_cartridge crt;
Intermittently:
-- Dumped from database version 11.5
-- Dumped by pg_dump version 11.5
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', 'hpdpidb_app', false);
CREATE VIEW hpdpidb_app.dp_cartridges AS
SELECT crt.uuid
FROM dp_medmng_cartridge crt;
Thanks,
Neeraj
On 3/28/21 11:00 PM, NEERAJ BANSAL wrote: > Hi, > > we are using pg_dump output to compare the schema consistency. But after > upgrade to postgres 11.5 we are seeing different behavior of pg_dump > intermittently. As per the > https://github.com/postgres/postgres/commit/582edc369cdbd348d68441fc50fa26a84afd0c1a > <https://github.com/postgres/postgres/commit/582edc369cdbd348d68441fc50fa26a84afd0c1a> > pg_dump should reset the schema path to empty and generate fully > qualified table names which is not happening in intermittent case. Same > can be seen on multiple different servers and many users are impacted > due to this. Please help me to understand this behavior difference. > Also, If possible please let me know if it is bug or some configuration > issue. First, the current version of 11 is 11.11. In a quick search of the release notes from 11.6 --> 11.11 I don't see anything obviously on point, still it would worth it to try the latest version. Second, where is the Postgres code/package coming from? > > > we have default search_path value in postgresql.conf file. > issue appear 1/5 > *Command used to dump schema:* > pg_dump.exe --file=D:\ schmdmp_tmp --no-owner --no-tablespaces > --schema=hpdpidb_app --schema-only --password --username=xyz --port=xxxx > --host=localhost db_xyz > > *In normal cases:* > > -- Dumped from database version 11.5 > > -- Dumped by pg_dump version 11.5 > > SET statement_timeout = 0; > > SET lock_timeout = 0; > > SET idle_in_transaction_session_timeout = 0; > > SET client_encoding = 'UTF8'; > > SET standard_conforming_strings = on; > > SELECT pg_catalog.set_config('search_path', '', false); > > > CREATE VIEW hpdpidb_app.dp_cartridges AS > > SELECT crt.uuid > > FROM hpdpidb_app.dp_medmng_cartridge crt; > > *Intermittently:* > > -- Dumped from database version 11.5 > > -- Dumped by pg_dump version 11.5 > > SET statement_timeout = 0; > > SET lock_timeout = 0; > > SET idle_in_transaction_session_timeout = 0; > > SET client_encoding = 'UTF8'; > > SET standard_conforming_strings = on; > > SELECT pg_catalog.set_config('search_path', 'hpdpidb_app', false); > > > CREATE VIEW hpdpidb_app.dp_cartridges AS > > SELECT crt.uuid > > FROM dp_medmng_cartridge crt; > > Thanks, > > Neeraj > -- Adrian Klaver adrian.klaver@aklaver.com