inconsistent page ordering

[jgbaum]

Environment

• PostgreSQL version: 10.6
• PostgREST version: docker.io/swaggerapi/swagger-ui:latest (3a716b3c198f9050f07d852a936facb9be3f1d9237294daf73044f03a19c8baa)
• Operating system: CentOS 8

Description of issue

I've been having issues with inconsistent pages between queries on a large table. The query that I'm running (on a single table) returns ~80,000 rows and our default page size (PGRST_MAX_ROWS) is set to 10,000. When I run successive queries with the 'offset' parameter moving from 0 to 80,000 in steps of 10,000, I've noticed that the result order is different between queries. So, e.g., some of the rows that were on page 1 when I ran the query with offset=0 will show up again on page 2, 3, 4, etc.

I can mitigate this by adding an 'order' parameter to the query, but was wondering if there is a cleaner / more foolproof solution?

Thanks for your assistance and I would be happy to provide any additional details, as needed.

[wolfgangwalther]

I can mitigate this by adding an 'order' parameter to the query, but was wondering if there is a cleaner / more foolproof solution?

PostgreSQL does not guarantee sort order without ORDER BY. You are adding that with the order= parameter - which is exactly what you should be doing. This is the correct solution.