On 04.04.2025 17:39, Andres Freund wrote:
On 2025-04-03 18:26:48 +0300, Alexander Korotkov wrote:
On Thu, Apr 3, 2025 at 5:18 PM Alena Rybakina <a.rybak...@postgrespro.ru> wrote:
Okay, I agree with you.
Good. I've reflected this limitation in comments and the commit
message. Also, I've adjust regression tests by removing excessive
ones and adding more important cases. I'm going to push this if no
objections.
This just failed on both buildfarm and CI:
BF:
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=rhinoceros&dt=2025-04-04%2013%3A52%3A13
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=iguana&dt=2025-04-04%2014%3A08%3A50
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=rhinoceros&dt=2025-04-04%2013%3A52%3A13
...
CI:
https://cirrus-ci.com/task/5466958990147584
https://api.cirrus-ci.com/v1/artifact/task/5466958990147584/testrun/build/testrun/pg_upgrade/002_pg_upgrade/log/regress_log_002_pg_upgrade
--- C:/cirrus/src/test/regress/expected/subselect.out 2025-04-04
13:20:49.429815400 +0000
+++
C:/cirrus/build/testrun/pg_upgrade/002_pg_upgrade/data/results/subselect.out
2025-04-04 13:23:33.602847700 +0000
@@ -2769,15 +2769,16 @@
EXPLAIN (COSTS OFF)
SELECT c.oid,c.relname FROM pg_class c JOIN pg_am a USING (oid)
WHERE c.oid IN (VALUES (1), (2));
- QUERY PLAN
----------------------------------------------------------------
- Hash Join
- Hash Cond: (a.oid = c.oid)
- -> Seq Scan on pg_am a
- -> Hash
- -> Index Scan using pg_class_oid_index on pg_class c
- Index Cond: (oid = ANY ('{1,2}'::oid[]))
-(6 rows)
+ QUERY PLAN
+---------------------------------------------------------
+ Merge Join
+ Merge Cond: (c.oid = a.oid)
+ -> Index Scan using pg_class_oid_index on pg_class c
+ Index Cond: (oid = ANY ('{1,2}'::oid[]))
+ -> Sort
+ Sort Key: a.oid
+ -> Seq Scan on pg_am a
+(7 rows)
-- Constant expressions are simplified
EXPLAIN (COSTS OFF)
This failure was entirely predictable, as this patch has been failing on cfbot
for *months*. Seriously. The tests passed exactly once since 2024-11-26, see:
https://cirrus-ci.com/github/postgresql-cfbot/postgresql/cf%2F5283
Come-on.
Sorry, I didn't understand at first how to find a specific diff file to
analyze what was wrong with my test, to be honest.
I only started to understand how to do this recently, then later I
thought everything was fine after the letter several days before.
I fixed it - changed the tables and didn't use system tables.
Sorry again, I'll be more careful with this.
--
Regards,
Alena Rybakina
Postgres Professional
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 288d139cfdd..33a10e8f518 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -2755,28 +2755,16 @@ SELECT * from onek WHERE unique1 in (VALUES(1200::bigint), (1));
-- VtA shouldn't depend on the side of the join probing with the VALUES expression.
EXPLAIN (COSTS OFF)
-SELECT c.oid,c.relname FROM pg_class c JOIN pg_am a USING (oid)
-WHERE a.oid IN (VALUES (1), (2));
- QUERY PLAN
----------------------------------------------------------
- Nested Loop
- -> Seq Scan on pg_am a
- Filter: (oid = ANY ('{1,2}'::oid[]))
- -> Index Scan using pg_class_oid_index on pg_class c
- Index Cond: (oid = a.oid)
-(5 rows)
-
-EXPLAIN (COSTS OFF)
-SELECT c.oid,c.relname FROM pg_class c JOIN pg_am a USING (oid)
-WHERE c.oid IN (VALUES (1), (2));
- QUERY PLAN
----------------------------------------------------------------
+SELECT c.unique1,c.ten FROM onek c JOIN tenk1 a USING (ten)
+WHERE a.ten IN (VALUES (1), (2));
+ QUERY PLAN
+--------------------------------------------------
Hash Join
- Hash Cond: (a.oid = c.oid)
- -> Seq Scan on pg_am a
+ Hash Cond: (a.ten = c.ten)
+ -> Seq Scan on tenk1 a
+ Filter: (ten = ANY ('{1,2}'::integer[]))
-> Hash
- -> Index Scan using pg_class_oid_index on pg_class c
- Index Cond: (oid = ANY ('{1,2}'::oid[]))
+ -> Seq Scan on onek c
(6 rows)
-- Constant expressions are simplified
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 101167de810..46b12df9f5c 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -1243,11 +1243,8 @@ SELECT * from onek WHERE unique1 in (VALUES(1200::bigint), (1));
-- VtA shouldn't depend on the side of the join probing with the VALUES expression.
EXPLAIN (COSTS OFF)
-SELECT c.oid,c.relname FROM pg_class c JOIN pg_am a USING (oid)
-WHERE a.oid IN (VALUES (1), (2));
-EXPLAIN (COSTS OFF)
-SELECT c.oid,c.relname FROM pg_class c JOIN pg_am a USING (oid)
-WHERE c.oid IN (VALUES (1), (2));
+SELECT c.unique1,c.ten FROM onek c JOIN tenk1 a USING (ten)
+WHERE a.ten IN (VALUES (1), (2));
-- Constant expressions are simplified
EXPLAIN (COSTS OFF)
