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

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Pavel Borisov <[email protected]>
To: Postgres hackers <[email protected]>
Subject: Re: Inherit regression outputs rows in alternative ordering when run on other table AM than heap
Date: Fri, 27 Mar 2026 17:45:09 +0400
Message-ID: <CALT9ZEHB5MWPa=RTUKQO1XmxVrNc8L2d3Dn6CMTtvBHkZS-=pw@mail.gmail.com> (raw)
In-Reply-To: <CALT9ZEF8JA+h9+6r_cJFjR-us7KG=hWbM7GBC6SVrW3FpCDv8g@mail.gmail.com>
References: <CALT9ZEF8JA+h9+6r_cJFjR-us7KG=hWbM7GBC6SVrW3FpCDv8g@mail.gmail.com>

Hi, hackers!

On Fri, 27 Mar 2026 at 16:54, Pavel Borisov <[email protected]> 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


Attachments:

  [application/octet-stream] v2-0001-Stabilize-output-of-inherit-regression-test.patch (21.1K, 2-v2-0001-Stabilize-output-of-inherit-regression-test.patch)
  download | inline diff:
From 6f4a2c6f3707787e5ba0d63fd18590d97ff1213a Mon Sep 17 00:00:00 2001
From: Pavel Borisov <[email protected]>
Date: Fri, 27 Mar 2026 16:37:59 +0400
Subject: [PATCH v2 1/3] Stabilize output of inherit regression test

The test assumed that a table's physical row order would be predictable
after an UPDATE.  But a non-heap table AM might produce some other
order. Even with heap AM, the assumption seems risky; Adding an ORDER BY
is a cheap insurance and doesn't break any goal of the test.
---
 src/test/regress/expected/inherit.out | 80 +++++++++++++--------------
 src/test/regress/sql/inherit.sql      | 80 +++++++++++++--------------
 2 files changed, 80 insertions(+), 80 deletions(-)

diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index a7fbeed9eb9..2cb70ca69dd 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -31,7 +31,7 @@ INSERT INTO d(aa) VALUES('ddddd');
 INSERT INTO d(aa) VALUES('dddddd');
 INSERT INTO d(aa) VALUES('ddddddd');
 INSERT INTO d(aa) VALUES('dddddddd');
-SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    
 ---------+----------
  a       | aaa
@@ -60,7 +60,7 @@ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
  d       | dddddddd
 (24 rows)
 
-SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    | bb 
 ---------+----------+----
  b       | bbb      | 
@@ -77,7 +77,7 @@ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
  d       | dddddddd | 
 (12 rows)
 
-SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
+SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    | cc 
 ---------+----------+----
  c       | ccc      | 
@@ -94,7 +94,7 @@ SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
  d       | dddddddd | 
 (12 rows)
 
-SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
+SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    | bb | cc | dd 
 ---------+----------+----+----+----
  d       | ddd      |    |    | 
@@ -105,7 +105,7 @@ SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
  d       | dddddddd |    |    | 
 (6 rows)
 
-SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    
 ---------+----------
  a       | aaa
@@ -116,7 +116,7 @@ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
  a       | aaaaaaaa
 (6 rows)
 
-SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
+SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    | bb 
 ---------+----------+----
  b       | bbb      | 
@@ -127,7 +127,7 @@ SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
  b       | bbbbbbbb | 
 (6 rows)
 
-SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
+SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    | cc 
 ---------+----------+----
  c       | ccc      | 
@@ -138,7 +138,7 @@ SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
  c       | cccccccc | 
 (6 rows)
 
-SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
+SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    | bb | cc | dd 
 ---------+----------+----+----+----
  d       | ddd      |    |    | 
@@ -154,7 +154,7 @@ UPDATE ONLY a SET aa='zzzzz' WHERE aa='aaaaa';
 UPDATE b SET aa='zzz' WHERE aa='aaa';
 UPDATE ONLY b SET aa='zzz' WHERE aa='aaa';
 UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%';
-SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    
 ---------+----------
  a       | zzzz
@@ -183,7 +183,7 @@ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
  d       | dddddddd
 (24 rows)
 
-SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    | bb 
 ---------+----------+----
  b       | bbb      | 
@@ -200,7 +200,7 @@ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
  d       | dddddddd | 
 (12 rows)
 
-SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
+SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    | cc 
 ---------+----------+----
  c       | ccc      | 
@@ -217,7 +217,7 @@ SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
  d       | dddddddd | 
 (12 rows)
 
-SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
+SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    | bb | cc | dd 
 ---------+----------+----+----+----
  d       | ddd      |    |    | 
@@ -228,7 +228,7 @@ SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
  d       | dddddddd |    |    | 
 (6 rows)
 
-SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
  relname |   aa   
 ---------+--------
  a       | zzzz
@@ -239,7 +239,7 @@ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
  a       | zzzzzz
 (6 rows)
 
-SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
+SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    | bb 
 ---------+----------+----
  b       | bbb      | 
@@ -250,7 +250,7 @@ SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
  b       | bbbbbbbb | 
 (6 rows)
 
-SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
+SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    | cc 
 ---------+----------+----
  c       | ccc      | 
@@ -261,7 +261,7 @@ SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
  c       | cccccccc | 
 (6 rows)
 
-SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
+SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    | bb | cc | dd 
 ---------+----------+----+----+----
  d       | ddd      |    |    | 
@@ -273,7 +273,7 @@ SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
 (6 rows)
 
 UPDATE b SET aa='new';
-SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    
 ---------+----------
  a       | zzzz
@@ -302,7 +302,7 @@ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
  d       | new
 (24 rows)
 
-SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa  | bb 
 ---------+-----+----
  b       | new | 
@@ -319,7 +319,7 @@ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
  d       | new | 
 (12 rows)
 
-SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
+SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    | cc 
 ---------+----------+----
  c       | ccc      | 
@@ -336,7 +336,7 @@ SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
  d       | new      | 
 (12 rows)
 
-SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
+SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa  | bb | cc | dd 
 ---------+-----+----+----+----
  d       | new |    |    | 
@@ -347,7 +347,7 @@ SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
  d       | new |    |    | 
 (6 rows)
 
-SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
  relname |   aa   
 ---------+--------
  a       | zzzz
@@ -358,7 +358,7 @@ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
  a       | zzzzzz
 (6 rows)
 
-SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
+SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa  | bb 
 ---------+-----+----
  b       | new | 
@@ -369,7 +369,7 @@ SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
  b       | new | 
 (6 rows)
 
-SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
+SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
  relname |    aa    | cc 
 ---------+----------+----
  c       | ccc      | 
@@ -380,7 +380,7 @@ SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
  c       | cccccccc | 
 (6 rows)
 
-SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
+SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa  | bb | cc | dd 
 ---------+-----+----+----+----
  d       | new |    |    | 
@@ -393,7 +393,7 @@ SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
 
 UPDATE a SET aa='new';
 DELETE FROM ONLY c WHERE aa='new';
-SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa  
 ---------+-----
  a       | new
@@ -416,7 +416,7 @@ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
  d       | new
 (18 rows)
 
-SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa  | bb 
 ---------+-----+----
  b       | new | 
@@ -433,7 +433,7 @@ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
  d       | new | 
 (12 rows)
 
-SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
+SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa  | cc 
 ---------+-----+----
  d       | new | 
@@ -444,7 +444,7 @@ SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
  d       | new | 
 (6 rows)
 
-SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
+SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa  | bb | cc | dd 
 ---------+-----+----+----+----
  d       | new |    |    | 
@@ -455,7 +455,7 @@ SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
  d       | new |    |    | 
 (6 rows)
 
-SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa  
 ---------+-----
  a       | new
@@ -466,7 +466,7 @@ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
  a       | new
 (6 rows)
 
-SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
+SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa  | bb 
 ---------+-----+----
  b       | new | 
@@ -477,12 +477,12 @@ SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
  b       | new | 
 (6 rows)
 
-SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
+SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa | cc 
 ---------+----+----
 (0 rows)
 
-SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
+SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa  | bb | cc | dd 
 ---------+-----+----+----+----
  d       | new |    |    | 
@@ -494,42 +494,42 @@ SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
 (6 rows)
 
 DELETE FROM a;
-SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa 
 ---------+----
 (0 rows)
 
-SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa | bb 
 ---------+----+----
 (0 rows)
 
-SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
+SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa | cc 
 ---------+----+----
 (0 rows)
 
-SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
+SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa | bb | cc | dd 
 ---------+----+----+----+----
 (0 rows)
 
-SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa 
 ---------+----
 (0 rows)
 
-SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
+SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa | bb 
 ---------+----+----
 (0 rows)
 
-SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
+SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa | cc 
 ---------+----+----
 (0 rows)
 
-SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
+SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
  relname | aa | bb | cc | dd 
 ---------+----+----+----+----
 (0 rows)
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 215d58e80d3..e0e58b2bf64 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -34,14 +34,14 @@ INSERT INTO d(aa) VALUES('dddddd');
 INSERT INTO d(aa) VALUES('ddddddd');
 INSERT INTO d(aa) VALUES('dddddddd');
 
-SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
-SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
-SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
-SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
-SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
-SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
-SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
-SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
+SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
 
 UPDATE a SET aa='zzzz' WHERE aa='aaaa';
 UPDATE ONLY a SET aa='zzzzz' WHERE aa='aaaaa';
@@ -49,49 +49,49 @@ UPDATE b SET aa='zzz' WHERE aa='aaa';
 UPDATE ONLY b SET aa='zzz' WHERE aa='aaa';
 UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%';
 
-SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
-SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
-SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
-SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
-SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
-SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
-SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
-SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
+SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
 
 UPDATE b SET aa='new';
 
-SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
-SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
-SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
-SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
-SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
-SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
-SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
-SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
+SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
 
 UPDATE a SET aa='new';
 
 DELETE FROM ONLY c WHERE aa='new';
 
-SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
-SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
-SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
-SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
-SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
-SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
-SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
-SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
+SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
 
 DELETE FROM a;
 
-SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
-SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
-SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
-SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
-SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
-SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
-SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
-SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
+SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER by 1,2;
+SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER by 1,2;
 
 -- Confirm PRIMARY KEY adds NOT NULL constraint to child table
 CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a);
-- 
2.39.2 (Apple Git-143)



  [application/octet-stream] v2-0002-Stabilize-output-of-join-regression-test.patch (3.3K, 3-v2-0002-Stabilize-output-of-join-regression-test.patch)
  download | inline diff:
From ba3b67c4a15397d6aa6af62f05b0eab9001975f2 Mon Sep 17 00:00:00 2001
From: Pavel Borisov <[email protected]>
Date: Fri, 27 Mar 2026 17:14:57 +0400
Subject: [PATCH v2 2/3]  Stabilize output of join regression test

    The test added by 739f1d6218f5 assumes particular join
    rows order. When run on a non-heap
    table AM test might produce some other
    order. Adding order by clause fixes the difference.
---
 src/test/regress/expected/join.out | 6 +++---
 src/test/regress/sql/join.sql      | 2 +-
 2 files changed, 4 insertions(+), 4 deletions(-)

diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 6917faec141..73b4edf14c6 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4469,19 +4469,19 @@ select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
 (8 rows)
 
 select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
-  lateral (select * from int8_tbl t3 where t2.q1 = t2.q2) ss;
+  lateral (select * from int8_tbl t3 where t2.q1 = t2.q2) ss order by 1,2,3,4,5,6;
         q1        |        q2        |        q1        |        q2        |        q1        |        q2         
 ------------------+------------------+------------------+------------------+------------------+-------------------
               123 | 4567890123456789 | 4567890123456789 | 4567890123456789 |              123 |               456
               123 | 4567890123456789 | 4567890123456789 | 4567890123456789 |              123 |  4567890123456789
+              123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789
               123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 |               123
               123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 |  4567890123456789
-              123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789
  4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 |              123 |               456
  4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 |              123 |  4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789
  4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 |               123
  4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 |  4567890123456789
- 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789
 (10 rows)
 
 --
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 55080bec9af..5814a2b5593 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1509,7 +1509,7 @@ select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
   lateral (select * from int8_tbl t3 where t2.q1 = t2.q2) ss;
 
 select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
-  lateral (select * from int8_tbl t3 where t2.q1 = t2.q2) ss;
+  lateral (select * from int8_tbl t3 where t2.q1 = t2.q2) ss order by 1,2,3,4,5,6;
 
 --
 -- check handling of join aliases when flattening multiple levels of subquery
-- 
2.39.2 (Apple Git-143)



  [application/octet-stream] v2-0003-Stabilize-output-of-updatable_views-regression-te.patch (18.4K, 4-v2-0003-Stabilize-output-of-updatable_views-regression-te.patch)
  download | inline diff:
From 7b95b9363f3b7f9c3b5d444843ac864504fec10e Mon Sep 17 00:00:00 2001
From: Pavel Borisov <[email protected]>
Date: Fri, 27 Mar 2026 17:37:08 +0400
Subject: [PATCH v2 3/3] Stabilize output of updatable_views regression test

The test assumes particular rows order. When run on a non-heap
table AM test might produce some other order. Adding order by
clause fixes the difference.
---
 src/test/regress/expected/updatable_views.out | 66 +++++++++----------
 src/test/regress/sql/updatable_views.sql      | 50 +++++++-------
 2 files changed, 58 insertions(+), 58 deletions(-)

diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 1950e6f281f..6cbebd93c2b 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -193,7 +193,7 @@ UPDATE rw_view14 SET ctid=null WHERE a=3; -- should fail
 ERROR:  cannot update column "ctid" of view "rw_view14"
 DETAIL:  View columns that refer to system columns are not updatable.
 UPDATE rw_view14 SET b='ROW 3' WHERE a=3; -- should be OK
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
  a  |   b    
 ----+--------
  -2 | Row -2
@@ -282,7 +282,7 @@ UPDATE rw_view15 SET upper=DEFAULT WHERE a=3; -- should fail
 ERROR:  cannot update column "upper" of view "rw_view15"
 DETAIL:  View columns that are not columns of their base relation are not updatable.
 UPDATE rw_view15 SET a=4 WHERE a=3; -- should be OK
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
  a  |      b      
 ----+-------------
  -2 | Row -2
@@ -301,15 +301,15 @@ INSERT INTO rw_view16 (a, b) VALUES (3, 'Row 3'); -- should be OK
 UPDATE rw_view16 SET a=3, aa=-3 WHERE a=3; -- should fail
 ERROR:  multiple assignments to same column "a"
 UPDATE rw_view16 SET aa=-3 WHERE a=3; -- should be OK
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
  a  |   b    
 ----+--------
+ -3 | Row 3
  -2 | Row -2
  -1 | Row -1
   0 | Row 0
   1 | Row 1
   2 | Row 2
- -3 | Row 3
 (6 rows)
 
 DELETE FROM rw_view16 WHERE a=-3; -- should be OK
@@ -406,7 +406,7 @@ INSERT INTO rw_view1 VALUES (3, 'Row 3');
 INSERT INTO rw_view1 (a) VALUES (4);
 UPDATE rw_view1 SET a=5 WHERE a=4;
 DELETE FROM rw_view1 WHERE b='Row 2';
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
  a  |      b      
 ----+-------------
  -2 | Row -2
@@ -974,7 +974,7 @@ UPDATE rw_view1 v SET bb='Updated row 2' WHERE rw_view1_aa(v)=2
            2 | Updated row 2
 (1 row)
 
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
  a  |       b       
 ----+---------------
  -2 | Row -2
@@ -1014,7 +1014,7 @@ GRANT UPDATE (bb,cc) ON rw_view1 TO regress_view_user2;
 RESET SESSION AUTHORIZATION;
 SET SESSION AUTHORIZATION regress_view_user2;
 CREATE VIEW rw_view2 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl;
-SELECT * FROM base_tbl; -- ok
+SELECT * FROM base_tbl ORDER BY 1; -- ok
  a |   b   | c 
 ---+-------+---
  1 | Row 1 | 1
@@ -1069,7 +1069,7 @@ DELETE FROM base_tbl WHERE a=1; -- ok
 DELETE FROM rw_view1 WHERE aa=2; -- not allowed
 ERROR:  permission denied for view rw_view1
 DELETE FROM rw_view2 WHERE aa=2; -- ok
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
  a |   b   | c 
 ---+-------+---
  3 | Row 3 | 3
@@ -1092,7 +1092,7 @@ ERROR:  permission denied for table base_tbl
 DELETE FROM rw_view1 WHERE aa=3; -- ok
 DELETE FROM rw_view2 WHERE aa=4; -- not allowed
 ERROR:  permission denied for table base_tbl
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
  a |   b   | c 
 ---+-------+---
  4 | Row 4 | 4
@@ -1108,7 +1108,7 @@ drop cascades to view rw_view2
 CREATE TABLE base_tbl(a int, b text, c float);
 INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0);
 SET SESSION AUTHORIZATION regress_view_user1;
-CREATE VIEW rw_view1 AS SELECT * FROM base_tbl;
+CREATE VIEW rw_view1 AS SELECT * FROM base_tbl ORDER BY 1;
 SELECT * FROM rw_view1;  -- not allowed
 ERROR:  permission denied for table base_tbl
 SELECT * FROM rw_view1 FOR UPDATE;  -- not allowed
@@ -1238,7 +1238,7 @@ INSERT INTO rw_view1 VALUES ('Row 2', 2.0, 2);
 GRANT SELECT ON rw_view1 TO regress_view_user2;
 GRANT UPDATE (bb,cc) ON rw_view1 TO regress_view_user2;
 SET SESSION AUTHORIZATION regress_view_user2;
-SELECT * FROM base_tbl; -- not allowed
+SELECT * FROM base_tbl ORDER BY 1; -- not allowed
 ERROR:  permission denied for table base_tbl
 SELECT * FROM rw_view1; -- not allowed
 ERROR:  permission denied for table base_tbl
@@ -1258,7 +1258,7 @@ SET SESSION AUTHORIZATION regress_view_user1;
 GRANT SELECT ON base_tbl TO regress_view_user2;
 GRANT UPDATE (a,c) ON base_tbl TO regress_view_user2;
 SET SESSION AUTHORIZATION regress_view_user2;
-SELECT * FROM base_tbl; -- ok
+SELECT * FROM base_tbl ORDER BY 1; -- ok
  a |   b   | c 
 ---+-------+---
  1 | Row 1 | 1
@@ -1302,7 +1302,7 @@ GRANT INSERT, DELETE ON base_tbl TO regress_view_user2;
 SET SESSION AUTHORIZATION regress_view_user2;
 INSERT INTO rw_view1 VALUES ('Row 4', 4.0, 4); -- ok
 DELETE FROM rw_view1 WHERE aa=2; -- ok
-SELECT * FROM base_tbl; -- ok
+SELECT * FROM base_tbl ORDER BY 1; -- ok
  a |   b   | c 
 ---+-------+---
  3 | Row 3 | 3
@@ -1475,7 +1475,7 @@ CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl;
 ALTER VIEW rw_view1 ALTER COLUMN bb SET DEFAULT 'View default';
 INSERT INTO rw_view1 VALUES (4, 'Row 4');
 INSERT INTO rw_view1 (aa) VALUES (5);
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
  a |      b       | c 
 ---+--------------+---
  1 | Row 1        | 1
@@ -1573,7 +1573,7 @@ NOTICE:  drop cascades to view rw_view1
 -- multiple array-column updates
 CREATE TABLE base_tbl (a int, arr int[]);
 INSERT INTO base_tbl VALUES (1,ARRAY[2]), (3,ARRAY[4]);
-CREATE VIEW rw_view1 AS SELECT * FROM base_tbl;
+CREATE VIEW rw_view1 AS SELECT * FROM base_tbl ORDER BY 1;
 UPDATE rw_view1 SET arr[1] = 42, arr[2] = 77 WHERE a = 3;
 SELECT * FROM rw_view1;
  a |   arr   
@@ -1728,7 +1728,7 @@ drop cascades to view rw_view2
 drop cascades to view rw_view3
 -- view on table with GENERATED columns
 CREATE TABLE base_tbl (id int, idplus1 int GENERATED ALWAYS AS (id + 1) STORED);
-CREATE VIEW rw_view1 AS SELECT * FROM base_tbl;
+CREATE VIEW rw_view1 AS SELECT * FROM base_tbl ORDER BY 1;
 INSERT INTO base_tbl (id) VALUES (1);
 INSERT INTO rw_view1 (id) VALUES (2);
 INSERT INTO base_tbl (id, idplus1) VALUES (3, DEFAULT);
@@ -1739,7 +1739,7 @@ DETAIL:  Column "idplus1" is a generated column.
 INSERT INTO rw_view1 (id, idplus1) VALUES (6, 7);  -- error
 ERROR:  cannot insert a non-DEFAULT value into column "idplus1"
 DETAIL:  Column "idplus1" is a generated column.
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
  id | idplus1 
 ----+---------
   1 |       2
@@ -1750,7 +1750,7 @@ SELECT * FROM base_tbl;
 
 UPDATE base_tbl SET id = 2000 WHERE id = 2;
 UPDATE rw_view1 SET id = 3000 WHERE id = 3;
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
   id  | idplus1 
 ------+---------
     1 |       2
@@ -1955,12 +1955,12 @@ INSERT INTO rw_view1(a) VALUES (9); -- ok
 INSERT INTO rw_view1(a) VALUES (10); -- should fail
 ERROR:  new row violates check option for view "rw_view1"
 DETAIL:  Failing row contains (10, 10).
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
  a | b  
 ---+----
  1 |  2
- 2 |  3
  1 | -1
+ 2 |  3
  3 |  5
  9 | 10
 (5 rows)
@@ -1998,7 +1998,7 @@ INSERT INTO rw_view2 VALUES (5); -- ok
 INSERT INTO rw_view2 VALUES (15); -- should fail
 ERROR:  new row violates check option for view "rw_view2"
 DETAIL:  Failing row contains (15).
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
  a 
 ---
  5
@@ -2035,11 +2035,11 @@ INSERT INTO rw_view2 VALUES (-10); -- ok, but not in view
 INSERT INTO rw_view2 VALUES (20); -- should fail
 ERROR:  new row violates check option for view "rw_view2"
 DETAIL:  Failing row contains (20).
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
   a  
 -----
-   5
  -10
+   5
 (2 rows)
 
 ALTER VIEW rw_view1 SET (check_option=here); -- invalid
@@ -2072,11 +2072,11 @@ SELECT * FROM information_schema.views WHERE table_name = 'rw_view2';
 (1 row)
 
 INSERT INTO rw_view2 VALUES (30); -- ok, but not in view
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
   a  
 -----
-   5
  -10
+   5
   30
 (3 rows)
 
@@ -2233,7 +2233,7 @@ INSERT INTO rw_view2 VALUES (50); -- ok, but not in view
 UPDATE rw_view2 SET a = a - 10; -- should fail
 ERROR:  new row violates check option for view "rw_view2"
 DETAIL:  Failing row contains (-5).
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
  a  | b  
 ----+----
   5 | 10
@@ -2244,7 +2244,7 @@ SELECT * FROM base_tbl;
 ALTER VIEW rw_view2 SET (check_option=cascaded);
 INSERT INTO rw_view2 VALUES (100); -- ok, but not in view (doesn't fail rw_view1's check)
 UPDATE rw_view2 SET a = 200 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view1's check)
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
   a  | b  
 -----+----
   50 | 10
@@ -2264,16 +2264,16 @@ INSERT INTO rw_view2 VALUES (20); -- ok, but not in view (doesn't fail rw_view1'
 UPDATE rw_view2 SET a = 30 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view1's check)
 INSERT INTO rw_view2 VALUES (5); -- ok
 UPDATE rw_view2 SET a = -5 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view2's check)
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
   a  | b  
 -----+----
-  50 | 10
- 100 | 10
- 200 | 10
  -10 | 10
+  -5 | 10
   20 | 10
   30 | 10
-  -5 | 10
+  50 | 10
+ 100 | 10
+ 200 | 10
 (7 rows)
 
 DROP TABLE base_tbl CASCADE;
@@ -2533,7 +2533,7 @@ EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (2, 'New row 2');
 (15 rows)
 
 INSERT INTO rw_view1 VALUES (2, 'New row 2');
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
  id |   data    | deleted 
 ----+-----------+---------
   1 | Row 1     | t
diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql
index eaee0b7e1d7..2c4e427e1ae 100644
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -67,7 +67,7 @@ INSERT INTO rw_view14 VALUES (null, 3, 'Row 3'); -- should fail
 INSERT INTO rw_view14 (a, b) VALUES (3, 'Row 3'); -- should be OK
 UPDATE rw_view14 SET ctid=null WHERE a=3; -- should fail
 UPDATE rw_view14 SET b='ROW 3' WHERE a=3; -- should be OK
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 DELETE FROM rw_view14 WHERE a=3; -- should be OK
 -- Partially updatable view
 INSERT INTO rw_view15 VALUES (3, 'ROW 3'); -- should fail
@@ -86,14 +86,14 @@ INSERT INTO rw_view15 (a) VALUES (4); -- should fail
 UPDATE rw_view15 SET upper='ROW 3' WHERE a=3; -- should fail
 UPDATE rw_view15 SET upper=DEFAULT WHERE a=3; -- should fail
 UPDATE rw_view15 SET a=4 WHERE a=3; -- should be OK
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 DELETE FROM rw_view15 WHERE a=4; -- should be OK
 -- Partially updatable view
 INSERT INTO rw_view16 VALUES (3, 'Row 3', 3); -- should fail
 INSERT INTO rw_view16 (a, b) VALUES (3, 'Row 3'); -- should be OK
 UPDATE rw_view16 SET a=3, aa=-3 WHERE a=3; -- should fail
 UPDATE rw_view16 SET aa=-3 WHERE a=3; -- should be OK
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 DELETE FROM rw_view16 WHERE a=-3; -- should be OK
 -- Read-only views
 INSERT INTO ro_view17 VALUES (3, 'ROW 3');
@@ -143,7 +143,7 @@ INSERT INTO rw_view1 VALUES (3, 'Row 3');
 INSERT INTO rw_view1 (a) VALUES (4);
 UPDATE rw_view1 SET a=5 WHERE a=4;
 DELETE FROM rw_view1 WHERE b='Row 2';
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 
 EXPLAIN (costs off) UPDATE rw_view1 SET a=6 WHERE a=5;
 EXPLAIN (costs off) DELETE FROM rw_view1 WHERE a=5;
@@ -415,7 +415,7 @@ CREATE FUNCTION rw_view1_aa(x rw_view1)
 
 UPDATE rw_view1 v SET bb='Updated row 2' WHERE rw_view1_aa(v)=2
   RETURNING rw_view1_aa(v), v.bb;
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 
 EXPLAIN (costs off)
 UPDATE rw_view1 v SET bb='Updated row 2' WHERE rw_view1_aa(v)=2
@@ -443,7 +443,7 @@ RESET SESSION AUTHORIZATION;
 
 SET SESSION AUTHORIZATION regress_view_user2;
 CREATE VIEW rw_view2 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl;
-SELECT * FROM base_tbl; -- ok
+SELECT * FROM base_tbl ORDER BY 1; -- ok
 SELECT * FROM rw_view1; -- ok
 SELECT * FROM rw_view2; -- ok
 
@@ -474,7 +474,7 @@ INSERT INTO rw_view2 VALUES ('Row 4', 4.0, 4); -- ok
 DELETE FROM base_tbl WHERE a=1; -- ok
 DELETE FROM rw_view1 WHERE aa=2; -- not allowed
 DELETE FROM rw_view2 WHERE aa=2; -- ok
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 RESET SESSION AUTHORIZATION;
 
 SET SESSION AUTHORIZATION regress_view_user1;
@@ -489,7 +489,7 @@ INSERT INTO rw_view2 VALUES ('Row 6', 6.0, 6); -- not allowed
 DELETE FROM base_tbl WHERE a=3; -- not allowed
 DELETE FROM rw_view1 WHERE aa=3; -- ok
 DELETE FROM rw_view2 WHERE aa=4; -- not allowed
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 RESET SESSION AUTHORIZATION;
 
 DROP TABLE base_tbl CASCADE;
@@ -500,7 +500,7 @@ CREATE TABLE base_tbl(a int, b text, c float);
 INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0);
 
 SET SESSION AUTHORIZATION regress_view_user1;
-CREATE VIEW rw_view1 AS SELECT * FROM base_tbl;
+CREATE VIEW rw_view1 AS SELECT * FROM base_tbl ORDER BY 1;
 SELECT * FROM rw_view1;  -- not allowed
 SELECT * FROM rw_view1 FOR UPDATE;  -- not allowed
 UPDATE rw_view1 SET b = 'foo' WHERE a = 1;  -- not allowed
@@ -582,7 +582,7 @@ GRANT SELECT ON rw_view1 TO regress_view_user2;
 GRANT UPDATE (bb,cc) ON rw_view1 TO regress_view_user2;
 
 SET SESSION AUTHORIZATION regress_view_user2;
-SELECT * FROM base_tbl; -- not allowed
+SELECT * FROM base_tbl ORDER BY 1; -- not allowed
 SELECT * FROM rw_view1; -- not allowed
 INSERT INTO base_tbl VALUES (3, 'Row 3', 3.0); -- not allowed
 INSERT INTO rw_view1 VALUES ('Row 3', 3.0, 3); -- not allowed
@@ -596,7 +596,7 @@ GRANT SELECT ON base_tbl TO regress_view_user2;
 GRANT UPDATE (a,c) ON base_tbl TO regress_view_user2;
 
 SET SESSION AUTHORIZATION regress_view_user2;
-SELECT * FROM base_tbl; -- ok
+SELECT * FROM base_tbl ORDER BY 1; -- ok
 SELECT * FROM rw_view1; -- ok
 UPDATE base_tbl SET a=a, c=c; -- ok
 UPDATE base_tbl SET b=b; -- not allowed
@@ -627,7 +627,7 @@ GRANT INSERT, DELETE ON base_tbl TO regress_view_user2;
 SET SESSION AUTHORIZATION regress_view_user2;
 INSERT INTO rw_view1 VALUES ('Row 4', 4.0, 4); -- ok
 DELETE FROM rw_view1 WHERE aa=2; -- ok
-SELECT * FROM base_tbl; -- ok
+SELECT * FROM base_tbl ORDER BY 1; -- ok
 
 RESET SESSION AUTHORIZATION;
 
@@ -765,7 +765,7 @@ ALTER VIEW rw_view1 ALTER COLUMN bb SET DEFAULT 'View default';
 INSERT INTO rw_view1 VALUES (4, 'Row 4');
 INSERT INTO rw_view1 (aa) VALUES (5);
 
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 
 DROP TABLE base_tbl CASCADE;
 
@@ -824,7 +824,7 @@ DROP TABLE base_tbl CASCADE;
 CREATE TABLE base_tbl (a int, arr int[]);
 INSERT INTO base_tbl VALUES (1,ARRAY[2]), (3,ARRAY[4]);
 
-CREATE VIEW rw_view1 AS SELECT * FROM base_tbl;
+CREATE VIEW rw_view1 AS SELECT * FROM base_tbl ORDER BY 1;
 
 UPDATE rw_view1 SET arr[1] = 42, arr[2] = 77 WHERE a = 3;
 
@@ -897,7 +897,7 @@ DROP TABLE base_tbl CASCADE;
 -- view on table with GENERATED columns
 
 CREATE TABLE base_tbl (id int, idplus1 int GENERATED ALWAYS AS (id + 1) STORED);
-CREATE VIEW rw_view1 AS SELECT * FROM base_tbl;
+CREATE VIEW rw_view1 AS SELECT * FROM base_tbl ORDER BY 1;
 
 INSERT INTO base_tbl (id) VALUES (1);
 INSERT INTO rw_view1 (id) VALUES (2);
@@ -906,12 +906,12 @@ INSERT INTO rw_view1 (id, idplus1) VALUES (4, DEFAULT);
 INSERT INTO base_tbl (id, idplus1) VALUES (5, 6);  -- error
 INSERT INTO rw_view1 (id, idplus1) VALUES (6, 7);  -- error
 
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 
 UPDATE base_tbl SET id = 2000 WHERE id = 2;
 UPDATE rw_view1 SET id = 3000 WHERE id = 3;
 
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 
 DROP TABLE base_tbl CASCADE;
 
@@ -977,7 +977,7 @@ UPDATE rw_view1 SET b = 5 WHERE a = 3; -- ok
 UPDATE rw_view1 SET b = -5 WHERE a = 3; -- should fail
 INSERT INTO rw_view1(a) VALUES (9); -- ok
 INSERT INTO rw_view1(a) VALUES (10); -- should fail
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 
 DROP TABLE base_tbl CASCADE;
 
@@ -994,7 +994,7 @@ SELECT * FROM information_schema.views WHERE table_name = 'rw_view2';
 INSERT INTO rw_view2 VALUES (-5); -- should fail
 INSERT INTO rw_view2 VALUES (5); -- ok
 INSERT INTO rw_view2 VALUES (15); -- should fail
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 
 UPDATE rw_view2 SET a = a - 10; -- should fail
 UPDATE rw_view2 SET a = a + 10; -- should fail
@@ -1006,7 +1006,7 @@ SELECT * FROM information_schema.views WHERE table_name = 'rw_view2';
 
 INSERT INTO rw_view2 VALUES (-10); -- ok, but not in view
 INSERT INTO rw_view2 VALUES (20); -- should fail
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 
 ALTER VIEW rw_view1 SET (check_option=here); -- invalid
 ALTER VIEW rw_view1 SET (check_option=local);
@@ -1018,7 +1018,7 @@ ALTER VIEW rw_view2 RESET (check_option);
 \d+ rw_view2
 SELECT * FROM information_schema.views WHERE table_name = 'rw_view2';
 INSERT INTO rw_view2 VALUES (30); -- ok, but not in view
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 
 DROP TABLE base_tbl CASCADE;
 
@@ -1142,14 +1142,14 @@ INSERT INTO rw_view2 VALUES (-5); -- should fail
 INSERT INTO rw_view2 VALUES (5); -- ok
 INSERT INTO rw_view2 VALUES (50); -- ok, but not in view
 UPDATE rw_view2 SET a = a - 10; -- should fail
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 
 -- Check option won't cascade down to base view with INSTEAD OF triggers
 
 ALTER VIEW rw_view2 SET (check_option=cascaded);
 INSERT INTO rw_view2 VALUES (100); -- ok, but not in view (doesn't fail rw_view1's check)
 UPDATE rw_view2 SET a = 200 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view1's check)
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 
 -- Neither local nor cascaded check options work with INSTEAD rules
 
@@ -1164,7 +1164,7 @@ INSERT INTO rw_view2 VALUES (20); -- ok, but not in view (doesn't fail rw_view1'
 UPDATE rw_view2 SET a = 30 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view1's check)
 INSERT INTO rw_view2 VALUES (5); -- ok
 UPDATE rw_view2 SET a = -5 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view2's check)
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 
 DROP TABLE base_tbl CASCADE;
 DROP FUNCTION rw_view1_trig_fn();
@@ -1287,7 +1287,7 @@ DELETE FROM rw_view1 WHERE id = 1 AND snoop(data);
 EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (2, 'New row 2');
 INSERT INTO rw_view1 VALUES (2, 'New row 2');
 
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY 1;
 
 DROP TABLE base_tbl CASCADE;
 
-- 
2.39.2 (Apple Git-143)

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Inherit regression outputs rows in alternative ordering when run on other table AM than heap
  In-Reply-To: <CALT9ZEHB5MWPa=RTUKQO1XmxVrNc8L2d3Dn6CMTtvBHkZS-=pw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox