From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Groshev Andrey <greenx(at)yandex(dot)ru> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [GENERAL] trouble with pg_upgrade 9.0 -> 9.1 |
Date: | 2012-12-20 11:37:46 |
Message-ID: | 20121220113746.GG20015@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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. +
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2012-12-20 12:50:12 | Re: [COMMITTERS] pgsql: Allow a streaming replication standby to follow a timeline switc |
Previous Message | Groshev Andrey | 2012-12-20 11:19:17 | Re: [GENERAL] trouble with pg_upgrade 9.0 -> 9.1 |
From | Date | Subject | |
---|---|---|---|
Next Message | Groshev Andrey | 2012-12-20 11:41:37 | Re: [GENERAL] trouble with pg_upgrade 9.0 -> 9.1 |
Previous Message | Andres Freund | 2012-12-20 11:33:00 | Re: PATCH: optimized DROP of multiple tables within a transaction |