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;
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 --
Bruce Momjian <bruce@momjian.us> writes:
> The correct solution is to reset AH->currSchema if we we dropped a
> schema.
No it isn't. This is an ugly, brute-force, misleadingly commented fix.
For one thing, why reset currSchema if it doesn't match the schema
we dropped? And what if we only drop some member objects and not the
schema itself? (That may not be possible right now, but it certainly
seems like a likely scenario in future as we extend pg_dump's selective
dump facilities.)
The whole thing is actually pretty subtle. Zoltan's original proposed
fix is no good because we do have to set the search path to ensure that
component names within a DROP will be resolved properly. Consider
create schema ss;
create type ss.complex as (x real, y real);
create function ss.realpart(ss.complex) returns real as
'select $1.x' language sql strict immutable;
pg_dump -c will emit stuff like this:
SET search_path = ss, pg_catalog;
DROP FUNCTION ss.realpart(complex);
DROP TYPE ss.complex;
DROP SCHEMA ss;
That is, the primary name of a DROP target will be fully qualified,
but other names within the command not necessarily. If we didn't emit
the "SET search_path" then the DROP FUNCTION could fail because
"complex" might not be in the default search path.
The fact that schema ss might not exist is one of the scenarios that
we need to qualify the primary name in the DROP to protect against.
It's possible say that "complex" would get mis-resolved as some other
type "complex" in some other schema, but it doesn't matter since the
qualified function name will surely not match anything else.
What I think we ought to do is just reset currSchema once, after the
DROP phase is completed, whether or not any schemas were dropped.
This will be considerably more bulletproof in partial-restore
situations.
Will go fix.
regards, tom lane
Tom Lane wrote: > The fact that schema ss might not exist is one of the scenarios that > we need to qualify the primary name in the DROP to protect against. > It's possible say that "complex" would get mis-resolved as some other > type "complex" in some other schema, but it doesn't matter since the > qualified function name will surely not match anything else. > > What I think we ought to do is just reset currSchema once, after the > DROP phase is completed, whether or not any schemas were dropped. > This will be considerably more bulletproof in partial-restore > situations. > > Will go fix. OK. I did consider that option but was unsure of the ramifications. The problem is definitely subtle. -- 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. +