更新行后，为什么 postgres 在选择查询中返回无序数据? [英] Why postgres returns unordered data in select query, after updation of row?

问题描述

我对 postgres 返回的行的默认顺序有点困惑.

I am bit confused over default ordering of the rows returned by postgres.

postgres=# select * from check_user;
 id | name
----+------
  1 | x
  2 | y
  3 | z
  4 | a
  5 | c1\
  6 | c2
  7 | c3
(7 rows)

postgres=# update check_user set name = 'c1' where name = 'c1\';
UPDATE 1
postgres=# select * from check_user;
 id | name
----+------
  1 | x
  2 | y
  3 | z
  4 | a
  6 | c2
  7 | c3
  5 | c1
(7 rows)

在任何更新之前，它返回按 id 排序的行，但更新后，顺序已更改.所以我的问题是，如果没有指定 order by，postgres 使用什么默认排序?

Before any updation, it was returning rows ordered by id, but after updation, the order has changed. So my question is that if order by is not specified, what default ordering does postgres uses ?

提前致谢.

推荐答案

简单来说，默认顺序"是从磁盘读取的任何内容.更新一行不会原地改变行...通常会将旧行标记为已删除并写入新行.

Put very simply the "default order" is whatever it happens to read from the disk. Updating a row will not change the row in place... Usually it marks the old row as deleted and writes a new one.

当 postgres 从内存页面读取行时，它(可能)会按照它们在页面上的存储顺序读取它们.它将以它认为最快的任何顺序读取页面(这可能是也可能不是它们在磁盘上的显示方式).它可以根据是否决定使用索引而改变.因此，它可以在您的应用不要求任何不同的情况下突然发生变化.

When postgres reads rows from pages of memory, it will (probably) read them in the order they are stored on the page. It will read pages in whatever order it thinks is quickest (that may or may not be how they appear on disk). It can change based on whether or not it decides to use an index. So it can suddenly change without your app asking for anything different.

如果您不指定order by，则不会采取任何操作来重新排序它们.

If you don't specify an order by it will not take any action to re-order them.

永远不要依赖默认顺序.这是未定义的行为.

NEVER rely on the default order. It is undefined behaviour.

这篇关于更新行后，为什么 postgres 在选择查询中返回无序数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！