Re: pglister: issue with materialized view after upgrade (+ solution)

On Fri, Sep 29, 2023 at 11:17 AM Célestin Matte
<celestin(dot)matte(at)cmatte(dot)me> wrote:
>
> After upgrading postgresql (with pg_upgradecluster), I got the following error when loading pglister's /moderate page:
>
> django.db.utils.NotSupportedError: CONCURRENTLY cannot be used when the materialized view is not populated
>
> It seems like the eximintegration.queue materialized view was lost in the upgrade process. It's originally created in Django's very first migration.
>
> The fix was to run directly in the database: refresh materialized view eximintegration.queue;
>
> From there, two things could be done:
> - Document it somewhere?
> - Push a patch to somehow avoid this issue from happening or adding a transparent workaround?
>
> What do you think?

Hm. I guess the problem here is that `pg_dump -s` creates the view
WITH NO DATA. But how exactly did you run the upgrade -- a normal
`pg_dump` of a pglister database will include a `REFRESH MATERIALIZED
VIEW` command right at the end. This may be an issue in how
pg_upgradecluster executes it perhaps?

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/