[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: pg_upgrade (Checking for reg* data types)
From: Gerrit Fouche <gerrit.fouche () gmail ! com>
Date: 2019-09-30 20:37:45
Message-ID: CAJGpXMgssWrrX4uWjP5-DLv9+c4zfjEiU=2WEJfyWaeY-vsZHA () mail ! gmail ! com
[Download RAW message or body]
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 +
>
[Attachment #3 (text/html)]
<div dir="ltr">Thank you very much. <br></div><br><div class="gmail_quote"><div \
dir="ltr" class="gmail_attr">On Mon, Sep 30, 2019 at 10:33 PM Bruce Momjian <<a \
href="mailto:bruce@momjian.us">bruce@momjian.us</a>> wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex">On Mon, Sep 30, 2019 at 11:15:47AM +0200, Gerrit \
Fouche wrote:<br> > <br>
> Hi All,<br>
> <br>
> I am trying to upgrade Postgresql 11.5 to 12 RC1, OS Centos 6. Running<br>
> pg_upgrade with --check it fails with "Checking for reg* data types in \
user<br> > tables" The table listed are: pg_ts_dict,pg_ts_parser. Trying to \
drop the<br> > tables I get msg " permission denied: "pg_ts_dict" \
is a system catalog" I also<br> > for same reason can not remove the oids \
"ALTER TABLE pg_ts_parser set without<br> > oids;"<br>
<br>
PG 12 outputs the schema.table.column names, separated by dots. I need<br>
to see the exact error output. This is the check query:<br>
<br>
"SELECT n.nspname, c.relname, a.attname \
"<br>
"FROM pg_catalog.pg_class c, "<br>
" pg_catalog.pg_namespace n, "<br>
" pg_catalog.pg_attribute a, "<br>
" pg_catalog.pg_type t "<br>
"WHERE c.oid = a.attrelid AND "<br>
" NOT a.attisdropped AND "<br>
" a.atttypid = t.oid AND "<br>
" t.typnamespace = "<br>
" (SELECT oid FROM pg_namespace \
"<br>
" WHERE nspname = \
'pg_catalog') AND"<br> " t.typname IN ( "<br>
/* regclass.oid is preserved, so 'regclass' is OK */<br>
" 'regconfig', "<br>
" 'regdictionary', "<br>
" 'regnamespace', "<br>
" 'regoper', "<br>
" 'regoperator', "<br>
" 'regproc', "<br>
" 'regprocedure' "<br>
/* regrole.oid is preserved, so 'regrole' is OK */<br>
/* regtype.oid is preserved, so 'regtype' is OK */<br>
" ) AND "<br>
" c.relnamespace = n.oid AND "<br>
" n.nspname NOT IN ('pg_catalog', \
'information_schema')");<br> <br>
<br>
-- <br>
Bruce Momjian <<a href="mailto:bruce@momjian.us" \
target="_blank">bruce@momjian.us</a>> <a href="http://momjian.us" \
rel="noreferrer" target="_blank">http://momjian.us</a><br> EnterpriseDB \
<a href="http://enterprisedb.com" rel="noreferrer" \
target="_blank">http://enterprisedb.com</a><br> <br>
+ As you are, so once was I. As I am, so you will be. +<br>
+ Ancient Roman grave inscription +<br>
</blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic