pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedREFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY
4+ messages / 3 participants
[nested] [flat]
* REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY
@ 2019-04-04 14:54 PG Doc comments form <noreply@postgresql.org>
2019-04-11 20:39 ` Re: REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 4+ messages in thread
From: PG Doc comments form @ 2019-04-04 14:54 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: awad@zyper.com
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=2aac63...
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY
2019-04-04 14:54 REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY PG Doc comments form <noreply@postgresql.org>
@ 2019-04-11 20:39 ` Bruce Momjian <bruce@momjian.us>
2019-04-24 11:23 ` Re: REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY Awad Mackie <awad@zyper.com>
0 siblings, 1 reply; 4+ messages in thread
From: Bruce Momjian @ 2019-04-11 20:39 UTC (permalink / raw)
To: awad@zyper.com; pgsql-docs@lists.postgresql.org
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=2aac63...
> 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 +
Attachments:
materialized.sql (application/x-sql, 409B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY
2019-04-04 14:54 REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY PG Doc comments form <noreply@postgresql.org>
2019-04-11 20:39 ` Re: REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY Bruce Momjian <bruce@momjian.us>
@ 2019-04-24 11:23 ` Awad Mackie <awad@zyper.com>
2019-07-24 17:22 ` Re: REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY Awad Mackie <awad@zyper.com>
0 siblings, 1 reply; 4+ messages in thread
From: Awad Mackie @ 2019-04-24 11:23 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs@lists.postgresql.org
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=2aac63...
> > 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
Attachments:
materialized.sql (application/sql, 577B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY
2019-04-04 14:54 REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY PG Doc comments form <noreply@postgresql.org>
2019-04-11 20:39 ` Re: REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY Bruce Momjian <bruce@momjian.us>
2019-04-24 11:23 ` Re: REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY Awad Mackie <awad@zyper.com>
@ 2019-07-24 17:22 ` Awad Mackie <awad@zyper.com>
0 siblings, 0 replies; 4+ messages in thread
From: Awad Mackie @ 2019-07-24 17:22 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs@lists.postgresql.org
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 <awad@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 <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=2aac63...
>> > 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
>
>
>
--
*Awad Mackie*
Lead Backend Engineer, Zyper
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-07-24 17:22 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-04-04 14:54 REFRESH MATERIALIZED VIEW CONCURRENTLY interaction with ORDER BY PG Doc comments form <noreply@postgresql.org>
2019-04-11 20:39 ` Bruce Momjian <bruce@momjian.us>
2019-04-24 11:23 ` Awad Mackie <awad@zyper.com>
2019-07-24 17:22 ` Awad Mackie <awad@zyper.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox