Re: [HACKERS] [GENERAL] trouble with pg_upgrade 9.0 - 9.1
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
Re: [HACKERS] [GENERAL] trouble with pg_upgrade 9.0 - 9.1
20.12.2012, 11:43, Bruce Momjian br...@momjian.us: 19.12.2012, 21:47, Tom Lane t...@sss.pgh.pa.us: Kevin Grittner kgri...@mail.com 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 br...@momjian.us 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
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 br...@momjian.us: 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
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 br...@momjian.us: 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 br...@momjian.us: 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 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
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 br...@momjian.us: 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 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
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 t...@sss.pgh.pa.us: Kevin Grittner kgri...@mail.com 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
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 br...@momjian.us: 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 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
Re: [HACKERS] [GENERAL] trouble with pg_upgrade 9.0 - 9.1
18.12.2012, 05:22, Bruce Momjian br...@momjian.us: 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