20.12.2012, 13:00, "Bruce Momjian" <br...@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 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 <br...@momjian.us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
