BUG #15380: Sorting paging data loss

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: 1726002692(at)qq(dot)com
Subject: BUG #15380: Sorting paging data loss
Date: 2018-09-12 03:09:29
Message-ID: 153672176900.23141.14337441776284994710@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15380
Logged by: 强 魏
Email address: 1726002692(at)qq(dot)com
PostgreSQL version: 10.5
Operating system: CentOS 7 X86_64
Description:

postgres(at)pg12[127(dot)0(dot)0(dot)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(at)pg12[127(dot)0(dot)0(dot)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(at)pg12[127(dot)0(dot)0(dot)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(at)pg12[127(dot)0(dot)0(dot)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 )

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2018-09-12 03:30:40 Re: BUG #15380: Sorting paging data loss
Previous Message Michael Paquier 2018-09-12 02:27:44 Re: BUG #15376: Postgres sql 9.4.19 pg_upgrade stops with error The source cluster was not shut down cleanly.