BUG #2512: pg_dump produces unrestorable output when table and serial sequence are not in the same schema

The following bug has been logged online:

Bug reference: 2512
Logged by: Phil Frost
Email address: indigo(at)bitglue(dot)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
--

"Phil Frost" <indigo(at)bitglue(dot)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