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(dot)klaver(at)aklaver(dot)com