On Fri, Aug 9, 2013 at 01:48:43AM -0400, Tom Lane wrote:
> Bruce Momjian <[email protected]> writes:
> > pg_dump goes to great lengths not to hard-code the schema name into
> > commands like CREATE TABLE, instead setting the search_path before
> > creating the table; these commands:
>
> > CREATE SCHEMA xx;
> > CREATE TABLE xx.test(x int);
>
> > generates this output:
>
> > SET search_path = xx, pg_catalog;
> > CREATE TABLE test (
> > x integer
> > );
>
> > If you dump a schema and want to reload it into another schema, you
> > should only need to update that one search_path line. However, later in
> > the dump file, we hardcode the schema name for setting the object owner:
>
> > ALTER TABLE xx.test OWNER TO postgres;
>
> > Could we use search_path here to avoid the schema designation?
>
> Perhaps, but that's not likely to reduce the number of places you have to
> edit, unless your dump is only one schema anyway.
>
> The practical difficulties involved can be seen by reading the comments
> and code for _getObjectDescription().
Yes, I looked at that. Seems _getObjectDescription() is only called
from _printTocEntry(), and that function has a call to
_selectOutputSchema() at the top, so we already know we have search_path
set to the proper schema.
The attached patch removes the unnecessary schema qualification for
ALTER OWNER, and the attached dump file show a two-schema dump that
restores just fine.
Basically, if we are going to use search_path to avoid schema
specification, we should do it in ALTER OWNER too.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
new file mode 100644
index cd7669b..0a79489
*** a/src/bin/pg_dump/pg_backup_archiver.c
--- b/src/bin/pg_dump/pg_backup_archiver.c
*************** _getObjectDescription(PQExpBuffer buf, T
*** 2906,2913 ****
strcmp(type, "TEXT SEARCH CONFIGURATION") == 0)
{
appendPQExpBuffer(buf, "%s ", type);
- if (te->namespace && te->namespace[0]) /* is null pre-7.3 */
- appendPQExpBuffer(buf, "%s.", fmtId(te->namespace));
/*
* Pre-7.3 pg_dump would sometimes (not always) put a fmtId'd name
--- 2906,2911 ----
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
--
-- Name: xx; Type: SCHEMA; Schema: -; Owner: postgres
--
CREATE SCHEMA xx;
ALTER SCHEMA xx OWNER TO postgres;
--
-- Name: yy; Type: SCHEMA; Schema: -; Owner: postgres
--
CREATE SCHEMA yy;
ALTER SCHEMA yy OWNER TO postgres;
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
SET search_path = xx, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: test2; Type: TABLE; Schema: xx; Owner: postgres; Tablespace:
--
CREATE TABLE test2 (
x integer
);
ALTER TABLE test2 OWNER TO postgres;
SET search_path = yy, pg_catalog;
--
-- Name: zz; Type: TABLE; Schema: yy; Owner: postgres; Tablespace:
--
CREATE TABLE zz (
x integer
);
ALTER TABLE zz OWNER TO postgres;
SET search_path = xx, pg_catalog;
--
-- Data for Name: test2; Type: TABLE DATA; Schema: xx; Owner: postgres
--
COPY test2 (x) FROM stdin;
\.
SET search_path = yy, pg_catalog;
--
-- Data for Name: zz; Type: TABLE DATA; Schema: yy; Owner: postgres
--
COPY zz (x) 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
--
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
