MIME-Version: 1.0
References: <8a949577-ce99-4590-9f31-3fc5d5c162f6@cmatte.me>
In-Reply-To: <8a949577-ce99-4590-9f31-3fc5d5c162f6@cmatte.me>
From: Magnus Hagander <magnus@hagander.net>
Date: Fri, 6 Oct 2023 18:15:04 -0400
Message-ID: 
 <CABUevEw3MaXWAT5pf7EPP_-oFJSpX7FVP0jgf71ps_jWnxPKaA@mail.gmail.com>
Subject: Re: pglister: issue with materialized view after upgrade (+ solution)
To: =?UTF-8?Q?C=C3=A9lestin_Matte?= <celestin.matte@cmatte.me>
Cc: PostgreSQL WWW <pgsql-www@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CABUevEw3MaXWAT5pf7EPP_-oFJSpX7FVP0jgf71ps_jWnxPKaA%40mail.gmail.com>
Precedence: bulk

On Fri, Sep 29, 2023 at 11:17=E2=80=AFAM C=C3=A9lestin 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 m=
aterialized 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 ex=
imintegration.queue;
>
>  From there, two things could be done:
> - Document it somewhere?
> - Push a patch to somehow avoid this issue from happening or adding a tra=
nsparent 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?

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