Why is ordering ignored when creating view for PostgreSQL?

Question

I have a PostgreSQL (version 12.15) database with a table like this:

id	created_at	number	chat_id
...	2023-08-10 21:44:19.272299	0	27936
...	2023-08-09 10:32:28.850167	0	27936
...	2023-08-08 17:32:11.207523	0	27936
...	2023-08-07 17:16:52.068309	0	27936
...	2023-08-06 17:15:47.014364	0	27936

This result was received from a query

SELECT * 
FROM _table_name 
WHERE chat_id = '27936' 
ORDER BY created_at DESC

To put down the value of number, I execute the following queries:

CREATE VIEW view_message 
AS 
    SELECT * 
    FROM _message 
    ORDER BY created_at;

CREATE RULE rule_message AS ON UPDATE TO view_message
    DO INSTEAD UPDATE _message SET number = NEW.number WHERE id = NEW.id;

UPDATE view_message
SET number = create_sequence_number(chat_id::text);

But after it I run

SELECT * 
FROM _table_name 
WHERE chat_id = '27936' 
ORDER BY created_at DESC

again, I get an output like this (number values are wrong):

id	created_at	number	chat_id
...	2023-08-10 21:44:19.272299	83	27936
...	2023-08-09 10:32:28.850167	82	27936
...	2023-08-08 17:32:11.207523	33	27936
...	2023-08-07 17:16:52.068309	82	27936
...	2023-08-06 17:15:47.014364	120	27936

number was to be maxed out for the topmost line and decremented by one for each successive.

It seems that ORDER BY created_at was not taken into account when creating view. But still I do not understand what exactly happened and why the error happened.

---

create_sequence_number is the function that generates a sequence number depending on the passed id value.

Answer 1

Create View

CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query.

create table order_test(id integer, fld_1 varchar);

insert into order_test values (1, 'dog'), (2, 'fish'), (3, 'cat');

select * from order_test;
 id | fld_1 
----+-------
  1 | dog
  2 | fish
  3 | cat
(3 rows)

create view vw_order_test as select * from order_test order by fld_1;

select * from vw_order_test ;
 id | fld_1 
----+-------
  3 | cat
  1 | dog
  2 | fish
(3 rows)

insert into order_test values (4, 'aardvark');

select * from order_test;
 id |  fld_1   
----+----------
  1 | dog
  2 | fish
  3 | cat
  4 | aardvark


select * from vw_order_test ;
 id |  fld_1   
----+----------
  4 | aardvark
  3 | cat
  1 | dog
  2 | fish

The ordering is honored.

UPDATE

create table big_table(id integer, fld_1 numeric);

insert into big_table select a, random() * 1000 from generate_series(1, 100000) as t(a);

  id   |        fld_1        
--------+---------------------
      1 |    763.817657064134
      2 |    980.199684623081
      3 |    244.682555561287
      4 |    643.861135921256
      5 |    144.942847930761
...

99996 |    230.111102236449
  99997 |    127.037526956709
  99998 |    66.7148919657663
  99999 |    749.832473831365
 100000 |    504.281936762339


create view vw_big_table as select * from big_table order by fld_1;

  id   |        fld_1        
--------+---------------------
   4858 | 0.00708054987086371
  68190 |  0.0139485327323996
  45454 |     0.0247458957503
  92812 |  0.0274875299197674

...

 22411 |    999.978586972237
  55171 |    999.980785632108
  44598 |    999.987221914829
  30712 |    999.993384242487
  18883 |     999.99984164636

explain select * from vw_big_table ;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Sort  (cost=9854.82..10104.82 rows=100000 width=15)
   Sort Key: big_table.fld_1
   ->  Seq Scan on big_table  (cost=0.00..1550.00 rows=100000 width=15)

This remains the same if I go to 1000000 rows or 10000000 rows.

Answer 2

You can't actually update a view. Only tables can be updated. The statement update view is converted to update table (possibly with some restrictions).

order by is only applied to the rowset returned from a select. It doesn't affect the order rows are accessed in the table, but your update statement is sensitive to access order.