Re: [HACKERS] [GENERAL] trouble with pg_upgrade 9.0 -> 9.1
20.12.2012, 11:43, "Bruce Momjian" :
>> 19.12.2012, 21:47, "Tom Lane" :
>>> "Kevin Grittner" writes:
>>>> Groshev Andrey wrote:
>>>> Mismatch of relation names: database "database", old rel
>>>> public.lob.ВерсияВнешнегоДокумента$Документ_pkey, new rel
>>>> public.plob.ВерсияВнешнегоДокумента$Документ
>>>> There is a limit on identifiers of 63 *bytes* (not characters)
>>>> after which the name is truncated. In UTF8 encoding, the underscore
>>>> would be in the 64th position.
>>> Hmm ... that is a really good point, except that you are not counting
>>> the "lob." or "plob." part, which we previously saw is part of the
>>> relation name not the schema name. Counting that part, it's already
>>> overlimit, which seems to be proof that Andrey isn't using UTF8 but
>>> some single-byte encoding.
>>>
>>> Anyway, that would only explain the issue if pg_upgrade were somehow
>>> changing the database encoding, which surely we'd have heard complaints
>>> about already? Or maybe this has something to do with pg_upgrade's
>>> client-side encoding rather than the server encoding...
>>>
>>> regards, tom lane
>> I'm initialize data dir with use ru_RU.UTF8, but this databse use CP1251,
>> ie one byte per character.
>
> Agreed. This is a complicated report because the identifiers:
>
> * contain periods
> * are long
> * are in cyrillic
> * don't use utf8
> * are very similar
>
> However, I just can't see how these could be causing the problem.
> Looking at the 9.1 pg_upgrade code, we already know that there are the
> same number of relations in old and new clusters, so everything must be
> being restored. And there is a lob.* and a plob.* that exist. The C
> code is also saying that the pg_class.oid of the lob.* in the old
> database is the same as the plob.* in the new database. That question
> is how is that happening.
>
> Can you email me privately the output of:
>
> pg_dump --schema-only --binary-upgrade database
>
> Thanks. If you want to debug this yourself, check these lines in the
> pg_dump output:
>
> -- 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 ("@Файл", "Страница");
>
> See that 786665369? That is the pg_class.oid of the plob in the old
> cluster, and hopefully the new one. Find where the lob*_pkey index is
> created and get that oid. Those should match the same names of the
> pg_class.oid in the old and new clusters, but it seems the new plob* oid
> is matching the lob oid in the old cluster.
>
> Also, pg_upgrade sorts everything by oid, so it can't be that somehow
> pg_upgrade isn't ordering things right, and because we already passed
> the oid check, we already know they have the same oid, but different
> names.
>
> --
> Bruce Momjian http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
Yes, was the last question. How to find out which version should stay?
And of course, I forgot to say a great big thank you!
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] trouble with pg_upgrade 9.0 -> 9.1
20.12.2012, 13:00, "Bruce Momjian" :
> 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 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
Re: [HACKERS] [GENERAL] trouble with pg_upgrade 9.0 -> 9.1
I'm initialize data dir with use ru_RU.UTF8, but this databse use CP1251, ie one byte per character. 19.12.2012, 21:47, "Tom Lane" : > "Kevin Grittner" writes: > >> Groshev Andrey wrote: >> Mismatch of relation names: database "database", old rel >> public.lob.ВерсияВнешнегоДокумента$Документ_pkey, new rel >> public.plob.ВерсияВнешнегоДокумента$Документ >> There is a limit on identifiers of 63 *bytes* (not characters) >> after which the name is truncated. In UTF8 encoding, the underscore >> would be in the 64th position. > > Hmm ... that is a really good point, except that you are not counting > the "lob." or "plob." part, which we previously saw is part of the > relation name not the schema name. Counting that part, it's already > overlimit, which seems to be proof that Andrey isn't using UTF8 but > some single-byte encoding. > > Anyway, that would only explain the issue if pg_upgrade were somehow > changing the database encoding, which surely we'd have heard complaints > about already? Or maybe this has something to do with pg_upgrade's > client-side encoding rather than the server encoding... > > regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] trouble with pg_upgrade 9.0 -> 9.1
No, people can confuse writing, but it makes a computer.
Unfortunately, I have not found developer this database, but I understand the
logic was:
plob - primary key (lob ~ BLOB)
rlob - reference key (lob ~ BLOB)
Maybe if I describe the task, this part of the database, the problem is clear.
We need to maintain external documents (binary scans, per page).
Therefore, there is a table to store the titles and a table to store binary
data.
To make it more comfortable I replaced all Russian words translated words.
This a table for headers store.
-- Table: "VersionOfTheExternalDocument$Document"
-- DROP TABLE "VersionOfTheExternalDocument$Document";
CREATE TABLE "VersionOfTheExternalDocument$Document"
(
"@File" integer NOT NULL DEFAULT
nextval((pg_get_serial_sequence('"public"."VersionOfTheExternalDocument$Document"'::text,
'@File'::text))::regclass),
"GUID" uuid,
"DataTime" timestamp without time zone DEFAULT (now())::timestamp without
time zone,
"Name" character varying,
"Size" integer,
CONSTRAINT "VersionOfTheExternalDocument$Document_pkey" PRIMARY KEY ("@File")
)
WITH (
OIDS=FALSE
);
ALTER TABLE "VersionOfTheExternalDocument$Document"
OWNER TO postgres;
GRANT ALL ON TABLE "VersionOfTheExternalDocument$Document" TO postgres;
GRANT SELECT ON TABLE "VersionOfTheExternalDocument$Document" TO view_user;
-- Index: "iVersionOfTheExternalDocument$Document-blb_header"
-- DROP INDEX "iVersionOfTheExternalDocument$Document-blb_header";
CREATE INDEX "iVersionOfTheExternalDocument$Document-blb_header"
ON "VersionOfTheExternalDocument$Document"
USING btree
("GUID", "@Файл", "ДатаВремя")
WHERE "GUID" IS NOT NULL;
---
And this for data.
-- Table: "lob.VersionOfTheExternalDocument$Document"
-- DROP TABLE "lob.VersionOfTheExternalDocument$Document";
CREATE TABLE "lob.VersionOfTheExternalDocument$Document"
(
"@File" integer NOT NULL,
"Page" integer NOT NULL,
"Data" bytea,
CONSTRAINT "lob.VersionOfTheExternalDocument$Document_pkey" PRIMARY KEY
("@File", "Page"),
CONSTRAINT "rlob.VersionOfTheExternalDocument$Document-@File" FOREIGN KEY
("@File")
REFERENCES "VersionOfTheExternalDocument$Document" ("@File") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
)
WITH (
OIDS=FALSE
);
ALTER TABLE "lob.VersionOfTheExternalDocument$Document"
OWNER TO postgres;
GRANT ALL ON TABLE "lob.VersionOfTheExternalDocument$Document" TO postgres;
GRANT SELECT ON TABLE "lob.VersionOfTheExternalDocument$Document" TO view_user;
20.12.2012, 07:12, "Bruce Momjian" :
> On Wed, Dec 19, 2012 at 10:35:11PM -0500, Bruce Momjian wrote:
>
>>> There is another table "ВерсияВнешнегоДокумента$Документ" (without ^lob.)
>>> It is referenced by a foreign key
>>> ("rlob.ВерсияВнешнегоДокумента$Документ-@Файл")
>>> But as I understand it, the problem with the primary key.
>> Does the old database have a table with prefix "plob.", called
>> plob.ВерсияВнешнегоДокумента$Документ?
>>
>> If not, if you do pg_dumpall --schema-only --binary-upgrade, is there a
>> table with that name mentioned?
>
> Also, when you say "rlob" above, is the 'r' a Latin letter sound that
> would look like a Russian 'p' in the error message? (In Cyrillic, a
> Latin-looking p sounds like Latin-sounding r.)
>
> --
> Bruce Momjian 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
Re: [HACKERS] [GENERAL] trouble with pg_upgrade 9.0 -> 9.1
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
("@Файл", "Страница");
20.12.2012, 06:35, "Bruce Momjian" :
> On Wed, Dec 19, 2012 at 01:51:08PM +0400, Groshev Andrey wrote:
>
>>> Can you post the full definition of the table on this public email list?
>>> Also, why did the error think this was in the public schema? Any idea?
>>>
>>> ---
>>>> 18.12.2012, 19:38, "Bruce Momjian" :
>>>>> On Mon, Dec 17, 2012 at 09:21:59PM -0500, Bruce Momjian wrote:
>>>>>>> Mismatch of relation names: database "database", old rel
>>>>>>> public.lob.ВерсияВнешнегоДокумента$Документ_pkey, new rel
>>>>>>> public.plob.ВерсияВнешнегоДокумента$Документ
>>>>>>> Failure, exiting
>> .. snip
>>
>> It's all what I'm found about this table.
>>
>> --
>> -- Name: lob.ВерсияВнешнегоДокумента$Документ; Type: TABLE; Schema: public;
>> Owner: postgres; Tablespace:
>> --
>>
>> CREATE TABLE "lob.ВерсияВнешнегоДокумента$Документ" (
>> "@Файл" integer NOT NULL,
>> "Страница" integer NOT NULL,
>> "Данные" bytea
>> );
>>
>> ALTER TABLE public."lob.ВерсияВнешнегоДокумента$Документ" OWNER TO postgres;
>>
>> --
>> -- Name: plob.ВерсияВнешнегоДокумента$Документ; Type: CONSTRAINT; Schema:
>> public; Owner: postgres; Tablespace:
>> --
>>
>> ALTER TABLE ONLY "lob.ВерсияВнешнегоДокумента$Документ"
>> ADD CONSTRAINT "plob.ВерсияВнешнегоДокумента$Документ"
>> PRIMARY KEY ("@Файл", "Страница");
>>
>> --
>> -- Name: rlob.ВерсияВнешнегоДокумента$Документ-@Файл; Type: FK CONSTRAINT;
>> Schema: public; Owner: postgres
>> --
>>
>> ALTER TABLE ONLY "lob.ВерсияВнешнегоДокумента$Документ"
>> ADD CONSTRAINT "rlob.ВерсияВнешнегоДокумента$Документ-@Файл"
>> FOREIGN KEY ("@Файл")
>> REFERENCES "ВерсияВнешнегоДокумента$Документ"("@Файл")
>> ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
>>
>> --
>> -- Name: lob.ВерсияВнешнегоДокумента$Документ; Type: ACL; Schema: public;
>> Owner: postgres
>> --
>>
>> REVOKE ALL ON TABLE "lob.ВерсияВнешнегоДокумента$Документ" FROM PUBLIC;
>> REVOKE ALL ON TABLE "lob.ВерсияВнешнегоДокумента$Документ" FROM postgres;
>> GRANT ALL ON TABLE "lob.ВерсияВнешнегоДокумента$Документ" TO postgres;
>> GRANT SELECT ON TABLE "lob.ВерсияВнешнегоДокумента$Документ" TO view_user;
>>
>> There is another table "ВерсияВнешнегоДокумента$Документ" (without ^lob.)
>> It is referenced by a foreign key
>> ("rlob.ВерсияВнешнегоДокумента$Документ-@Файл")
>> But as I understand it, the problem with the primary key.
>
> Does the old database have a table with prefix "plob.", called
> plob.ВерсияВнешнегоДокумента$Документ?
>
> If not, if you do pg_dumpall --schema-only --binary-upgrade, is there a
> table with that name mentioned?
>
> --
> Bruce Momjian 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
Re: [HACKERS] [GENERAL] trouble with pg_upgrade 9.0 -> 9.1
>
> Can you post the full definition of the table on this public email list?
> Also, why did the error think this was in the public schema? Any idea?
>
> ---
>
>> 18.12.2012, 19:38, "Bruce Momjian" :
>>> On Mon, Dec 17, 2012 at 09:21:59PM -0500, Bruce Momjian wrote:
> Mismatch of relation names: database "database", old rel
> public.lob.ВерсияВнешнегоДокумента$Документ_pkey, new rel
> public.plob.ВерсияВнешнегоДокумента$Документ
> Failure, exiting
.. snip
It's all what I'm found about this table.
--
-- Name: lob.ВерсияВнешнегоДокумента$Документ; Type: TABLE; Schema: public;
Owner: postgres; Tablespace:
--
CREATE TABLE "lob.ВерсияВнешнегоДокумента$Документ" (
"@Файл" integer NOT NULL,
"Страница" integer NOT NULL,
"Данные" bytea
);
ALTER TABLE public."lob.ВерсияВнешнегоДокумента$Документ" OWNER TO postgres;
--
-- Name: plob.ВерсияВнешнегоДокумента$Документ; Type: CONSTRAINT; Schema:
public; Owner: postgres; Tablespace:
--
ALTER TABLE ONLY "lob.ВерсияВнешнегоДокумента$Документ"
ADD CONSTRAINT "plob.ВерсияВнешнегоДокумента$Документ"
PRIMARY KEY ("@Файл", "Страница");
--
-- Name: rlob.ВерсияВнешнегоДокумента$Документ-@Файл; Type: FK CONSTRAINT;
Schema: public; Owner: postgres
--
ALTER TABLE ONLY "lob.ВерсияВнешнегоДокумента$Документ"
ADD CONSTRAINT "rlob.ВерсияВнешнегоДокумента$Документ-@Файл"
FOREIGN KEY ("@Файл")
REFERENCES "ВерсияВнешнегоДокумента$Документ"("@Файл")
ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
--
-- Name: lob.ВерсияВнешнегоДокумента$Документ; Type: ACL; Schema: public;
Owner: postgres
--
REVOKE ALL ON TABLE "lob.ВерсияВнешнегоДокумента$Документ" FROM PUBLIC;
REVOKE ALL ON TABLE "lob.ВерсияВнешнегоДокумента$Документ" FROM postgres;
GRANT ALL ON TABLE "lob.ВерсияВнешнегоДокумента$Документ" TO postgres;
GRANT SELECT ON TABLE "lob.ВерсияВнешнегоДокумента$Документ" TO view_user;
There is another table "ВерсияВнешнегоДокумента$Документ" (without ^lob.)
It is referenced by a foreign key
("rlob.ВерсияВнешнегоДокумента$Документ-@Файл")
But as I understand it, the problem with the primary key.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] trouble with pg_upgrade 9.0 -> 9.1
later in the log pg_dump, I found the definition of "new rel"
--
-- Name: plob.ВерсияВнешнегоДокумента$Документ; Type: CONSTRAINT; Schema:
public; Owner: postgres; Tablespace:
--
ALTER TABLE ONLY "lob.ВерсияВнешнегоДокумента$Документ"
ADD CONSTRAINT "plob.ВерсияВнешнегоДокумента$Документ" PRIMARY KEY
("@Файл", "Страница");
18.12.2012, 19:38, "Bruce Momjian" :
> On Mon, Dec 17, 2012 at 09:21:59PM -0500, Bruce Momjian wrote:
>
>>> Mismatch of relation names: database "database", old rel
>>> public.lob.ВерсияВнешнегоДокумента$Документ_pkey, new rel
>>> public.plob.ВерсияВнешнегоДокумента$Документ
>>> Failure, exiting
>
> I am now confused over the error message above. This is the code that
> is generating the error:
>
> /*
> * TOAST table names initially match the heap pg_class oid.
> * In pre-8.4, TOAST table names change during CLUSTER; in pre-9.0,
> * TOAST table names change during ALTER TABLE ALTER COLUMN SET TYPE.
> * In >= 9.0, TOAST relation names always use heap table oids, hence
> * we cannot check relation names when upgrading from pre-9.0.
> * Clusters upgraded to 9.0 will get matching TOAST names.
> */
> if (strcmp(old_rel->nspname, new_rel->nspname) != 0 ||
> ((GET_MAJOR_VERSION(old_cluster.major_version) >= 900 ||
> strcmp(old_rel->nspname, "pg_toast") != 0) &&
> strcmp(old_rel->relname, new_rel->relname) != 0))
> pg_log(PG_FATAL, "Mismatch of relation names: database \"%s\", "
> "old rel %s.%s, new rel %s.%s\n",
> old_db->db_name, old_rel->nspname, old_rel->relname,
> new_rel->nspname, new_rel->relname);
>
> Looking at the Russian, I see 'old rel' public.lob.* and 'new rel'
> public.plob.*. I assume the database is called 'database', and the
> schema is called 'public', but what is 'lob' and 'plob'? If those are
> tables or indexes, what is after the period? Do you have periods
> embedded in the table/index names? That is certainly possible, but not
> common, e.g.:
>
> test=> create table "test.x" (y int);
> CREATE TABLE
>
> Is the schema called "public.lob"? I expected to see schema.objname.
>
> --
> Bruce Momjian 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
Re: [HACKERS] [GENERAL] trouble with pg_upgrade 9.0 -> 9.1
18.12.2012, 05:22, "Bruce Momjian" : > This is the first pg_upgrade mismatch report we have gotten about 9.1. > I have asked the reporter for details. > > Is what is the full 9.1 version number? > > --- >> # rpm -qa |grep postgres >> postgresql90-devel-9.0.11-1PGDG.rhel6.x86_64 >> postgresql91-9.1.7-1PGDG.rhel6.x86_64 >> postgresql90-9.0.11-1PGDG.rhel6.x86_64 >> postgresql90-server-9.0.11-1PGDG.rhel6.x86_64 >> postgresql91-libs-9.1.7-1PGDG.rhel6.x86_64 >> postgresql91-server-9.1.7-1PGDG.rhel6.x86_64 >> postgresql91-devel-9.1.7-1PGDG.rhel6.x86_64 >> postgresql90-libs-9.0.11-1PGDG.rhel6.x86_64 >> postgresql90-contrib-9.0.11-1PGDG.rhel6.x86_64 >> postgresql91-contrib-9.1.7-1PGDG.rhel6.x86_64 >> Full version ? It is not full postgresql91-9.1.7-1PGDG.rhel6.x86_64 or I do not understand something? I installed latest postgresql from the repository http://yum.pgrpms.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
