pg_upgrade and materialized views
От | Claudio Freire |
---|---|
Тема | pg_upgrade and materialized views |
Дата | |
Msg-id | CAGTBQpbrY9CdRGGhyBZ9yqY4jWaGC85rUF4X+R7d-aim=mBNsw@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: pg_upgrade and materialized views
Re: pg_upgrade and materialized views Re: pg_upgrade and materialized views |
Список | 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.
В списке pgsql-bugs по дате отправления: