Re: [GENERAL] trouble with pg_upgrade 9.0 -> 9.1
От | Groshev Andrey |
---|---|
Тема | Re: [GENERAL] trouble with pg_upgrade 9.0 -> 9.1 |
Дата | |
Msg-id | 21981356002357@web27f.yandex.ru обсуждение исходный текст |
Ответ на | Re: [GENERAL] trouble with pg_upgrade 9.0 -> 9.1 (Bruce Momjian <bruce@momjian.us>) |
Ответы |
Re: [GENERAL] trouble with pg_upgrade 9.0 -> 9.1
|
Список | pgsql-hackers |
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", and throughthe 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, andnot 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@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. +
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Amit KapilaДата:
Сообщение: Re: ThisTimeLineID in checkpointer and bgwriter processes
Следующее
От: Andres FreundДата:
Сообщение: Re: PATCH: optimized DROP of multiple tables within a transaction