CODEWITHSUNDEEP
postgresqlpostgresql-9.5materialized-views
Nikolay Ageev
Nikolay Ageev

Reputation: 601

Postgresql REFRESH MATERIALIZED VIEW CONCURRENTLY breaks the order

I have materialized view and it's very helpful. But after some time of using it I got issue that order in view is broken. I created Matview as:

CREATE MATERIALIZED VIEW mat_view_sorted_products AS
      SELECT item.id, item.category_id FROM item
      INNER JOIN stock ON stock.item_id = item.id
      ORDER BY is_available DESC, views DESC;

CREATE UNIQUE INDEX mat_view_index_id ON mat_view_sorted_products (id);

ORDER BY in my case is very important thing, so it works when I query from Matview and got sorted result of joining tables:

SELECT id FROM mat_view_sorted_products
LIMIT 100 OFFSET 0; //got 100 sorted item ids by (is_available DESC, views DESC)

To refresh Materialized view I use: REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_sorted_products

But after some time and many refreshing iterations I saw that the order of matview records is broken. I thought that CONCURRENTLY updates values of free (of reading) records and do not change anything else.

Not? How is it working and how not to break ORDER while refreshing View?

P.S.

if I do REFRESH without CONCURRENTLY it restores normal sorting. It blocks view, cleans it and restores from select with order. But users do not have time to wait each time View is refreshing

UPDATE:

Of course I use order in my full SELECT statement:

SELECT id, name, views FROM item WHERE id IN (SELECT id FROM mat_view_sorted_products
LIMIT 100 OFFSET 0) ORDER BY is_available DESC, views DESC;

And it makes Sequence Scan on mat_view_sorted_products and I got 100 sorted items of all sorted items in view. As I think the view creates like:

  1. got 200 items ordered by ... from AS SELECT
  2. inserted them into MatView
  3. doing seq scan on 200 sorted items to get first 100 sorted items of all

Upvotes: 1

Views: 1711

Answers (1)

user330315
user330315

Reputation:

No, the order is "not broken" because there is no such thing as the "order" of rows in a table.

Tables (and that includes materialized views) represent unorder sets and they have no implied order.

The only way to get a guaranteed sort order is to use order by in your SELECT statement. There is no alternative.

Remove the order by from your CREATE MATERIALIZED view statement and add it to your actual SELECT statement. Using LIMIT or OFFSET without an ORDER BY is a bug in your code.

Upvotes: 8

Related Questions