Re: pg_upgrade and relkind filtering

Robert Haas wrote:
> On Mon, Dec 5, 2011 at 5:06 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > Pg_upgrade has the following check to make sure the cluster is safe for
> > upgrading:
> >
> > ? ? ? ?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 != 'pg_catalog' AND "
> > ? ? ? ? ?" ? ? n.nspname != 'information_schema'");
> >
> > Based on a report from EnterpriseDB, I noticed that we check all
> > pg_class entries, while there are cases where this is unnecessary
> > because there is no data behind the entry, e.g. views. ?Here are the
> > relkinds supported:
> >
> > ? ? ? ?#define ? ? ? RELKIND_RELATION ? ? ? ?'r' ? ? ? /* ordinary table */
> > ? ? ? ?#define ? ? ? RELKIND_INDEX ? ? ? ? ? 'i' ? ? ? /* secondary index */
> > ? ? ? ?#define ? ? ? RELKIND_SEQUENCE ? ? ? ?'S' ? ? ? /* sequence object */
> > ? ? ? ?#define ? ? ? RELKIND_TOASTVALUE ? ? ?'t' ? ? ? /* for out-of-line values */
> > ? ? ? ?#define ? ? ? RELKIND_VIEW ? ? ? ? ? ?'v' ? ? ? /* view */
> > ? ? ? ?#define ? ? ? RELKIND_COMPOSITE_TYPE ?'c' ? ? ? /* composite type */
> > ? ? ? ?#define ? ? ? RELKIND_FOREIGN_TABLE ? 'f' ? ? ? /* foreign table */
> > ? ? ? ?#define ? ? ? RELKIND_UNCATALOGED ? ? 'u' ? ? ? /* not yet cataloged */
> >
> > What types, other than views, can we skip in this query?
>
> It's not obvious to me that anything other than a table or index would matter.

Well, I assume the composite type could be referenced by another table,
and the foreign table might have data stored in it that is now invalid.
Toast and sequences are probably safely skipped, but also probably never
a problem to check.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +