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

From: Célestin Matte <[email-masked]>
Date: Fri, 29 Sep 2023 17:17:03 +0200

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

---

From: Magnus Hagander <[email-masked]>
Date: Fri, 6 Oct 2023 18:15:04 -0400

> - Push a patch to somehow avoid this issue from happening or adding a transparent workaround?

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/

---

From: Célestin Matte <[email-masked]>
Date: Mon, 9 Oct 2023 14:31:16 +0200

> 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