Question 235071 in PostgreSQL Question posted in
The official documentation can be found here.

Why is ordering ignored when creating view for PostgreSQL?

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search