Re: pg_upgrade and materialized views

On 2018-02-20 18:13:26 -0300, Claudio Freire wrote:
> I'm not 100% sure this is a pg_upgrade bug or a pg_dump
> --binary-upgrade one, or some other thing, but at this point I'm
> fairly certain there's something wrong in one of them.
> 
> I just tried to pg_upgrade a database from 9.5 to 10.2. I took a
> snapshot off a replica, promoted it, and then did the pg_upgrade there
> (to avoid breaking our production server).
> 
> It all went very well, except that a database-wide vacuum is
> complaining about materialized views, not all of them, specifically
> the ones in which we regularly use "REFRESH MATERIALIZED VIEW
> CONCURRENTLY" on.
> 
> In our production master, those views contain rather old relfrozenxid:
> 
> mat=# select relname, relfrozenxid from pg_class where relname like
> '%_mv' or relname = 'user_agents_canonical_user_agent_os';
>                relname               | relfrozenxid
> -------------------------------------+--------------
>  os_ranking_mv                       |    272288261
>  site_ranking_mv                     |    272260588
>  carrier_ranking_mv                  |    272273002
>  brand_ranking_mv                    |    226575108
>  device_specs_ranking_mv             |    182006046
>  user_agents_canonical_user_agent_os |    129807014
> (6 rows)
> 
> Of those, the last 3 get concurrent refreshes, the first 3 don't.
> 
> In the upgraded server, vacuum complained with:
> 
> INFO:  vacuuming "public.user_agents_canonical_user_agent_os"
> vacuumdb: vacuuming of database "mat" failed: ERROR:  found xmin
> 244738497 from before relfrozenxid 245830003
> 
> Now, 245830003 looks a lot like the current xid during pg_upgrade, so
> I believe pg_dump is somehow failing to restore relfrozenxid on those
> matviews. In fact, trying pg_dump --binary-upgrade on any matview
> shows that it's not setting relfrozenxid, probably because in a normal
> dump, matviews are refreshed, but not when --binary-upgrade is used
> (since it's usually used with --schema-only as well).

The important part then happens in pg_dump. Note

        /*
         * To create binary-compatible heap files, we have to ensure the same
         * physical column order, including dropped columns, as in the
         * original.  Therefore, we create dropped columns above and drop them
         * here, also updating their attlen/attalign values so that the
         * dropped column can be skipped properly.  (We do not bother with
         * restoring the original attbyval setting.)  Also, inheritance
         * relationships are set up by doing ALTER TABLE INHERIT rather than
         * using an INHERITS clause --- the latter would possibly mess up the
         * column order.  That also means we have to take care about setting
         * attislocal correctly, plus fix up any inherited CHECK constraints.
         * Analogously, we set up typed tables using ALTER TABLE / OF here.
         */
        if (dopt->binary_upgrade &&
            (tbinfo->relkind == RELKIND_RELATION ||
             tbinfo->relkind == RELKIND_FOREIGN_TABLE ||
             tbinfo->relkind == RELKIND_PARTITIONED_TABLE))
        {
...
            appendPQExpBufferStr(q, "\n-- For binary upgrade, set heap's relfrozenxid and relminmxid\n");
            appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n"
                              "SET relfrozenxid = '%u', relminmxid = '%u'\n"
                              "WHERE oid = ",
                              tbinfo->frozenxid, tbinfo->minmxid);
            appendStringLiteralAH(q, fmtId(tbinfo->dobj.name), fout);
            appendPQExpBufferStr(q, "::pg_catalog.regclass;\n");

note that the above if clause doesn't include materialized tables. Which
sems to explain this bug?  Could you check that just updating the above
if to include matviews fixes the bug for you?


Looking into this I also saw:

/*
 *    set_frozenxids()
 *
 *    We have frozen all xids, so set datfrozenxid, relfrozenxid, and
 *    relminmxid to be the old cluster's xid counter, which we just set
 *    in the new cluster.  User-table frozenxid and minmxid values will
 *    be set by pg_dump --binary-upgrade, but objects not set by the pg_dump
 *    must have proper frozen counters.
 */
static
void
set_frozenxids(bool minmxid_only)
...
            /* set pg_class.relfrozenxid */
            PQclear(executeQueryOrDie(conn,
                                      "UPDATE    pg_catalog.pg_class "
                                      "SET    relfrozenxid = '%u' "
            /* only heap, materialized view, and TOAST are vacuumed */
                                      "WHERE    relkind IN ("
                                      CppAsString2(RELKIND_RELATION) ", "
                                      CppAsString2(RELKIND_MATVIEW) ", "
                                      CppAsString2(RELKIND_TOASTVALUE) ")",
                                      old_cluster.controldata.chkpnt_nxtxid));

which makes a bit uncomfortable, but I can't quite put my finger on
why.

Greetings,

Andres Freund