Support DECLARE CURSOR syntax and added regression for table hinting.

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

diff --git a/sql/ut-A.sql b/sql/ut-A.sql
index a9bfac2..7fa720c 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
@@ -33,7 +35,7 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
 
 -- No. A-5-1-3
---+SeqScan(t1)
+/*SeqScan(t1)*/
 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
 
 -- No. A-5-1-4
@@ -41,23 +43,7 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
 
 -- No. A-5-1-5
--- +SeqScan(t1)
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-
--- No. A-5-1-6
---SeqScan(t1)
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-
--- No. A-5-1-7
-/*+SeqScan(t1) /* nest comment */ */
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-
--- No. A-5-1-8
-/* +SeqScan(t1) /* nest comment */ */
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-
--- No. A-5-1-9
-/*SeqScan(t1) /* nest comment */ */
+/* /*+SeqScan(t1)*/  */
 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
 
 ----
@@ -487,6 +473,11 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
 Setあ" WHERE "tT()""  
 Setあ".c1 = 1;
 
+-- No. A-7-3-13
+-- No. A-9-2-13
+/*+SeqScan(a123456789b123456789c123456789d123456789e123456789f123)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 "123456789012345678901234567890123456789012345678901234" WHERE "123456789012345678901234567890123456789012345678901234".c1 = 1;
+
 ----
 ---- No. A-7-4 hint parse error
 ----
@@ -522,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)*/
@@ -579,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)*/
@@ -1085,11 +1081,10 @@ BEGIN
         RETURN 0;
     END IF;
 
-    EXECUTE '/*+ IndexScan(t_1) */'
-            ' SELECT nested_planner($1) FROM s1.t1 t_1'
-            ' JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)'
-            ' ORDER BY t_1.c1 LIMIT 1'
-        INTO new_cnt USING cnt - 1;
+       SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) INTO new_cnt
+         FROM s1.t1 t_1
+         JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
+        ORDER BY t_1.c1 LIMIT 1;
 
     RETURN new_cnt;
 END;
@@ -1119,6 +1114,29 @@ EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
 EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
 
 --No.13-3-3
+--
+-- Redefine not to use cached plan
+--
+CREATE OR REPLACE FUNCTION nested_planner(cnt int) RETURNS int AS $$
+DECLARE
+    new_cnt int;
+BEGIN
+    RAISE NOTICE 'nested_planner(%)', cnt;
+
+    /* 再帰終了の判断 */
+    IF cnt <= 1 THEN
+        RETURN 0;
+    END IF;
+
+       SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) INTO new_cnt
+         FROM s1.t1 t_1
+         JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
+        ORDER BY t_1.c1 LIMIT 1;
+
+    RETURN new_cnt;
+END;
+$$ LANGUAGE plpgsql IMMUTABLE;
+
 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
 /*+SeqScan(t_2)*/
 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
@@ -1126,83 +1144,76 @@ EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
 ----
 ---- No. A-13-4 output of debugging log on hint status
 ----
+CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$
+       SELECT /*+ IndexScan(t_1) */t_1.c1
+         FROM s1.t1 t_1
+         JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
+        ORDER BY t_1.c1 LIMIT 1;
+$$ LANGUAGE SQL IMMUTABLE;
 
 --No.13-4-1
 /*+HashJoin(t_1 t_2)*/
 EXPLAIN (COSTS false)
- SELECT nested_planner(2) FROM s1.t1 t_1
+ 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.13-4-2
 /*+HashJoin(st_1 st_2)*/
 EXPLAIN (COSTS false)
- SELECT nested_planner(2) FROM s1.t1 st_1
+ SELECT recall_planner() FROM s1.t1 st_1
    JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
   ORDER BY st_1.c1;
 
 --No.13-4-3
 /*+HashJoin(t_1 t_2)*/
 EXPLAIN (COSTS false)
- SELECT nested_planner(2) FROM s1.t1 st_1
+ SELECT recall_planner() FROM s1.t1 st_1
    JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
   ORDER BY st_1.c1;
 
 --No.13-4-4
 /*+HashJoin(st_1 st_2)*/
 EXPLAIN (COSTS false)
- SELECT nested_planner(2) FROM s1.t1 t_1
+ 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.13-4-5
 /*+HashJoin(t_1 t_1)*/
 EXPLAIN (COSTS false)
- SELECT nested_planner(2) FROM s1.t1 t_1
+ SELECT recall_planner() FROM s1.t1 t_1
   ORDER BY t_1.c1;
 
 --No.13-4-6
-CREATE OR REPLACE FUNCTION nested_planner_one_t(cnt int) RETURNS int AS $$
-DECLARE
-    new_cnt int;
-BEGIN
-    RAISE NOTICE 'nested_planner_one_t(%)', cnt;
-
-    IF cnt <= 1 THEN
-        RETURN 0;
-    END IF;
-
-    EXECUTE '/*+ IndexScan(t_1) */'
-            ' SELECT nested_planner_one_t($1) FROM s1.t1 t_1'
-            ' ORDER BY t_1.c1 LIMIT 1'
-        INTO new_cnt USING cnt - 1;
-
-    RETURN new_cnt;
-END;
-$$ LANGUAGE plpgsql IMMUTABLE;
+CREATE OR REPLACE FUNCTION recall_planner_one_t() RETURNS int AS $$
+       SELECT /*+ IndexScan(t_1) */t_1.c1
+         FROM s1.t1 t_1
+        ORDER BY t_1.c1 LIMIT 1;
+$$ LANGUAGE SQL IMMUTABLE;
 
 EXPLAIN (COSTS false)
- SELECT nested_planner_one_t(2) FROM s1.t1 t_1
+ SELECT recall_planner_one_t() FROM s1.t1 t_1
    JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
   ORDER BY t_1.c1;
 /*+HashJoin(t_1 t_1)*/
 EXPLAIN (COSTS false)
- SELECT nested_planner_one_t(2) FROM s1.t1 t_1
+ SELECT recall_planner_one_t() FROM s1.t1 t_1
    JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
   ORDER BY t_1.c1;
 
-DROP FUNCTION nested_planner_one_t(int);
+DROP FUNCTION recall_planner_one_t(int);
 
 --No.13-4-7
 /*+HashJoin(t_1 t_1)*/
 EXPLAIN (COSTS false)
- SELECT nested_planner(2) FROM s1.t1 t_1
+ 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.13-4-8
 /*+MergeJoin(t_1 t_2)HashJoin(t_1 t_2)*/
 EXPLAIN (COSTS false)
- SELECT nested_planner(2) FROM s1.t1 t_1
+ 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;