Обсуждение: BUG #15851: Concurrent Refresh of Materialized views not preserving the order of the underlying query
BUG #15851: Concurrent Refresh of Materialized views not preserving the order of the underlying query
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 15851 Logged by: Sai D Email address: sai.dulla@berkeley.edu 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
PG Bug reporting form <noreply@postgresql.org> 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
BUG #15851: Concurrent Refresh of Materialized views not preservingthe order of the underlying query
От
"David G. Johnston"
Дата:
On Thursday, June 13, 2019, Tom Lane <tgl@sss.pgh.pa.us> wrote:
PG Bug reporting form <noreply@postgresql.org> 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.
On Fri, 14 Jun 2019 at 12:29, Tom Lane <tgl@sss.pgh.pa.us> 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
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