pg_upgrade and materialized views

From: Claudio Freire <klaussfreire(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: pg_upgrade and materialized views
Date: 2018-02-20 21:13:26
Message-ID: CAGTBQpbrY9CdRGGhyBZ9yqY4jWaGC85rUF4X+R7d-aim=mBNsw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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).

I haven't yet managed to build a minimal case to reproduce this, I'll
post it when I succeed, but I wanted to report the issue now since it
looks like a genuine bug.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2018-02-20 21:26:59 Re: pg_upgrade and materialized views
Previous Message Andrew Gierth 2018-02-20 18:35:18 Re: BUG #15074: psql client never returns when creating index (long running operation)