public inbox for [email protected]help / color / mirror / Atom feed
Inherit regression outputs rows in alternative ordering when run on other table AM than heap 4+ messages / 2 participants [nested] [flat]
* Inherit regression outputs rows in alternative ordering when run on other table AM than heap @ 2026-03-27 12:54 Pavel Borisov <[email protected]> 2026-03-27 13:45 ` Re: Inherit regression outputs rows in alternative ordering when run on other table AM than heap Pavel Borisov <[email protected]> 2026-04-29 07:49 ` Re: Inherit regression outputs rows in alternative ordering when run on other table AM than heap John Naylor <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: Pavel Borisov @ 2026-03-27 12:54 UTC (permalink / raw) To: Postgres hackers <[email protected]> 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 Attachments: [application/octet-stream] v1-0001-Stabilize-output-of-inherit-regression-test.patch (21.1K, 2-v1-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 v1] 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) ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Inherit regression outputs rows in alternative ordering when run on other table AM than heap 2026-03-27 12:54 Inherit regression outputs rows in alternative ordering when run on other table AM than heap Pavel Borisov <[email protected]> @ 2026-03-27 13:45 ` Pavel Borisov <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Pavel Borisov @ 2026-03-27 13:45 UTC (permalink / raw) To: Postgres hackers <[email protected]> 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) ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Inherit regression outputs rows in alternative ordering when run on other table AM than heap 2026-03-27 12:54 Inherit regression outputs rows in alternative ordering when run on other table AM than heap Pavel Borisov <[email protected]> @ 2026-04-29 07:49 ` John Naylor <[email protected]> 2026-04-29 10:24 ` Re: Inherit regression outputs rows in alternative ordering when run on other table AM than heap Pavel Borisov <[email protected]> 1 sibling, 1 reply; 4+ messages in thread From: John Naylor @ 2026-04-29 07:49 UTC (permalink / raw) To: Pavel Borisov <[email protected]>; +Cc: Postgres hackers <[email protected]> On Fri, Mar 27, 2026 at 7:54 PM Pavel Borisov <[email protected]> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Inherit regression outputs rows in alternative ordering when run on other table AM than heap 2026-03-27 12:54 Inherit regression outputs rows in alternative ordering when run on other table AM than heap Pavel Borisov <[email protected]> 2026-04-29 07:49 ` Re: Inherit regression outputs rows in alternative ordering when run on other table AM than heap John Naylor <[email protected]> @ 2026-04-29 10:24 ` Pavel Borisov <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Pavel Borisov @ 2026-04-29 10:24 UTC (permalink / raw) To: John Naylor <[email protected]>; +Cc: Postgres hackers <[email protected]> Hi, Jonh! On Wed, 29 Apr 2026 at 11:49, John Naylor <[email protected]> wrote: > > On Fri, Mar 27, 2026 at 7:54 PM Pavel Borisov <[email protected]> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-04-29 10:24 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-03-27 12:54 Inherit regression outputs rows in alternative ordering when run on other table AM than heap Pavel Borisov <[email protected]> 2026-03-27 13:45 ` Pavel Borisov <[email protected]> 2026-04-29 07:49 ` John Naylor <[email protected]> 2026-04-29 10:24 ` Pavel Borisov <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox