Обсуждение: Re: [GENERAL] trouble with pg_upgrade 9.0 -> 9.1
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? --------------------------------------------------------------------------- On Mon, Dec 17, 2012 at 03:33:40PM +0400, Groshev Andrey wrote: > Hello! > I'm trying to update a database from version 9.0 to 9.1 by pg_upgrade. > The test is normal, but the actual conversion fails. > This is a bug from pg_upgrade? > I just saw this in the newsletter similar error with a note that it has been fixed in 9.1.2, but I already install 9.1.7. > > My environment > centos 6.3 > # uname -rm > 2.6.32-279.14.1.el6.x86_64 x86_64 > > # 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 > > > # time sudo -u postgres sh -c '/usr/pgsql-9.1/bin/pg_upgrade -b /usr/pgsql-9.0/bin/ -B /usr/pgsql-9.1/bin/ -d /var/lib/pgsql/9.0/data/-D /var/lib/pgsql/9.1/data/ -vvv -c -l ./log 2>&1 | iconv -f cp1251 -t utf-8' > Running in verbose mode > ..... > Running in verbose mode > Running in verbose mode > Performing Consistency Checks > ----------------------------- > Checking current, bin, and data directories ok > Checking cluster versions ok > "/usr/pgsql-9.0/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.0/data" -o "-p 5432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000"start >> "./log" 2>&1 > Checking database user is a superuser ok > Checking for prepared transactions ok > Checking for reg* system oid user data types ok > Checking for contrib/isn with bigint-passing mismatch ok > "/usr/pgsql-9.0/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.0/data" stop >> "./log" 2>&1 > "/usr/pgsql-9.1/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.1/data" -o "-p 5432 -b" start >> "./log" 2>&1 > Checking for presence of required libraries ok > Checking database user is a superuser ok > Checking for prepared transactions ok > > *Clusters are compatible* > "/usr/pgsql-9.1/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.1/data" stop >> "./log" 2>&1 > > real 0m4.344s > user 0m0.029s > sys 0m0.051s > > > > Checking current, bin, and data directories ok > Checking cluster versions ok > "/usr/pgsql-9.0/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.0/data" -o "-p 5432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000"start >> "./log > " 2>&1 > Checking database user is a superuser ok > Checking for prepared transactions ok > Checking for reg* system oid user data types ok > Checking for contrib/isn with bigint-passing mismatch ok > Creating catalog dump "/usr/pgsql-9.1/bin/pg_dumpall" --port 5432 --username "postgres"--schema-only --binary-upgrade > -f "/var/lib/pgsql/pg_upgrade/pg_upgrade_dump_all.sql" > ok > "/usr/pgsql-9.0/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.0/data" stop >> "./log" 2>&1 > "/usr/pgsql-9.1/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.1/data" -o "-p 5432 -b" start >> "./log" 2>&1 > Checking for presence of required libraries ok > Checking database user is a superuser ok > Checking for prepared transactions ok > > | If pg_upgrade fails after this point, you must > | re-initdb the new cluster before continuing. > | You will also need to remove the ".old" suffix > | from /var/lib/pgsql/9.0/data/global/pg_control.old. > > Performing Upgrade > ------------------ > Adding ".old" suffix to old global/pg_control ok > Analyzing all rows in the new cluster "/usr/pgsql-9.1/bin/vacuumdb" --port 5432 --username "postgres"--all --analyze >> "./log" 2>&1 > ok > Freezing all rows on the new cluster "/usr/pgsql-9.1/bin/vacuumdb" --port 5432 --username "postgres"--all --freeze >> "./log" 2>&1 > ok > "/usr/pgsql-9.1/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.1/data" stop >> "./log" 2>&1 > Deleting new commit clogs ok > Copying old commit clogs to new server cp -Rf "/var/lib/pgsql/9.0/data/pg_clog" "/var/lib/pgsql/9.1/data/pg_clog" > ok > Setting next transaction id for new cluster "/usr/pgsql-9.1/bin/pg_resetxlog" -f -x 728832600 "/var/lib/pgsql/9.1/data"> /dev/null > ok > Resetting WAL archives "/usr/pgsql-9.1/bin/pg_resetxlog" -l 3,2349,11 "/var/lib/pgsql/9.1/data">> "./log" 2>&1 > ok > "/usr/pgsql-9.1/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.1/data" -o "-p 5432 -b" start >> "./log" 2>&1 > Setting frozenxid counters in new cluster ok > Creating databases in the new cluster "/usr/pgsql-9.1/bin/psql" --set ON_ERROR_STOP=on --no-psqlrc--port 5432 --username "postgres" -f > "/var/lib/pgsql/pg_upgrade/pg_upgrade_dump_globals.sql" --dbname template1 >> "./log" > ok > Adding support functions to new cluster ok > Restoring database schema to new cluster "/usr/pgsql-9.1/bin/psql" --set ON_ERROR_STOP=on --no-psqlrc--port 5432 --username "postgres" -f > "/var/lib/pgsql/pg_upgrade/pg_upgrade_dump_db.sql" --dbname template1 >> "./log" > psql:/var/lib/pgsql/pg_upgrade/pg_upgrade_dump_db.sql:153584: ПРЕДУПРЕЖДЕНИЕ: => как имя оператора считается устаревшим > DETAIL: Это имя может быть вовсе запрещено в будущих версиях PostgreSQL. > ok > Removing support functions from new cluster ok > Removing support functions from new cluster ok > "/usr/pgsql-9.1/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.1/data" stop >> "./log" 2>&1 > Restoring user relation files > > Mismatch of relation names: database "database", old rel public.lob.ВерсияВнешнегоДокумента$Документ_pkey, new rel public.plob.ВерсияВнешнегоДокумент > а$Документ > Failure, exiting > > real 0m28.443s > user 0m4.868s > sys 0m0.921s > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
18.12.2012, 05:22, "Bruce Momjian" <bruce@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
On Tue, Dec 18, 2012 at 09:28:00AM +0400, Groshev Andrey wrote: > > > 18.12.2012, 05:22, "Bruce Momjian" <bruce@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 Oops, I see that now, sorry. I wanted to make sure you were on the most recent 9.1 version, and you are. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
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 changeduring 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 frompre-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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
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.ВерсияВнешнегоДокумента$Документ" PRIMARYKEY ("@Файл", "Страница"); 18.12.2012, 19:38, "Bruce Momjian" <bruce@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 <bruce@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. +
On Tue, Dec 18, 2012 at 09:34:53PM +0400, Groshev Andrey wrote: > 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 ("@Файл", "Страница"); > > 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" <bruce@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 relpublic.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 <bruce@momjian.us> http://momjian.us > > EnterpriseDB http://enterprisedb.com > > > > + It's impossible for everything to be true. + -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
> > 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" <bruce@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 relpublic.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.ВерсияВнешнегоДокумента$Документ" PRIMARYKEY ("@Файл", "Страница"); -- -- Name: rlob.ВерсияВнешнегоДокумента$Документ-@Файл; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY "lob.ВерсияВнешнегоДокумента$Документ" ADD CONSTRAINT "rlob.ВерсияВнешнегоДокумента$Документ-@Файл" FOREIGNKEY ("@Файл") 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.
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" <bruce@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, newrel 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. [ Sorry I have not been replying promptly. I have been sick with the flue for the past four days, and while I read the email promptly, my brain isn't sharp enough to send email out for everyone to read. I am better today so hopefully I will be 100% soon. ] OK, this tells me that the period is in the table name: -- Name: lob.ВерсияВнешнегоДокумента$Документ;Type: TABLE; Schema: public; Owner: postgres; Tablespace: I needed to check that the period wasn't a symptom of a bug. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
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" <bruce@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, newrel 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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
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.ВерсияВнешнегоДокумента$Документ" PRIMARYKEY ("@Файл", "Страница"); 20.12.2012, 06:35, "Bruce Momjian" <bruce@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" <bruce@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, newrel 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 <bruce@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. +
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 INITIALLYIMMEDIATE ) 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" <bruce@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 <bruce@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. +
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 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" fortable "test"CREATE TABLEtest=> alter index "test_pkey" rename to ptest;ALTER INDEXtest=> select * from pg_constraint whereconname = '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? 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 willrename 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. +
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. +
On Thu, Dec 20, 2012 at 03:19:17PM +0400, Groshev Andrey wrote: > > > 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", andthrough the pg_dump "plob.". > But your letter immediately pointed me to the end of my research :) Good. > > 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. pg_catalog.pg_indexes is a view. You can to modify pg_class to match the pg_constraint name. You might be able to just rename the index in Pgadmin to match. Perhaps PGAdmin allowed this mismatch to happen? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes: > 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? Are there > legitimate cases where they will not match and the index name will not > be preserved though a dump/restore? This seems safe: It's not always been true that ALTER INDEX would try to rename constraints to keep 'em in sync. A quick check says that only 8.3 and later do that. I'm not sure though how a 9.0 database could get into such a state without manual catalog hacking, since as you say a dump and reload should have ended up with the index and constraint having the same name again. I'd be inclined not to worry about this in pg_upgrade, at least not till we see a plausible scenario for the situation to arise without manual catalog changes. regards, tom lane
On Thu, Dec 20, 2012 at 11:08:58AM -0500, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > 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? Are there > > legitimate cases where they will not match and the index name will not > > be preserved though a dump/restore? This seems safe: > > It's not always been true that ALTER INDEX would try to rename > constraints to keep 'em in sync. A quick check says that only 8.3 and > later do that. I'm not sure though how a 9.0 database could get into > such a state without manual catalog hacking, since as you say a dump and > reload should have ended up with the index and constraint having the > same name again. > > I'd be inclined not to worry about this in pg_upgrade, at least not till > we see a plausible scenario for the situation to arise without manual > catalog changes. Agreed. I added a C comment so we don't forget about the matching requirement. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +