From: | Andres Freund <andres(at)anarazel(dot)de> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org, andrew(at)dunslane(dot)net, Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> |
Subject: | reg* checks in pg_upgrade are out of date |
Date: | 2018-11-22 00:12:14 |
Message-ID: | 20181122001214.p7assjzejyyul32l@alap3.anarazel.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
It seems the list of reg* types and the check for them in pg_upgrade
have gone out of sync. We have the following reg* types:
SELECT typname FROM pg_type WHERE typname LIKE 'reg%' order by typname;
┌───────────────┐
│ typname │
├───────────────┤
│ regclass │
│ regconfig │
│ regdictionary │
│ regnamespace │
│ regoper │
│ regoperator │
│ regproc │
│ regprocedure │
│ regrole │
│ regtype │
└───────────────┘
(10 rows)
but pg_upgrade doesn't consider all of them:
/*
* While several relkinds don't store any data, e.g. views, they can
* be used to define data types of other columns, so we check all
* relkinds.
*/
res = executeQueryOrDie(conn,
"SELECT n.nspname, c.relname, a.attname "
"FROM pg_catalog.pg_class c, "
" pg_catalog.pg_namespace n, "
" pg_catalog.pg_attribute a "
"WHERE c.oid = a.attrelid AND "
" NOT a.attisdropped AND "
" a.atttypid IN ( "
" 'pg_catalog.regproc'::pg_catalog.regtype, "
" 'pg_catalog.regprocedure'::pg_catalog.regtype, "
" 'pg_catalog.regoper'::pg_catalog.regtype, "
" 'pg_catalog.regoperator'::pg_catalog.regtype, "
/* regclass.oid is preserved, so 'regclass' is OK */
/* regtype.oid is preserved, so 'regtype' is OK */
" 'pg_catalog.regconfig'::pg_catalog.regtype, "
" 'pg_catalog.regdictionary'::pg_catalog.regtype) AND "
" c.relnamespace = n.oid AND "
" n.nspname NOT IN ('pg_catalog', 'information_schema')");
(I don't get the order here btw)
ISTM when regrole and regnamespace were added, pg_upgrade wasn't
considered. It turns out that regrole is safe, because we preserve user
oids, but regnamespace isn't afaict. I don't think it's extremely
likely that users store such reg* columns in tables, but we probably
still should fix this.
Greetings,
Andres Freund
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2018-11-22 00:14:15 | Re: [RFC] Removing "magic" oids |
Previous Message | Andres Freund | 2018-11-21 23:47:22 | pg_upgrade supported versions policy |