Обсуждение: pglister: issue with materialized view after upgrade (+ solution)

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'svery 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?
-- 
Célestin Matte

On Fri, Sep 29, 2023 at 11:17 AM Célestin Matte
<celestin.matte@cmatte.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'svery 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/

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

I used pg_upgradecluster with no additional option (sudo -u postgres pg_upgradecluster 12 main).

> This may be an issue in how pg_upgradecluster executes it perhaps?

Do you mean that the issue could be with pg_upgradecluster then?

-- 
Célestin Matte