Re: [GENERAL] trouble with pg_upgrade 9.0 -> 9.1
От | Bruce Momjian |
---|---|
Тема | Re: [GENERAL] trouble with pg_upgrade 9.0 -> 9.1 |
Дата | |
Msg-id | 20121220113746.GG20015@momjian.us обсуждение исходный текст |
Ответ на | Re: [GENERAL] trouble with pg_upgrade 9.0 -> 9.1 (Groshev Andrey <greenx@yandex.ru>) |
Список | pgsql-hackers |
On Thu, Dec 20, 2012 at 03:19:17PM +0400, Groshev Andrey wrote: > > > 20.12.2012, 13:00, "Bruce Momjian" <bruce@momjian.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", andthrough 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, andnot 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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Andres FreundДата:
Сообщение: Re: PATCH: optimized DROP of multiple tables within a transaction