Re: Problem with pg_dump -n schemaname - Mailing list pgsql-patches

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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: src/bin/pg_dump/pg_backup_archiver.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_backup_archiver.c,v
retrieving revision 1.149
diff -c -c -r1.149 pg_backup_archiver.c
*** src/bin/pg_dump/pg_backup_archiver.c    15 Nov 2007 21:14:41 -0000    1.149
--- src/bin/pg_dump/pg_backup_archiver.c    24 Nov 2007 17:31:07 -0000
***************
*** 245,250 ****
--- 245,265 ----
                  _selectOutputSchema(AH, te->namespace);
                  /* Drop it */
                  ahprintf(AH, "%s", te->dropStmt);
+                 if (strcmp(te->desc, "SCHEMA") == 0)
+                 {
+                     /*
+                      * If we dropped a schema, we know we are going to be
+                      * creating one later so don't remember the current one
+                      * so we try later. The previous 'search_path' setting
+                      * might have failed because the schema didn't exist
+                      * (and now it certainly doesn't exist), so force
+                      * search_path to be set as part of the next operation
+                      * and it might succeed.
+                      */
+                     if (AH->currSchema)
+                         free(AH->currSchema);
+                     AH->currSchema = strdup("");
+                 }
              }
          }
      }
--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = ss, pg_catalog;

DROP TABLE ss.x;
DROP SCHEMA tt;
DROP SCHEMA ss;
DROP SCHEMA public;
--
-- Name: public; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA public;


ALTER SCHEMA public OWNER TO postgres;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'standard public schema';


--
-- Name: ss; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA ss;


ALTER SCHEMA ss OWNER TO postgres;

--
-- Name: tt; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA tt;


ALTER SCHEMA tt OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: x; Type: TABLE; Schema: ss; Owner: postgres; Tablespace:
--

CREATE TABLE x (
    y integer
);


ALTER TABLE ss.x OWNER TO postgres;

--
-- Data for Name: x; Type: TABLE DATA; Schema: ss; Owner: postgres
--

COPY x (y) FROM stdin;
\.


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--

--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = ss, pg_catalog;

DROP TABLE ss.x;
DROP SCHEMA tt;
DROP SCHEMA ss;
DROP SCHEMA public;
--
-- Name: public; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA public;


ALTER SCHEMA public OWNER TO postgres;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'standard public schema';


--
-- Name: ss; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA ss;


ALTER SCHEMA ss OWNER TO postgres;

--
-- Name: tt; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA tt;


ALTER SCHEMA tt OWNER TO postgres;

SET search_path = ss, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: x; Type: TABLE; Schema: ss; Owner: postgres; Tablespace:
--

CREATE TABLE x (
    y integer
);


ALTER TABLE ss.x OWNER TO postgres;

--
-- Data for Name: x; Type: TABLE DATA; Schema: ss; Owner: postgres
--

COPY x (y) FROM stdin;
\.


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--