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 <[EMAIL PROTECTED]> 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
--
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
