Fix crash bug caused by plancache invalidation

[pghintplan/pg_hint_plan.git] / sql / ut-A.sql

diff --git a/sql/ut-A.sql b/sql/ut-A.sql
index c9800b2..38cecb7 100644 (file)
--- a/sql/ut-A.sql
+++ b/sql/ut-A.sql
@@ -16,9 +16,11 @@ CREATE EXTENSION pg_hint_plan;
 DROP EXTENSION pg_hint_plan;
 
 -- No.A-1-1-4
-CREATE EXTENSION pg_hint_plan SCHEMA other_name;
+CREATE SCHEMA other_schema;
+CREATE EXTENSION pg_hint_plan SCHEMA other_schema;
 
 CREATE EXTENSION pg_hint_plan;
+DROP SCHEMA other_schema;
 
 ----
 ---- No. A-5-1 comment pattern
@@ -511,51 +513,52 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
 ----
 ---- No. A-8-1 original GUC parameter
 ----
-
+---- Don't test postgresql itself.
 -- No. A-8-1-1
-SET ROLE super_user;
-SET pg_hint_plan.debug_print TO off;
-SHOW pg_hint_plan.enable_hint;
-SHOW pg_hint_plan.debug_print;
-SHOW pg_hint_plan.parse_messages;
-SET pg_hint_plan.enable_hint TO off;
-SET pg_hint_plan.debug_print TO on;
-SET pg_hint_plan.parse_messages TO error;
-SHOW pg_hint_plan.enable_hint;
-SHOW pg_hint_plan.debug_print;
-SHOW pg_hint_plan.parse_messages;
-RESET pg_hint_plan.enable_hint;
-RESET pg_hint_plan.debug_print;
-RESET pg_hint_plan.parse_messages;
-SHOW pg_hint_plan.enable_hint;
-SHOW pg_hint_plan.debug_print;
-SHOW pg_hint_plan.parse_messages;
-
--- No. A-8-1-2
-SET ROLE normal_user;
-SHOW pg_hint_plan.enable_hint;
-SHOW pg_hint_plan.debug_print;
-SHOW pg_hint_plan.parse_messages;
-SET pg_hint_plan.enable_hint TO off;
-SET pg_hint_plan.debug_print TO on;
-SET pg_hint_plan.parse_messages TO error;
-SHOW pg_hint_plan.enable_hint;
-SHOW pg_hint_plan.debug_print;
-SHOW pg_hint_plan.parse_messages;
-RESET pg_hint_plan.enable_hint;
-RESET pg_hint_plan.debug_print;
-RESET pg_hint_plan.parse_messages;
-SHOW pg_hint_plan.enable_hint;
-SHOW pg_hint_plan.debug_print;
-SHOW pg_hint_plan.parse_messages;
-
-RESET ROLE;
+-- SET ROLE super_user;
+-- SET pg_hint_plan.debug_print TO off;
+-- SHOW pg_hint_plan.enable_hint;
+-- SHOW pg_hint_plan.debug_print;
+-- SHOW pg_hint_plan.parse_messages;
+-- SET pg_hint_plan.enable_hint TO off;
+-- SET pg_hint_plan.debug_print TO on;
+-- SET pg_hint_plan.parse_messages TO error;
+-- SHOW pg_hint_plan.enable_hint;
+-- SHOW pg_hint_plan.debug_print;
+-- SHOW pg_hint_plan.parse_messages;
+-- RESET pg_hint_plan.enable_hint;
+-- RESET pg_hint_plan.debug_print;
+-- RESET pg_hint_plan.parse_messages;
+-- SHOW pg_hint_plan.enable_hint;
+-- SHOW pg_hint_plan.debug_print;
+-- SHOW pg_hint_plan.parse_messages;
+-- 
+-- -- No. A-8-1-2
+-- SET ROLE normal_user;
+-- SHOW pg_hint_plan.enable_hint;
+-- SHOW pg_hint_plan.debug_print;
+-- SHOW pg_hint_plan.parse_messages;
+-- SET pg_hint_plan.enable_hint TO off;
+-- SET pg_hint_plan.debug_print TO on;
+-- SET pg_hint_plan.parse_messages TO error;
+-- SHOW pg_hint_plan.enable_hint;
+-- SHOW pg_hint_plan.debug_print;
+-- SHOW pg_hint_plan.parse_messages;
+-- RESET pg_hint_plan.enable_hint;
+-- RESET pg_hint_plan.debug_print;
+-- RESET pg_hint_plan.parse_messages;
+-- SHOW pg_hint_plan.enable_hint;
+-- SHOW pg_hint_plan.debug_print;
+-- SHOW pg_hint_plan.parse_messages;
+-- 
+-- RESET ROLE;
 
 ----
 ---- No. A-8-2 original GUC parameter pg_hint_plan.enable_hint
 ----
 
 -- No. A-8-2-1
+SET pg_hint_plan.debug_print TO off;
 SET pg_hint_plan.enable_hint TO on;
 SHOW pg_hint_plan.enable_hint;
 /*+Set(enable_indexscan off)*/
@@ -568,20 +571,24 @@ SHOW pg_hint_plan.enable_hint;
 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
 
 -- No. A-8-2-3
-SET pg_hint_plan.enable_hint TO DEFAULT;
-SHOW pg_hint_plan.enable_hint;
-/*+Set(enable_indexscan off)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+-- Don't test PostgreSQL itself.
+-- SET pg_hint_plan.enable_hint TO DEFAULT;
+-- SHOW pg_hint_plan.enable_hint;
+-- /*+Set(enable_indexscan off)*/
+-- EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
 
 -- No. A-8-2-4
-SET pg_hint_plan.enable_hint TO enable;
-SHOW pg_hint_plan.enable_hint;
+-- Don't test PostgreSQL itself
+-- SET pg_hint_plan.enable_hint TO enable;
+-- SHOW pg_hint_plan.enable_hint;
 
 ----
 ---- No. A-8-3 original GUC parameter pg_hint_plan.debug_print
 ----
 
 -- No. A-8-3-1
+SET pg_hint_plan.enable_hint TO on;
+SHOW pg_hint_plan.enable_hint;
 SET pg_hint_plan.debug_print TO on;
 SHOW pg_hint_plan.debug_print;
 /*+Set(enable_indexscan off)*/
@@ -1145,6 +1152,11 @@ CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$
 $$ LANGUAGE SQL IMMUTABLE;
 
 --No.13-4-1
+-- recall_planner() is reduced to constant while planning using the
+-- hint defined in the function. Then the outer query is planned based
+-- on the following hint. pg_hint_plan shows the log for the function
+-- but the resulting explain output doesn't contain the corresponding
+-- plan.
 /*+HashJoin(t_1 t_2)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 t_1
@@ -1152,6 +1164,7 @@ EXPLAIN (COSTS false)
   ORDER BY t_1.c1;
 
 --No.13-4-2
+--See description for No.13-4-1
 /*+HashJoin(st_1 st_2)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 st_1
@@ -1159,6 +1172,7 @@ EXPLAIN (COSTS false)
   ORDER BY st_1.c1;
 
 --No.13-4-3
+--See description for No.13-4-1
 /*+HashJoin(t_1 t_2)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 st_1
@@ -1166,6 +1180,7 @@ EXPLAIN (COSTS false)
   ORDER BY st_1.c1;
 
 --No.13-4-4
+--See description for No.13-4-1
 /*+HashJoin(st_1 st_2)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 t_1
@@ -1173,6 +1188,8 @@ EXPLAIN (COSTS false)
   ORDER BY t_1.c1;
 
 --No.13-4-5
+-- See description for No.13-4-1. No joins in ths plan, so
+-- pg_hint_plan doesn't complain on the wrongly written error hint.
 /*+HashJoin(t_1 t_1)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 t_1
@@ -1198,6 +1215,7 @@ EXPLAIN (COSTS false)
 DROP FUNCTION recall_planner_one_t(int);
 
 --No.13-4-7
+-- See description for No.13-4-1. Complains on the wrongly written hint.
 /*+HashJoin(t_1 t_1)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 t_1
@@ -1210,3 +1228,27 @@ EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 t_1
    JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
   ORDER BY t_1.c1;
+
+--No.14-1-1 plancache invalidation
+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
+CREATE INDEX ON s1.tpc(a);
+PREPARE p1 AS SELECT * FROM s1.tpc WHERE a < 999;
+/*+ IndexScan(tpc) */PREPARE p2 AS SELECT * FROM s1.tpc WHERE a < 999;
+/*+ SeqScan(tpc) */PREPARE p3(int) AS SELECT * FROM s1.tpc WHERE a = $1;
+EXPLAIN EXECUTE p1;
+EXPLAIN EXECUTE p2;
+EXPLAIN EXECUTE p3(500);
+-- The DROP invalidates the plan caches
+DROP TABLE s1.tpc;
+EXPLAIN EXECUTE p1;
+EXPLAIN EXECUTE p2;
+EXPLAIN EXECUTE p3(500);
+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
+CREATE INDEX ON s1.tpc(a);
+EXPLAIN EXECUTE p1;
+EXPLAIN EXECUTE p2;
+EXPLAIN EXECUTE p3(500);
+DEALLOCATE p1;
+DEALLOCATE p2;
+DEALLOCATE p3;
+DROP TABLE s1.tpc;