Get rid of pg_class usage in SJE regression tests

akorotkov · akorotkov · commit e1b7fde418f2 · 2024-02-19T14:11:50.000+02:00
Usage of pg_class led to instability, see the buildfarm failure. https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=prion&dt=2024-02-15%2021%3A58%3A04 Reported-by: Andrei Lepikhov Discussion: https://postgr.es/m/250c4cec-e459-4311-8fd6-da2558b3fb0a@postgrespro.ru
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
@@ -2873,7 +2873,6 @@ SELECT y,x,array_agg(distinct w) FROM btg WHERE y < 0 GROUP BY x,y;
 (6 rows)
 
 RESET enable_incremental_sort;
-DROP TABLE btg;
 -- Check we don't pick aggregate path key instead of grouping path key
 CREATE TABLE group_agg_pk AS SELECT
   i % 10 AS x,
@@ -2925,32 +2924,31 @@ DROP TABLE agg_sort_order CASCADE;
 SET enable_hashjoin = off;
 SET enable_nestloop = off;
 explain (COSTS OFF)
-SELECT c1.relname,c1.relpages
-FROM pg_class c1 JOIN pg_class c2 ON (c1.relname=c2.relname AND c1.relpages=c2.relpages)
-GROUP BY c1.reltuples,c1.relpages,c1.relname
-ORDER BY c1.relpages, c1.relname, c1.relpages*c1.relpages;
-                                         QUERY PLAN                                          
----------------------------------------------------------------------------------------------
+SELECT b1.x,b1.w FROM btg b1 JOIN btg b2 ON (b1.z=b2.z AND b1.w=b2.w)
+GROUP BY b1.x,b1.z,b1.w ORDER BY b1.z, b1.w, b1.x*b1.x;
+                            QUERY PLAN                             
+-------------------------------------------------------------------
  Incremental Sort
-   Sort Key: c1.relpages, c1.relname, ((c1.relpages * c1.relpages))
-   Presorted Key: c1.relpages, c1.relname
+   Sort Key: b1.z, b1.w, ((b1.x * b1.x))
+   Presorted Key: b1.z, b1.w
    ->  Group
-         Group Key: c1.relpages, c1.relname, c1.reltuples
+         Group Key: b1.z, b1.w, b1.x
          ->  Incremental Sort
-               Sort Key: c1.relpages, c1.relname, c1.reltuples
-               Presorted Key: c1.relpages, c1.relname
+               Sort Key: b1.z, b1.w, b1.x
+               Presorted Key: b1.z, b1.w
                ->  Merge Join
-                     Merge Cond: ((c1.relpages = c2.relpages) AND (c1.relname = c2.relname))
+                     Merge Cond: ((b1.z = b2.z) AND (b1.w = b2.w))
                      ->  Sort
-                           Sort Key: c1.relpages, c1.relname
-                           ->  Seq Scan on pg_class c1
+                           Sort Key: b1.z, b1.w
+                           ->  Seq Scan on btg b1
                      ->  Sort
-                           Sort Key: c2.relpages, c2.relname
-                           ->  Seq Scan on pg_class c2
+                           Sort Key: b2.z, b2.w
+                           ->  Seq Scan on btg b2
 (16 rows)
 
 RESET enable_hashjoin;
 RESET enable_nestloop;
+DROP TABLE btg;
 RESET enable_hashagg;
 RESET max_parallel_workers;
 RESET max_parallel_workers_per_gather;
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
@@ -1229,8 +1229,6 @@ EXPLAIN (VERBOSE, COSTS OFF)
 SELECT y,x,array_agg(distinct w) FROM btg WHERE y < 0 GROUP BY x,y;
 RESET enable_incremental_sort;
 
-DROP TABLE btg;
-
 -- Check we don't pick aggregate path key instead of grouping path key
 CREATE TABLE group_agg_pk AS SELECT
   i % 10 AS x,
@@ -1264,13 +1262,12 @@ DROP TABLE agg_sort_order CASCADE;
 SET enable_hashjoin = off;
 SET enable_nestloop = off;
 explain (COSTS OFF)
-SELECT c1.relname,c1.relpages
-FROM pg_class c1 JOIN pg_class c2 ON (c1.relname=c2.relname AND c1.relpages=c2.relpages)
-GROUP BY c1.reltuples,c1.relpages,c1.relname
-ORDER BY c1.relpages, c1.relname, c1.relpages*c1.relpages;
+SELECT b1.x,b1.w FROM btg b1 JOIN btg b2 ON (b1.z=b2.z AND b1.w=b2.w)
+GROUP BY b1.x,b1.z,b1.w ORDER BY b1.z, b1.w, b1.x*b1.x;
 RESET enable_hashjoin;
 RESET enable_nestloop;
 
+DROP TABLE btg;
 RESET enable_hashagg;
 RESET max_parallel_workers;
 RESET max_parallel_workers_per_gather;
