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

On Thu, Dec 20, 2012 at 03:19:17PM +0400, Groshev Andrey wrote:
>
>
> 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 :)

Good.

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

pg_catalog.pg_indexes is a view. You can to modify pg_class to match
the pg_constraint name. You might be able to just rename the index in
Pgadmin to match.

Perhaps PGAdmin allowed this mismatch to happen?

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

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