Re: Problem with pg_dump -n schemaname

Interesting. I was able to reproduce this with just pg_dump -c (clear).

We _could_ move the SET search_path out of that loop but that isn't the
right solution. _printTocEntry() already has the _selectOutputSchema()
call we need. I have no idea what object might need the
_selectOutputSchema() there too.

The correct solution is to reset AH->currSchema if we we dropped a
schema. The logic is that if we dropped a schema, we don't know for
sure that search_path succeeded so we clear the variable so it is set
the next time an object is created.

Patch attached and applied. Attached are also good/bad dumps of the
same database. The patch adds the proper SET search_path.

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

Zoltan Boszormenyi wrote:
> Hi,
>
> we came across a problem when you want to dump only one schema.
> The ASCII output when loaded with psql into an empty database
> doesn't produce an identical schema to the original.
> The problem comes from this statement ordering:
>
> SET ... -- some initial DB parameters
> ...
> SET search_path = schemaname , pg_catalog;
> -- the above fails because no schema with this name exists
> -- as a consequence, the original search_path (e.g. "$user",
> public)
> -- is not modified
>
> DROP INDEX schemaname.index1;
> ...
> DROP TABLE schemaname.table1;
> DROP SCHEMA schemaname;
>
> CREATE SCHEMA schemaname;
> ALTER SCHEMA schemaname OWNER TO schemaowner;
>
> CREATE TABLE table1; -- note that it was DROPped with full name
> schemaname.table1
> ...
>
> So, because search_path is ' "$user", public ' for e.g. postgres,
> the tables are created in the public schema. Hence, I propose
> the attached patch which issues "SET search_path = ..." statements
> before the first CREATE TABLE stmt in their respective schema
> instead of before the first DROP command.
>
> The problem manifests only when you dump only one schema.
> The same problem exists in at least 8.0.3, 8.2.5 and last 8.3cvs.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +