BUG #2299: pg_dump error w/ renamed primary keys

Started by Gavin Scottabout 20 years ago2 messagesbugs
Jump to latest
#1Gavin Scott
gavin@planetacetech.com

The following bug has been logged online:

Bug reference: 2299
Logged by: Gavin Scott
Email address: gavin@planetacetech.com
PostgreSQL version: 8.1.3
Operating system: fedora core 4
Description: pg_dump error w/ renamed primary keys
Details:

If you rename a primary key, pg_dump tries to recreate it with the original
name. Here is how to recreate:

[postgres81@boll ~]$ createdb bug
CREATE DATABASE
[postgres81@boll ~]$ psql bug << EOF

CREATE TABLE foo ( id integer );
ALTER TABLE foo ADD CONSTRAINT foo_pkey PRIMARY KEY (id);
ALTER TABLE foo_pkey RENAME TO bar_pkey;
EOF

CREATE TABLE
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "foo_pkey"
for table "foo"
ALTER TABLE
ALTER TABLE
[postgres81@boll ~]$ echo '\d foo' | psql bug
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
Indexes:
"bar_pkey" PRIMARY KEY, btree (id)

[postgres81@boll ~]$ pg_dump bug | grep -B 1 -i key
--
-- Name: foo_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres81;
Tablespace:
--
ALTER TABLE ONLY foo
ADD CONSTRAINT foo_pkey PRIMARY KEY (id);

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Gavin Scott (#1)
Re: BUG #2299: pg_dump error w/ renamed primary keys

"Gavin Scott" <gavin@planetacetech.com> writes:

If you rename a primary key, pg_dump tries to recreate it with the original
name. Here is how to recreate:

CREATE TABLE foo ( id integer );
ALTER TABLE foo ADD CONSTRAINT foo_pkey PRIMARY KEY (id);
ALTER TABLE foo_pkey RENAME TO bar_pkey;

This is correct, as the controlling name is that of the constraint not
the index. We really should forbid the above entirely, but it's not
a very high-priority issue.

(Somewhat higher priority is to add a RENAME CONSTRAINT capability;
that's on the TODO list. There was a patch for this submitted recently
but it needs work...)

regards, tom lane