Re: REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY

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@momjian.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@momjian.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