BUG #15851: Concurrent Refresh of Materialized views not preserving the order of the underlying query

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #15851: Concurrent Refresh of Materialized views not preserving the order of the underlying query

apt.postgresql.org Repository Update
The following bug has been logged on the website:

Bug reference:      15851
Logged by:          Sai D
Email address:      [hidden email]
PostgreSQL version: 10.5
Operating system:   Ubuntu 16.04.10
Description:        

Tests Run:
Materialized view refresh concurrently with unique index:
Sort order is not the same as that of the output of the underlying query

Tested with Unique Index key that is the same as the sort order columns in
the underlying query.
Results: Incorrect sort order after refresh concurrently
Tested with Unique Index key that is different from the sort order columns
in the underlying query.
Results: Incorrect sort order after refresh concurrently


Materialized view refresh with unique index (Plain refresh, no concurrently
key word in the refresh command):
Sort order is the same as that of the output of the underlying query

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15851: Concurrent Refresh of Materialized views not preserving the order of the underlying query

Tom Lane-2
PG Bug reporting form <[hidden email]> writes:
> Materialized view refresh concurrently with unique index:
> Sort order is not the same as that of the output of the underlying query

I do not think this is a bug.  A matview is basically a table, and
tables don't guarantee to preserve row ordering.

Possibly we need to clarify the docs around this point.

A more aggressive approach would be to reject ORDER BY in the
query defining a matview, but perhaps that's too in-your-face...

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

BUG #15851: Concurrent Refresh of Materialized views not preserving the order of the underlying query

David G Johnston
On Thursday, June 13, 2019, Tom Lane <[hidden email]> wrote:
PG Bug reporting form <[hidden email]> writes:
> Materialized view refresh concurrently with unique index:
> Sort order is not the same as that of the output of the underlying query

I do not think this is a bug.  A matview is basically a table, and
tables don't guarantee to preserve row ordering.

Possibly we need to clarify the docs around this point.

A more aggressive approach would be to reject ORDER BY in the
query defining a matview, but perhaps that's too in-your-face...


The notes section for refresh already mentions cluster and adding an explicit order by; and they both seem like good points when working with non-concurrent semantics.  Probably should just add another sentence or two:

When operating in CONCURRENTLY mode the deltas that are calculated are applied as a sequence of normal insert/update/delete.  A subsequent CLUSTER would need to be run on the materialized view if one wishes for the entire table to be physically ordered again (though consider a lower fill-factor for the materialized view to reduce the amount of work such a cluster would need to accomplish).

 David J.

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15851: Concurrent Refresh of Materialized views not preserving the order of the underlying query

David Rowley-3
In reply to this post by Tom Lane-2
On Fri, 14 Jun 2019 at 12:29, Tom Lane <[hidden email]> wrote:
> A more aggressive approach would be to reject ORDER BY in the
> query defining a matview, but perhaps that's too in-your-face...

Yeah. I think if we'd thought about it at the time we'd probably have
rejected an ORDER BY in the view definition. Doing that today might
break pg_upgrade and pg_restore.  Do you think there's any merit in a
WARNING during CREATE MATERIALIZED VIEW if the query has an ORDER BY?

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: BUG #15851: Concurrent Refresh of Materialized views not preserving the order of the underlying query

Andres Freund
In reply to this post by Tom Lane-2
Hi,

On 2019-06-13 20:28:54 -0400, Tom Lane wrote:
> A more aggressive approach would be to reject ORDER BY in the
> query defining a matview, but perhaps that's too in-your-face...

That'd probably be over the top - ISTM it can make plenty of sense to
"pre order" a matview, especially if you're going to create several
indexes.

Greetings,

Andres Freund