Thread: Problem with pg_dump -n schemaname

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.

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

--- postgresql-8.2.5.orig/src/bin/pg_dump/pg_backup_archiver.c    2007-08-06 03:38:24.000000000 +0200
+++ postgresql-8.2.5/src/bin/pg_dump/pg_backup_archiver.c    2007-11-16 11:00:46.000000000 +0100
@@ -241,9 +241,6 @@
             {
                 /* We want the schema */
                 ahlog(AH, 1, "dropping %s %s\n", te->desc, te->tag);
-                /* Select owner and schema as necessary */
-                _becomeOwner(AH, te);
-                _selectOutputSchema(AH, te->namespace);
                 /* Drop it */
                 ahprintf(AH, "%s", te->dropStmt);
             }
@@ -275,6 +272,10 @@
         {
             ahlog(AH, 1, "creating %s %s\n", te->desc, te->tag);

+            /* Select owner and schema as necessary */
+            _becomeOwner(AH, te);
+            _selectOutputSchema(AH, te->namespace);
+
             _printTocEntry(AH, te, ropt, false, false);
             defnDumped = true;