Thread: ALTER INDEX/ALTER TABLE on indexes can cause unrestorable dumps

Andy just reported on IRC that renaming indexes can lead to unrestorable
dumps under certain circumstances. A simple example(8.2 but at least 8.1
and 8.3 seem to behave exactly the same) for that is:

test=# CREATE TABLE foo(bar int PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
test=# ALTER TABLE foo_pkey RENAME TO mynew_pkey;
ALTER TABLE
test=# CLUSTER mynew_pkey ON foo ;
CLUSTER

which - if dumped & restored leads to:

ERROR:  index "mynew_pkey" for table "foo" does not exist

the reason for this seems to be that pg_dump is using the constraint
name (which is not changed by ALTER TABLE/ALTER INDEX) and not the index
name to dump this kind of information but I wonder if it would actually
be more sensible (until we get ALTER TABLE .. ALTER CONSTRAINT) to
simply forbid renaming indexes that are part of a constraint like that
and hint towards ALTER TABLE ADD/DROP CONSTRAINT ?



Stefan

dup 3854
thanks

Stefan Kaltenbrunner wrote:

> the reason for this seems to be that pg_dump is using the constraint name
> (which is not changed by ALTER TABLE/ALTER INDEX) and not the index name to
> dump this kind of information but I wonder if it would actually be more
> sensible (until we get ALTER TABLE .. ALTER CONSTRAINT) to simply forbid
> renaming indexes that are part of a constraint like that and hint towards
> ALTER TABLE ADD/DROP CONSTRAINT ?

I think the fix is to make pg_dump emit ALTER TABLE/CLUSTER ON using the
right index name ...

(This makes me wonder whether we should be making pg_dump emit sorted
output for tables with a clustering index.)

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support