Re: search_path in pg_dump output.

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