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