Skip site navigation (1) Skip section navigation (2)

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

From: "Phil Frost" <indigo(at)bitglue(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2512: pg_dump produces unrestorable output when table and serial sequence are not in the same schema
Date: 2006-07-03 18:19:09
Message-ID: 200607031819.k63IJ9pe093043@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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
--

Responses

pgsql-bugs by date

Next:From: Simon RiggsDate: 2006-07-03 20:21:48
Subject: Re: BUG #2511: violation of primary key on update with 2
Previous:From: Kris JurkaDate: 2006-07-03 15:43:15
Subject: Re: Diffrence between 8.0.3 and 8.1.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group