public inbox for [email protected]help / color / mirror / Atom feed
pglister: issue with materialized view after upgrade (+ solution) 3+ messages / 2 participants [nested] [flat]
* pglister: issue with materialized view after upgrade (+ solution) @ 2023-09-29 15:17 Célestin Matte <[email protected]> 2023-10-06 22:15 ` Re: pglister: issue with materialized view after upgrade (+ solution) Magnus Hagander <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Célestin Matte @ 2023-09-29 15:17 UTC (permalink / raw) To: PostgreSQL WWW <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pglister: issue with materialized view after upgrade (+ solution) 2023-09-29 15:17 pglister: issue with materialized view after upgrade (+ solution) Célestin Matte <[email protected]> @ 2023-10-06 22:15 ` Magnus Hagander <[email protected]> 2023-10-09 12:31 ` Re: pglister: issue with materialized view after upgrade (+ solution) Célestin Matte <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Magnus Hagander @ 2023-10-06 22:15 UTC (permalink / raw) To: Célestin Matte <[email protected]>; +Cc: PostgreSQL WWW <[email protected]> On Fri, Sep 29, 2023 at 11:17 AM Célestin Matte <[email protected]> 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/ ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pglister: issue with materialized view after upgrade (+ solution) 2023-09-29 15:17 pglister: issue with materialized view after upgrade (+ solution) Célestin Matte <[email protected]> 2023-10-06 22:15 ` Re: pglister: issue with materialized view after upgrade (+ solution) Magnus Hagander <[email protected]> @ 2023-10-09 12:31 ` Célestin Matte <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Célestin Matte @ 2023-10-09 12:31 UTC (permalink / raw) To: Magnus Hagander <[email protected]>; +Cc: PostgreSQL WWW <[email protected]> > 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-10-09 12:31 UTC | newest] Thread overview: 3+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-09-29 15:17 pglister: issue with materialized view after upgrade (+ solution) Célestin Matte <[email protected]> 2023-10-06 22:15 ` Magnus Hagander <[email protected]> 2023-10-09 12:31 ` Célestin Matte <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox