Sorry, realised I could have elaborated a bit more on this one. Running against PG11.4 provided by Docker Hub this time.
I've added a single update statement which just reverses the last change in the original SQL: UPDATE test SET col = 4 WHERE col = 14; Running a concurrent update I get: REFRESH MATERIALIZED VIEW CONCURRENTLY m_test; SELECT * FROM m_test; col ----- 1 2 5 6 7 8 9 10 13 4 (10 rows) But then if I go ahead and do a non-concurrent refresh with no further data changes: REFRESH MATERIALIZED VIEW m_test; SELECT * FROM m_test; col ----- 1 2 4 5 6 7 8 9 10 13 (10 rows) which is what I was expecting. On Wed, 24 Apr 2019 at 12:23, Awad Mackie <a...@zyper.com> wrote: > 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 <br...@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 <br...@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 > > > -- *Awad Mackie* Lead Backend Engineer, Zyper
