BUG #2512: pg_dump produces unrestorable output when table and serial sequence are not in the same schema - Mailing list pgsql-bugs

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
--