Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Stabilize output of new isolation test insert-conflict-do-update-4.

  1. Inherit regression outputs rows in alternative ordering when run on other table AM than heap

    Pavel Borisov <pashkin.elfe@gmail.com> — 2026-03-27T12:54:25Z

    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

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

    Pavel Borisov <pashkin.elfe@gmail.com> — 2026-03-27T13:45:09Z

    Hi, hackers!

On Fri, 27 Mar 2026 at 16:54, Pavel Borisov <pashkin.elfe@gmail.com> wrote:
>
> 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].
> 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

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

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

    John Naylor <johncnaylorls@gmail.com> — 2026-04-29T07:49:16Z

    On Fri, Mar 27, 2026 at 7:54 PM Pavel Borisov <pashkin.elfe@gmail.com> wrote:
> 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:

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

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

    Pavel Borisov <pashkin.elfe@gmail.com> — 2026-04-29T10:24:51Z

    Hi, Jonh!

On Wed, 29 Apr 2026 at 11:49, John Naylor <johncnaylorls@gmail.com> wrote:
>
> On Fri, Mar 27, 2026 at 7:54 PM Pavel Borisov <pashkin.elfe@gmail.com> wrote:
> > 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:
>
> 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.
>
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