make installcheck: 5 of 14 tests failed.

[joelonsql]

Hi,

Thanks for creating pg_hint_plan.

Some tests are failing. I've attached the regression.diffs below.

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 20.04.2 LTS
Release:    20.04
Codename:   focal

$ psql --version
psql (PostgreSQL) 13.2 (Ubuntu 13.2-1.pgdg20.04+1)

$ make installcheck
/usr/lib/postgresql/13/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/usr/lib/postgresql/13/bin'    --encoding=UTF8 --dbname=contrib_regression init base_plan pg_hint_plan ut-init ut-A ut-S ut-J ut-L ut-G ut-R ut-fdw ut-W ut-T ut-fini
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test init                         ... FAILED      896 ms
test base_plan                    ... ok           12 ms
test pg_hint_plan                 ... FAILED     7937 ms
test ut-init                      ... FAILED      551 ms
test ut-A                         ... FAILED      328 ms
test ut-S                         ... FAILED      443 ms
test ut-J                         ... ok          288 ms
test ut-L                         ... ok          250 ms
test ut-G                         ... ok          157 ms
test ut-R                         ... ok         1622 ms
test ut-fdw                       ... ok           43 ms
test ut-W                         ... ok          224 ms
test ut-T                         ... ok           35 ms
test ut-fini                      ... ok           13 ms

=======================
5 of 14 tests failed.
=======================

$ cat regression.diffs
diff -U3 /home/joel/postgresql-extensions/pg_hint_plan/expected/init.out /home/joel/postgresql-extensions/pg_hint_plan/results/init.out
--- /home/joel/postgresql-extensions/pg_hint_plan/expected/init.out 2021-04-29 07:57:13.223086918 +0000
+++ /home/joel/postgresql-extensions/pg_hint_plan/results/init.out  2021-04-29 08:14:28.188557252 +0000
@@ -38,6 +38,7 @@
CREATE INDEX p2_val_idx_1 ON p2 USING hash (val);
CREATE INDEX p2_val_id_idx ON p2 (val, id);
CREATE INDEX p2_val_idx2 ON p2 (val COLLATE "ja_JP");
+ERROR:  collation "ja_JP" for encoding "UTF8" does not exist
CREATE INDEX p2_val_idx3 ON p2 (val varchar_ops);
CREATE INDEX p2_val_idx4 ON p2 (val DESC NULLS LAST);
CREATE INDEX p2_val_idx5 ON p2 (val NULLS FIRST);
diff -U3 /home/joel/postgresql-extensions/pg_hint_plan/expected/pg_hint_plan.out /home/joel/postgresql-extensions/pg_hint_plan/results/pg_hint_plan.out
--- /home/joel/postgresql-extensions/pg_hint_plan/expected/pg_hint_plan.out 2021-04-29 07:57:13.227087063 +0000
+++ /home/joel/postgresql-extensions/pg_hint_plan/results/pg_hint_plan.out  2021-04-29 08:14:36.328707456 +0000
@@ -7061,14 +7061,14 @@
/*+IndexScan(p2 p2_val_idx6)*/
EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
LOG:  available indexes for IndexScan(p2): p2_val_idx6
-LOG:  available indexes for IndexScan(p2_c1): p2_c1_val_idx7
-LOG:  available indexes for IndexScan(p2_c2): p2_c2_val_idx7
-LOG:  available indexes for IndexScan(p2_c3): p2_c3_val_idx7
-LOG:  available indexes for IndexScan(p2_c4): p2_c4_val_idx7
-LOG:  available indexes for IndexScan(p2_c1_c1): p2_c1_c1_val_idx7
-LOG:  available indexes for IndexScan(p2_c1_c2): p2_c1_c2_val_idx7
-LOG:  available indexes for IndexScan(p2_c3_c1): p2_c3_c1_val_idx7
-LOG:  available indexes for IndexScan(p2_c3_c2): p2_c3_c2_val_idx7
+LOG:  available indexes for IndexScan(p2_c1): p2_c1_val_idx6
+LOG:  available indexes for IndexScan(p2_c2): p2_c2_val_idx6
+LOG:  available indexes for IndexScan(p2_c3): p2_c3_val_idx6
+LOG:  available indexes for IndexScan(p2_c4): p2_c4_val_idx6
+LOG:  available indexes for IndexScan(p2_c1_c1): p2_c1_c1_val_idx6
+LOG:  available indexes for IndexScan(p2_c1_c2): p2_c1_c2_val_idx6
+LOG:  available indexes for IndexScan(p2_c3_c1): p2_c3_c1_val_idx6
+LOG:  available indexes for IndexScan(p2_c3_c2): p2_c3_c2_val_idx6
LOG:  pg_hint_plan:
used hint:
IndexScan(p2 p2_val_idx6)
@@ -7102,14 +7102,14 @@
/*+IndexScan(p2 p2_val_idx p2_val_idx6)*/
EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
LOG:  available indexes for IndexScan(p2): p2_val_idx6 p2_val_idx
-LOG:  available indexes for IndexScan(p2_c1): p2_c1_val_idx7 p2_c1_val_idx
-LOG:  available indexes for IndexScan(p2_c2): p2_c2_val_idx7 p2_c2_val_idx
-LOG:  available indexes for IndexScan(p2_c3): p2_c3_val_idx7 p2_c3_val_idx
-LOG:  available indexes for IndexScan(p2_c4): p2_c4_val_idx7 p2_c4_val_idx
-LOG:  available indexes for IndexScan(p2_c1_c1): p2_c1_c1_val_idx7 p2_c1_c1_val_idx
-LOG:  available indexes for IndexScan(p2_c1_c2): p2_c1_c2_val_idx7 p2_c1_c2_val_idx
-LOG:  available indexes for IndexScan(p2_c3_c1): p2_c3_c1_val_idx7 p2_c3_c1_val_idx
-LOG:  available indexes for IndexScan(p2_c3_c2): p2_c3_c2_val_idx7 p2_c3_c2_val_idx
+LOG:  available indexes for IndexScan(p2_c1): p2_c1_val_idx6 p2_c1_val_idx
+LOG:  available indexes for IndexScan(p2_c2): p2_c2_val_idx6 p2_c2_val_idx
+LOG:  available indexes for IndexScan(p2_c3): p2_c3_val_idx6 p2_c3_val_idx
+LOG:  available indexes for IndexScan(p2_c4): p2_c4_val_idx6 p2_c4_val_idx
+LOG:  available indexes for IndexScan(p2_c1_c1): p2_c1_c1_val_idx6 p2_c1_c1_val_idx
+LOG:  available indexes for IndexScan(p2_c1_c2): p2_c1_c2_val_idx6 p2_c1_c2_val_idx
+LOG:  available indexes for IndexScan(p2_c3_c1): p2_c3_c1_val_idx6 p2_c3_c1_val_idx
+LOG:  available indexes for IndexScan(p2_c3_c2): p2_c3_c2_val_idx6 p2_c3_c2_val_idx
LOG:  pg_hint_plan:
used hint:
IndexScan(p2 p2_val_idx p2_val_idx6)
diff -U3 /home/joel/postgresql-extensions/pg_hint_plan/expected/ut-init.out /home/joel/postgresql-extensions/pg_hint_plan/results/ut-init.out
--- /home/joel/postgresql-extensions/pg_hint_plan/expected/ut-init.out  2021-04-29 07:57:13.247087791 +0000
+++ /home/joel/postgresql-extensions/pg_hint_plan/results/ut-init.out   2021-04-29 08:14:36.888717778 +0000
@@ -10,6 +10,7 @@
  NOLOGIN
  NOREPLICATION
  CONNECTION LIMIT 1;
+ERROR:  role "super_user" already exists
CREATE ROLE normal_user
  NOSUPERUSER
  NOCREATEDB
@@ -27,6 +28,7 @@
CREATE TABLE s2.t1 (LIKE s1.t1 INCLUDING ALL);
CREATE TABLE s1.p1 (LIKE s1.t1 INCLUDING ALL);
CREATE UNIQUE INDEX p1_parent ON s1.p1 USING btree (c4 COLLATE "ja_JP" varchar_ops ASC NULLS LAST, (c1 * 2 < 100)) WHERE c1 < 10;
+ERROR:  collation "ja_JP" for encoding "UTF8" does not exist
CREATE TABLE s1.p2 (LIKE s1.t1 INCLUDING ALL);
CREATE TABLE s1.p1c1 (LIKE s1.p1 INCLUDING ALL, CHECK (c1 <= 100)) INHERITS(s1.p1);
NOTICE:  merging column "c1" with inherited definition
diff -U3 /home/joel/postgresql-extensions/pg_hint_plan/expected/ut-A.out /home/joel/postgresql-extensions/pg_hint_plan/results/ut-A.out
--- /home/joel/postgresql-extensions/pg_hint_plan/expected/ut-A.out 2021-04-29 07:57:13.227087063 +0000
+++ /home/joel/postgresql-extensions/pg_hint_plan/results/ut-A.out  2021-04-29 08:14:37.224723968 +0000
@@ -3134,11 +3134,7 @@
----
-- No. A-11-5-1
SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
-(1 row)
-
+ERROR:  pg_stat_statements must be loaded via shared_preload_libraries
SELECT * FROM s1.t1 WHERE t1.c1 = 1;
  c1 | c2 | c3 | c4
----+----+----+----
@@ -3176,12 +3172,7 @@
  JOIN pg_catalog.pg_database d
    ON (s.dbid = d.oid)
  ORDER BY 1;
-                query                 | calls
---------------------------------------+-------
- SELECT * FROM s1.t1 WHERE t1.c1 = $1 |     3
- SELECT pg_stat_statements_reset()    |     1
-(2 rows)
-
+ERROR:  pg_stat_statements must be loaded via shared_preload_libraries
----
---- No. A-12-1 reset of global variable of core at the error
---- No. A-12-2 reset of global variable of original at the error
diff -U3 /home/joel/postgresql-extensions/pg_hint_plan/expected/ut-S.out /home/joel/postgresql-extensions/pg_hint_plan/results/ut-S.out
--- /home/joel/postgresql-extensions/pg_hint_plan/expected/ut-S.out 2021-04-29 07:57:13.247087791 +0000
+++ /home/joel/postgresql-extensions/pg_hint_plan/results/ut-S.out  2021-04-29 08:14:37.676732297 +0000
@@ -5242,8 +5242,8 @@

\o results/ut-S.tmpout
/*+IndexScan(p1 p1_parent)*/ EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10;
-LOG:  available indexes for IndexScan(p1): p1_parent
-LOG:  available indexes for IndexScan(p1c1): p1c1_c4_expr_idx
+LOG:  available indexes for IndexScan(p1):
+LOG:  available indexes for IndexScan(p1c1):
LOG:  pg_hint_plan:
used hint:
IndexScan(p1 p1_parent)
@@ -5255,11 +5255,11 @@
\! sql/maskout.sh results/ut-S.tmpout
  QUERY PLAN
----------------
- Append  (cost=xxx..xxx rows=4 width=xxx)
-   ->  Index Scan using p1_parent on p1 p1_1  (cost=xxx..xxx rows=1 width=xxx)
-         Filter: ((c2 * 2) < 100)
-   ->  Index Scan using p1c1_c4_expr_idx on p1c1 p1_2  (cost=xxx..xxx rows=3 width=xxx)
-         Filter: ((c2 * 2) < 100)
+ Append  (cost={inf}..{inf} rows=4 width=xxx)
+   ->  Seq Scan on p1 p1_1  (cost={inf}..{inf} rows=1 width=xxx)
+         Filter: ((c1 < 10) AND ((c2 * 2) < 100))
+   ->  Seq Scan on p1c1 p1_2  (cost={inf}..{inf} rows=3 width=xxx)
+         Filter: ((c1 < 10) AND ((c2 * 2) < 100))

-- No. S-3-10-4
\o results/ut-S.tmpout
@@ -6045,7 +6045,7 @@
/*+IndexScanRegexp(p1 p1.*i)*/
EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
LOG:  available indexes for IndexScanRegexp(p1): p1_i2 p1_i
-LOG:  available indexes for IndexScanRegexp(p1c1): p1c1_i p1c1_c4_expr_idx
+LOG:  available indexes for IndexScanRegexp(p1c1): p1c1_i
LOG:  pg_hint_plan:
used hint:
IndexScanRegexp(p1 p1.*i)

[Joezczhang]

Hi: Have you fixed those failures? I have almost the same questions as you. I just want to know how to solve it.

[michaelpq]

Yes, I can see the same amount of breakages here, with impacts across various branches. I am planning to work more on stabilizing all that.

[michaelpq]

The tests have now been fixed for 10~15 (I was myself annoyed with the dependency on a Japanese collation, which was actually useless for the scope of the tests). The branch PG15 has not yet been created (should be soon), so please use the master branch when doing so for now.