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

20.12.2012, 13:00, "Bruce Momjian" <bruce(at)momjian(dot)us>:
> 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 have already begun to approach this to the idea, when noticed that pgAdmin describes this index through "_pkey", and through the pg_dump "plob.".
But your letter immediately pointed me to the end of my research :)

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

Unfortunately, my knowledge is not enough to talk about it.
I do not know what comes first in this case: pg_class, pg_constraint or pg_catalog.index or pg_catalog.pg_indexes.
Incidentally, in the last of:

#
select schemaname,tablename,indexname,tablespace from pg_catalog.pg_indexes where indexname like '%ВерсияВнешнегоДокумента$Документ%';
schemaname | tablename | indexname | tablespace
------------+--------------------------------------+----------------------------------------------+------------
public | lob.ВерсияВнешнегоДокумента$Документ | lob.ВерсияВнешнегоДокумента$Документ_pkey |
public | ВерсияВнешнегоДокумента$Документ | ВерсияВнешнегоДокумента$Документ_pkey |
public | ВерсияВнешнегоДокумента$Документ | iВерсияВнешнегоДокумента$Документ-blb_header |
(3 rows)

If pg_upgrade said that the old database is not in a very good condition, I would look for a problem in the database, and not something else.

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