Re: [GENERAL] trouble with pg_upgrade 9.0 -> 9.1

On Thu, Dec 20, 2012 at 08:55:16AM +0400, Groshev Andrey wrote:
> No, old database not use table plob......
> only primary key
>
> --
> -- Name: plob.ВерсияВнешнегоДокумента$Документ; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
> --
>
>
> -- For binary upgrade, must preserve pg_class oids
> SELECT binary_upgrade.set_next_index_pg_class_oid('786665369'::pg_catalog.oid);
>
> ALTER TABLE ONLY "lob.ВерсияВнешнегоДокумента$Документ"
> ADD CONSTRAINT "plob.ВерсияВнешнегоДокумента$Документ" PRIMARY KEY ("@Файл", "Страница");

OK, now I know what is happening, though I can't figure out yet how you
got there. Basically, when you create a primary key, the name you
supply goes into two places, pg_class, for the index, and pg_constraint
for the constraint name.

What is happening is that you have a "pg_class" entry called lob.*_pkey
and a "pg_constraint" entry with plob.*. You can verify it yourself by
running queries on the system tables. Let me know if you want me to
show you the queries.

pg_dump dumps the pg_constraint name when recreating the index, while
pg_upgrade uses the pg_class name. When you restore the database into
the new cluster, the pg_class index name is lost and the new primary key
gets identical pg_class and pg_constraint names.

I tried to recreate the problem with these commands:

test=> create table test (x int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
test=> alter index "test_pkey" rename to ptest;
ALTER INDEX
test=> select * from pg_constraint where conname = 'ptest';
conname | connamespace |
---------+--------------+-
ptest | 2200 |
(1 row)

test=> select * from pg_class where relname = 'ptest';
relname | relnamespace |
---------+--------------+-
ptest | 2200 |
(1 row)

As you can see, ALTER INDEX renamed both the pg_constraint and pg_class
names. Is it possible someone manually updated the system table to
rename this primary key? That would cause this error message. The fix
is to just to make sure they match.

Does pg_upgrade need to be modified to handle this case? Are there
legitimate cases where they will not match and the index name will not
be preserved though a dump/restore? This seems safe:

test=> alter table test add constraint zz primary key using index ii;
NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "ii" to "zz"
ALTER TABLE

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +