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.