| From: | Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc> |
|---|---|
| To: | pgsql-bugs(at)postgresql(dot)org |
| Cc: | andy(at)tcpd(dot)net |
| Subject: | ALTER INDEX/ALTER TABLE on indexes can cause unrestorable dumps |
| Date: | 2008-01-07 20:05:15 |
| Message-ID: | 478285FB.40402@kaltenbrunner.cc |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2008-01-07 22:12:36 | Re: BUG #3852: Could not create complex aggregate |
| Previous Message | Alvaro Herrera | 2008-01-07 19:16:10 | Re: ALTER INDEX/ALTER TABLE on indexes can cause unrestorable dumps |