Thread: pg_upgrade (Checking for reg* data types)
Hi All,
I am trying to upgrade Postgresql 11.5 to 12 RC1, OS Centos 6. Running pg_upgrade with --check it fails with "Checking for reg* data types in user tables" The table listed are: pg_ts_dict,pg_ts_parser. Trying to drop the tables I get msg " permission denied: "pg_ts_dict" is a system catalog" I also for same reason can not remove the oids "ALTER TABLE pg_ts_parser set without oids;"
From pg_catalog.pg_extension the following are listed: plpgsql,pg_stat_statements,tablefunc. So the Text search extension is not in use.
This database was upgrade since Postgresql 8.3 by using "pg_dumpall -p 5432 | psql -d postgres -p 5433" and not pg_upgrade.
Regards
Gerrit Fouche
On Mon, Sep 30, 2019 at 11:15:47AM +0200, Gerrit Fouche wrote: > > Hi All, > > I am trying to upgrade Postgresql 11.5 to 12 RC1, OS Centos 6. Running > pg_upgrade with --check it fails with "Checking for reg* data types in user > tables" The table listed are: pg_ts_dict,pg_ts_parser. Trying to drop the > tables I get msg " permission denied: "pg_ts_dict" is a system catalog" I also > for same reason can not remove the oids "ALTER TABLE pg_ts_parser set without > oids;" > > From pg_catalog.pg_extension the following are listed: > plpgsql,pg_stat_statements,tablefunc. So the Text search extension is not in > use. > > This database was upgrade since Postgresql 8.3 by using "pg_dumpall -p 5432 | > psql -d postgres -p 5433" and not pg_upgrade. Wow, 8.3 --- that is old. Please do psql \d on those two tables and show us the output. System tables are created by pg_upgrade as fresh, not copied from the old cluster, so I am confused how you have reg* entries in there. Also, I wonder if there are pg_ts_dict and pg_ts_parser tables that are not in pg_catalog, but in some other schema, and those are what is complaining about. (The pg_upgrade query specifically skips checking pg_catalog tables.) I think maybe pg_upgrade should always output the schema name for such objects --- I think someone propsed a patch for that recently. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
On Mon, Sep 30, 2019 at 11:15:47AM +0200, Gerrit Fouche wrote:
>
> Hi All,
>
> I am trying to upgrade Postgresql 11.5 to 12 RC1, OS Centos 6. Running
> pg_upgrade with --check it fails with "Checking for reg* data types in user
> tables" The table listed are: pg_ts_dict,pg_ts_parser. Trying to drop the
> tables I get msg " permission denied: "pg_ts_dict" is a system catalog" I also
> for same reason can not remove the oids "ALTER TABLE pg_ts_parser set without
> oids;"
PG 12 outputs the schema.table.column names, separated by dots. I need
to see the exact error output. This is the check query:
"SELECT n.nspname, c.relname, a.attname "
"FROM pg_catalog.pg_class c, "
" pg_catalog.pg_namespace n, "
" pg_catalog.pg_attribute a, "
" pg_catalog.pg_type t "
"WHERE c.oid = a.attrelid AND "
" NOT a.attisdropped AND "
" a.atttypid = t.oid AND "
" t.typnamespace = "
" (SELECT oid FROM pg_namespace "
" WHERE nspname = 'pg_catalog') AND"
" t.typname IN ( "
/* regclass.oid is preserved, so 'regclass' is OK */
" 'regconfig', "
" 'regdictionary', "
" 'regnamespace', "
" 'regoper', "
" 'regoperator', "
" 'regproc', "
" 'regprocedure' "
/* regrole.oid is preserved, so 'regrole' is OK */
/* regtype.oid is preserved, so 'regtype' is OK */
" ) AND "
" c.relnamespace = n.oid AND "
" n.nspname NOT IN ('pg_catalog', 'information_schema')");
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
Thank you very much.
On Mon, Sep 30, 2019 at 10:33 PM Bruce Momjian <bruce@momjian.us> wrote:
On Mon, Sep 30, 2019 at 11:15:47AM +0200, Gerrit Fouche wrote:
>
> Hi All,
>
> I am trying to upgrade Postgresql 11.5 to 12 RC1, OS Centos 6. Running
> pg_upgrade with --check it fails with "Checking for reg* data types in user
> tables" The table listed are: pg_ts_dict,pg_ts_parser. Trying to drop the
> tables I get msg " permission denied: "pg_ts_dict" is a system catalog" I also
> for same reason can not remove the oids "ALTER TABLE pg_ts_parser set without
> oids;"
PG 12 outputs the schema.table.column names, separated by dots. I need
to see the exact error output. This is the check query:
"SELECT n.nspname, c.relname, a.attname "
"FROM pg_catalog.pg_class c, "
" pg_catalog.pg_namespace n, "
" pg_catalog.pg_attribute a, "
" pg_catalog.pg_type t "
"WHERE c.oid = a.attrelid AND "
" NOT a.attisdropped AND "
" a.atttypid = t.oid AND "
" t.typnamespace = "
" (SELECT oid FROM pg_namespace "
" WHERE nspname = 'pg_catalog') AND"
" t.typname IN ( "
/* regclass.oid is preserved, so 'regclass' is OK */
" 'regconfig', "
" 'regdictionary', "
" 'regnamespace', "
" 'regoper', "
" 'regoperator', "
" 'regproc', "
" 'regprocedure' "
/* regrole.oid is preserved, so 'regrole' is OK */
/* regtype.oid is preserved, so 'regtype' is OK */
" ) AND "
" c.relnamespace = n.oid AND "
" n.nspname NOT IN ('pg_catalog', 'information_schema')");
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +