BUG #15380: Sorting paging data loss

Started by PG Bug reporting formover 7 years ago2 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

The following bug has been logged on the website:

Bug reference: 15380
Logged by: 强 魏
Email address: 1726002692@qq.com
PostgreSQL version: 10.5
Operating system: CentOS 7 X86_64
Description:

postgres@pg12[127.0.0.1:5432]#\d+ tt
                                                           Table
"public.tt"
+--------+-----------------------------+-----------+----------+--------------------------------+---------+--------------+-------------+
| Column |            Type             | Collation | Nullable |           
Default             | Storage | Stats target | Description |
+--------+-----------------------------+-----------+----------+--------------------------------+---------+--------------+-------------+
| id     | integer                     |           | not null |
nextval('tt_id_seq'::regclass) | plain   |              |             |
| time   | timestamp without time zone |           |          |             
                  | plain   |              |             |
+--------+-----------------------------+-----------+----------+--------------------------------+---------+--------------+-------------+
postgres@pg12[127.0.0.1:5432]#select ctid,* from tt order by id;
+--------+----+----------------------------+
|  ctid  | id |            time            |
+--------+----+----------------------------+
| (0,1)  |  1 | 2018-09-12 10:54:35.997079 |
| (0,2)  |  2 | 2018-09-12 10:54:37.61052  |
| (0,3)  |  3 | 2018-09-12 10:54:38.481238 |
| (0,4)  |  4 | 2018-09-12 10:54:39.443595 |
| (0,5)  |  5 | 2018-09-12 10:54:40.256502 |
| (0,19) |  6 | 2018-09-12 10:54:43.650884 |
| (0,6)  |  6 | 2018-09-12 10:54:41.035477 |
| (0,17) |  6 | 2018-09-12 10:54:41.992702 |
| (0,18) |  6 | 2018-09-12 10:54:42.845176 |
| (0,10) | 10 | 2018-09-12 10:54:44.511766 |
| (0,11) | 11 | 2018-09-12 10:54:45.358093 |
| (0,12) | 12 | 2018-09-12 10:54:46.14805  |
| (0,13) | 13 | 2018-09-12 10:54:46.914753 |
| (0,14) | 14 | 2018-09-12 10:54:47.718782 |
| (0,15) | 15 | 2018-09-12 10:54:48.528458 |
| (0,16) | 16 | 2018-09-12 10:54:49.263626 |
+--------+----+----------------------------+
(16 rows)
Time: 8.453 ms
postgres@pg12[127.0.0.1:5432]#select ctid,* from tt order by id limit 7
offset 0;
+--------+----+----------------------------+
|  ctid  | id |            time            |
+--------+----+----------------------------+
| (0,1)  |  1 | 2018-09-12 10:54:35.997079 |
| (0,2)  |  2 | 2018-09-12 10:54:37.61052  |
| (0,3)  |  3 | 2018-09-12 10:54:38.481238 |
| (0,4)  |  4 | 2018-09-12 10:54:39.443595 |
| (0,5)  |  5 | 2018-09-12 10:54:40.256502 |
| (0,6)  |  6 | 2018-09-12 10:54:41.035477 |
| (0,17) |  6 | 2018-09-12 10:54:41.992702 |
+--------+----+----------------------------+
(7 rows)
Time: 7.970 ms
postgres@pg12[127.0.0.1:5432]#select ctid,* from tt order by id limit 7
offset 7;
+--------+----+----------------------------+
|  ctid  | id |            time            |
+--------+----+----------------------------+
| (0,17) |  6 | 2018-09-12 10:54:41.992702 |
| (0,18) |  6 | 2018-09-12 10:54:42.845176 |
| (0,10) | 10 | 2018-09-12 10:54:44.511766 |
| (0,11) | 11 | 2018-09-12 10:54:45.358093 |
| (0,12) | 12 | 2018-09-12 10:54:46.14805  |
| (0,13) | 13 | 2018-09-12 10:54:46.914753 |
| (0,14) | 14 | 2018-09-12 10:54:47.718782 |
+--------+----+----------------------------+

but this tuple is lost ((0,19) , 6 , 2018-09-12 10:54:43.650884 )

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: PG Bug reporting form (#1)
Re: BUG #15380: Sorting paging data loss

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:

[ underspecified ORDER BY produces variable row ordering ]

I don't see a bug here. If you want consistent row ordering, you
need to give an ORDER BY that uniquely orders the rows.

regards, tom lane