Re: REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: awad(at)zyper(dot)com, pgsql-docs(at)lists(dot)postgresql(dot)org
Subject: Re: REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY
Date: 2019-04-11 20:39:01
Message-ID: 20190411203901.ksxws4qhv3fzijoe@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

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 +

Attachment Content-Type Size
materialized.sql application/x-sql 409 bytes

In response to

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Bruce Momjian 2019-04-12 01:48:46 Re: Clarification to pg_upgrade docs on reverting to old cluster
Previous Message Bruce Momjian 2019-04-11 17:26:11 Re: Small mistake (grammar/wording)