Re: REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY

From: Awad Mackie <awad(at)zyper(dot)com>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: pgsql-docs(at)lists(dot)postgresql(dot)org
Subject: Re: REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY
Date: 2019-04-24 11:23:27
Message-ID: CAJOOwwmXO8e5P7hD2eb8ggzQuWQYjaM3nLa09ELDzNkd7AO5zA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

I've added 5 more statements to the attached SQL to demonstrate the problem
in the last two SELECTs. Ran it against PG 11.2 provided by Docker Hub this
time and the problem is still visible.

On Thu, 11 Apr 2019 at 21:39, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Thu, Apr 4, 2019 at 02:54:29PM +0000, PG Doc comments form wrote:
> > The following documentation comment has been logged on the website:
> >
> > Page:
> https://www.postgresql.org/docs/10/sql-refreshmaterializedview.html
> > Description:
> >
> > On 10.7 we hit a case where the backing query had an order by clause and
> a
> > concurrent refresh updated it differently than a normal refresh. This is
> a
> > bit of an odd corner case given that up till that point, views would
> respect
> > the ordering.
> >
> > It's not explicit that CONCURRENTLY populates the data any differently
> than
> > normal, specifically, in a way that means the ordering in the backing
> query
> > is not always maintained, depending on the order of updates.
> >
> > Reading through the comment at
> >
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/matview.c;h=2aac63296bfee535af3ea660c617b265d7ec8042;hb=HEAD#l548
> > I can see the logic in that, but it could use an explicit mention in the
> > CONCURRENTLY section. Not sure if there's any plan for changing the
> > behaviour either.
> >
> > Also the existing sentence "If you want the data to be ordered upon
> > generation, you must use an ORDER BY clause in the backing query." sort
> of
> > implies that the ORDER BY will be respected.
>
> I ran the attached SQL file on PG 10.7 and PG head and got output that
> honored the ORDER BY. Is the test wrong? Is something else needed to
> see the ordering fail. Can you provide an example of the failure? If I
> remove the ORDER BY from the materialized view, I do get randomly
> ordered rows.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + As you are, so once was I. As I am, so you will be. +
> + Ancient Roman grave inscription +
>

--
Awad Mackie
Lead Backend Engineer

Attachment Content-Type Size
materialized.sql application/sql 577 bytes

In response to

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Peter Eisentraut 2019-04-24 18:58:27 Re: Passphrase protected SSL key and reloads
Previous Message Joe Conway 2019-04-24 11:22:03 Re: Passphrase protected SSL key and reloads