On 2018-11-22 08:49:23 -0500, Andrew Dunstan wrote: > > On 11/21/18 7:12 PM, Andres Freund wrote: > > 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. > > > > yeah, I think you're right, both about the need to fix it and the > unlikelihood of it occurring in the wild.
I've done so, and backpatched to 9.5, where these types where added. Greetings, Andres Freund