Thread: BUG #2512: pg_dump produces unrestorable output when table and serial sequence are not in the same schema
BUG #2512: pg_dump produces unrestorable output when table and serial sequence are not in the same schema
From
"Phil Frost"
Date:
The following bug has been logged online: Bug reference: 2512 Logged by: Phil Frost Email address: indigo@bitglue.com PostgreSQL version: 8.1.4 Operating system: Mac OS 10.4.7 Description: pg_dump produces unrestorable output when table and serial sequence are not in the same schema Details: Creating the database: test=> create schema private; test=> create table o(i serial primary key); test=> alter sequence o_i_seq set schema private; test=> insert into o default values; test=> insert into o default values; test=> select * from o; -- do things still work? i --- 1 2 (2 rows) -- does the default value for the table remain sane? test=> \d o Table "public.o" Column | Type | Modifiers --------+---------+------------------------------------------------------- i | integer | not null default nextval('private.o_i_seq'::regclass) Indexes: "o_pkey" PRIMARY KEY, btree (i) -- is pg_get_serial_sequence confused? no. test=> select pg_catalog.pg_get_serial_sequence('o', 'i'); pg_get_serial_sequence ------------------------ private.o_i_seq (1 row) =========================================== The dump contains the two lines: SET search_path = private, pg_catalog; SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('o', 'i'), 2, true); The problem is that search_path is set, and then pg_get_serial_sequence is called with an unqualified table name parameter. The error will be ERROR: relation "o" does not exist In fact it does exist, just not in a schema in search_path. The full dump: =========================================== -- -- PostgreSQL database dump -- SET client_encoding = 'SQL_ASCII'; SET check_function_bodies = false; SET client_min_messages = warning; -- -- Name: private; Type: SCHEMA; Schema: -; Owner: pfrost -- CREATE SCHEMA private; ALTER SCHEMA private OWNER TO pfrost; -- -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres -- COMMENT ON SCHEMA public IS 'Standard public schema'; SET search_path = public, pg_catalog; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: o; Type: TABLE; Schema: public; Owner: pfrost; Tablespace: -- CREATE TABLE o ( i serial NOT NULL ); ALTER TABLE public.o OWNER TO pfrost; SET search_path = private, pg_catalog; -- -- Name: o_i_seq; Type: SEQUENCE SET; Schema: private; Owner: pfrost -- SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('o', 'i'), 2, true); SET search_path = public, pg_catalog; -- -- Data for Name: o; Type: TABLE DATA; Schema: public; Owner: pfrost -- COPY o (i) FROM stdin; 1 2 \. -- -- Name: o_pkey; Type: CONSTRAINT; Schema: public; Owner: pfrost; Tablespace: -- ALTER TABLE ONLY o ADD CONSTRAINT o_pkey PRIMARY KEY (i); -- -- 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 --
Re: BUG #2512: pg_dump produces unrestorable output when table and serial sequence are not in the same schema
From
Tom Lane
Date:
"Phil Frost" <indigo@bitglue.com> writes: > Creating the database: > test=> create schema private; > test=> create table o(i serial primary key); > test=> alter sequence o_i_seq set schema private; I would argue that the bug is we allow you to do the above ;-). It's not really sane to move a serial sequence out of its owning table's schema (compare the situation for indexes). If you play with it you'll find that "alter table o set schema private" will move the sequence too ... but the above case isn't checked for. This ties into the long-running discussion on whether a serial column should be entirely a black box or not. Currently there are quite a few things you could do to the underlying sequence object that pg_dump would fail to dump/restore correctly. One school of thought says we'd be best off to forbid any direct manipulation of the sequence object. regards, tom lane