[BUGS] BUG #3854: pg_dump dumps renamed primary key constraints by their old name

[Milen A. Radev]

The following bug has been logged online:

Bug reference:      3854
Logged by:          Milen A. Radev
Email address:      [EMAIL PROTECTED]
PostgreSQL version: 8.2.6
Operating system:   Debian Etch
Description:        pg_dump dumps renamed primary key constraints by their
old name
Details: 

After a table and the implicit index related to its primary key are renamed,
pg_dump still creates a statement for the primary key using its old name.

Most of the time that's probably harmless but not when there are clustered
tables.


Steps to reproduce the problem:

===================================================

dev:~# /usr/local/postgresql-8.2.6/bin/psql -U postgres -p 6543
Welcome to psql 8.2.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# create table x (x_id integer primary key, foo integer);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x_pkey" for
table "x"
CREATE TABLE
postgres=# \d x
       Table "public.x"
 Column |  Type   | Modifiers
--------+---------+-----------
 x_id   | integer | not null
 foo    | integer |
Indexes:
    "x_pkey" PRIMARY KEY, btree (x_id)

postgres=# CLUSTER x_pkey ON x;
CLUSTER
postgres=# \d x
       Table "public.x"
 Column |  Type   | Modifiers
--------+---------+-----------
 x_id   | integer | not null
 foo    | integer |
Indexes:
    "x_pkey" PRIMARY KEY, btree (x_id) CLUSTER

postgres=# alter table x rename to a;
ALTER TABLE
postgres=# alter index x_pkey rename to a_pkey;
ALTER INDEX
postgres=# \d a
       Table "public.a"
 Column |  Type   | Modifiers
--------+---------+-----------
 x_id   | integer | not null
 foo    | integer |
Indexes:
    "a_pkey" PRIMARY KEY, btree (x_id) CLUSTER

postgres=# \q
dev:~# /usr/local/postgresql-8.2.6/bin/pg_dump -U postgres -p 6543 postgres
--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- 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: a; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--

CREATE TABLE a (
    x_id integer NOT NULL,
    foo integer
);


ALTER TABLE public.a OWNER TO postgres;

--
-- Data for Name: a; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY a (x_id, foo) FROM stdin;
\.


--
-- Name: x_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres;
Tablespace:
--

ALTER TABLE ONLY a
    ADD CONSTRAINT x_pkey PRIMARY KEY (x_id);

ALTER TABLE a CLUSTER ON a_pkey;


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

dev:~#

===================================================




The problematic statements are:

ALTER TABLE ONLY a
    ADD CONSTRAINT x_pkey PRIMARY KEY (x_id);

ALTER TABLE a CLUSTER ON a_pkey;

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster