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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
--
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2006-07-03 20:21:48 | Re: BUG #2511: violation of primary key on update with 2 |
Previous Message | Kris Jurka | 2006-07-03 15:43:15 | Re: Diffrence between 8.0.3 and 8.1.3 |