[PATCH] Make select_views regression test output deterministic
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253359psql -h localhost -U postgresBuilt from patchset v1 (message #1), August 23, 2026 at 01:46 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253359_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253359_1 && git checkout t253359_1Patchset v1 (message #1) is on t253359_1
Greetings,
select_views runs "SELECT * FROM street" with no ORDER BY, and its iexit
query, "SELECT name, #thepath", orders only by name and the point count.
Both lean on heap scan order for the rest, so the expected output isn't
deterministic and can shift with page layout or scan order.
The fix gives each query a total ordering (COLLATE "C" so it's
locale-independent). Since the ordering covers every output column, rows a
scan could reorder are identical in the output anyway, so the result is
stable.
The expected-output diff is large, but it's only reordered rows. The lines
are wide because thepath is a geometric path column, and no query result
changes. Full regression passes with the regenerated file.
--
Bryan Green
EDB: https://www.enterprisedb.com
Bryan Green <dbryan.green@gmail.com> writes:
select_views runs "SELECT * FROM street" with no ORDER BY, and its iexit
query, "SELECT name, #thepath", orders only by name and the point count.
Both lean on heap scan order for the rest, so the expected output isn't
deterministic and can shift with page layout or scan order.
The fix gives each query a total ordering (COLLATE "C" so it's
locale-independent). Since the ordering covers every output column, rows a
scan could reorder are identical in the output anyway, so the result is
stable.
I'm disinclined to accept this patch. You've shown no actual evidence
of output instability, and we have decades of experience showing the
contrary.
I'm not unsympathetic to the idea that this test could be a headache
for someone trying to run the regression tests atop a different table
AM --- but if we were trying to make the tests safe for scenarios
like that, there are hundreds of other places that would have to
change too. And the implications of doing that are not great.
As the manual explains:
You might wonder why we don't order all the regression test queries explicitly
to get rid of this issue once and for all. The reason is that that would
make the regression tests less useful, not more, since they'd tend
to exercise query plan types that produce ordered results to the
exclusion of those that don't.
I suspect that in a fair number of places, we'd actually find
ourselves breaking the intent of the test case because it would
now not exercise the plan shape it was made to test. So moving
the goalposts meaningfully in this respect, while preserving
test coverage, seems like a pretty deep research project.
But short of that, I don't see much point in one-off fixes.
regards, tom lane
On 8/9/26 10:04, Tom Lane wrote:
Bryan Green <dbryan.green@gmail.com> writes:
select_views runs "SELECT * FROM street" with no ORDER BY, and its iexit
query, "SELECT name, #thepath", orders only by name and the point count.
Both lean on heap scan order for the rest, so the expected output isn't
deterministic and can shift with page layout or scan order.
The fix gives each query a total ordering (COLLATE "C" so it's
locale-independent). Since the ordering covers every output column, rows a
scan could reorder are identical in the output anyway, so the result is
stable.I'm disinclined to accept this patch. You've shown no actual evidence
of output instability, and we have decades of experience showing the
contrary.I'm not unsympathetic to the idea that this test could be a headache
for someone trying to run the regression tests atop a different table
AM --- but if we were trying to make the tests safe for scenarios
like that, there are hundreds of other places that would have to
change too. And the implications of doing that are not great.
As the manual explains:You might wonder why we don't order all the regression test queries explicitly
to get rid of this issue once and for all. The reason is that that would
make the regression tests less useful, not more, since they'd tend
to exercise query plan types that produce ordered results to the
exclusion of those that don't.I suspect that in a fair number of places, we'd actually find
ourselves breaking the intent of the test case because it would
now not exercise the plan shape it was made to test. So moving
the goalposts meaningfully in this respect, while preserving
test coverage, seems like a pretty deep research project.
But short of that, I don't see much point in one-off fixes.regards, tom lane
Tom,
Agreed, and thanks for spelling out the coverage rationale. It does seem
like an interesting deep research project, but I won't be adding this to
the commitfest.
--
Bryan Green
EDB: https://www.enterprisedb.com