Subject: Inherit regression outputs rows in alternative ordering when run on other table AM than heap

From: Pavel Borisov <[email-masked]>
Date: Fri, 27 Mar 2026 16:54:25 +0400

Hi, hackers!

Existing inherit regression test results are tied to the particular
row order after UPDATE clause. The context is approximately the same
as in [1].

When run on different table AM it shows the following difference in output:

@@ -157,12 +157,12 @@
 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
  relname |    aa
 ---------+----------
+ a       | zzzzzz
  a       | zzzz
  a       | zzzzz
  a       | zzzzzz
  a       | zzzzzz
  a       | zzzzzz
- a       | zzzzzz
  b       | bbb
  b       | bbbb
  b       | bbbbb
@@ -231,12 +231,12 @@
 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
  relname |   aa
 ---------+--------
+ a       | zzzzzz
  a       | zzzz
  a       | zzzzz
  a       | zzzzzz
  a       | zzzzzz
  a       | zzzzzz
- a       | zzzzzz
 (6 rows)

 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
@@ -276,9 +276,9 @@
 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
  relname |    aa
 ---------+----------
+ a       | zzzzzz
  a       | zzzz
  a       | zzzzz
- a       | zzzzzz
  a       | zzzzzz
  a       | zzzzzz
  a       | zzzzzz
@@ -350,12 +350,12 @@
 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
  relname |   aa
 ---------+--------
+ a       | zzzzzz
  a       | zzzz
  a       | zzzzz
  a       | zzzzzz
  a       | zzzzzz
  a       | zzzzzz
- a       | zzzzzz
 (6 rows)

This could be easily fixed by adding ORDER BY clause to the test. See
the patch proposed. I added order by not only to places prone to the
difference quoted but also to other places as it's cheap and doesn't
harm. If the committer don't agree with extra order by's I can leave
only those necessary for fixing quoted test output.

[1] https://github.com/postgres/postgres/commit/4c1a27e53a508f74883cda52a6c8612121d7fd6b

---

From: Pavel Borisov <[email-masked]>
Date: Fri, 27 Mar 2026 17:45:09 +0400

Hi, hackers!

> [1] https://github.com/postgres/postgres/commit/4c1a27e53a508f74883cda52a6c8612121d7fd6b

To existing unchanged patch 0001, I've added a couple of others:

0002: Fixes join order differences.
The patch fixes only particular fail that I'm seeing.

0003: Fixed updatable view order differences.
It's carpet replacement of the test with adding ORDER BY's.

Kind regards,
Pavel Borisov
Supabase

---

From: John Naylor <[email-masked]>
Date: Wed, 29 Apr 2026 14:49:16 +0700

> When run on different table AM it shows the following difference in output:

I think it'd be beneficial to make regression tests more reproducible
across different table AMs. It's worth asking how much the ongoing
maintenance cost would be, since I imagine the one you're testing is
not the only one that shows differences.

-- 
John Naylor
Amazon Web Services

---

From: Pavel Borisov <[email-masked]>
Date: Wed, 29 Apr 2026 14:24:51 +0400

Hi, Jonh!

> not the only one that shows differences.
I agree with you. However, it looks quite difficult for me to imagine
all possible tests differences that some (unspecified) custom AM's
could introduce to tests written in PG test suite (i.e. written
considering a single existing heap table AM). It might be beneficial
to use an iterative approach and fix what is cheap first.

At the same time, adapting PG tests to some known table access methods
looks like a too limited case to me.

So this thread only adds ORDER BY to tests that are inadvertently tied
to the heap rows order, and so are cheap enough to fix.
I tried to add ORDER BY's when they're beneficial in my opinion, not
too wide, and at the same time not limited just to test differences
for particular table AM.

Kind regards,
Pavel Borisov
Supabase