From 4e3c9871ca22d4a5916c1d659787bca3cb09a184 Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Fri, 14 Feb 2020 13:44:27 +0900 Subject: [PATCH] Fix behavior of nested planning Issue: https://github.com/ossc-db/pg_hint_plan/issues/39 When planning is nested by function call, the hints in the function should affect the corresponding query. Maybe 9599067 introduced that. Fix the nested planning behavior. --- expected/ut-A.out | 41 ++++++++++++++++++++++++++--------------- pg_hint_plan.c | 3 ++- sql/ut-A.sql | 11 +++++++++++ 3 files changed, 39 insertions(+), 16 deletions(-) diff --git a/expected/ut-A.out b/expected/ut-A.out index 697cdb1..1413400 100644 --- a/expected/ut-A.out +++ b/expected/ut-A.out @@ -4415,6 +4415,11 @@ CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$ ORDER BY t_1.c1 LIMIT 1; $$ 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 @@ -4422,7 +4427,7 @@ EXPLAIN (COSTS false) ORDER BY t_1.c1; LOG: pg_hint_plan: used hint: -HashJoin(t_1 t_2) +IndexScan(t_1) not used hint: duplication hint: error hint: @@ -4446,6 +4451,7 @@ error hint: (7 rows) --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 @@ -4453,8 +4459,8 @@ EXPLAIN (COSTS false) ORDER BY st_1.c1; LOG: pg_hint_plan: used hint: +IndexScan(t_1) not used hint: -HashJoin(st_1 st_2) duplication hint: error hint: @@ -4477,6 +4483,7 @@ error hint: (7 rows) --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 @@ -4484,7 +4491,7 @@ EXPLAIN (COSTS false) ORDER BY st_1.c1; LOG: pg_hint_plan: used hint: -HashJoin(t_1 t_2) +IndexScan(t_1) not used hint: duplication hint: error hint: @@ -4507,6 +4514,7 @@ error hint: (6 rows) --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 @@ -4514,8 +4522,8 @@ EXPLAIN (COSTS false) ORDER BY t_1.c1; LOG: pg_hint_plan: used hint: +IndexScan(t_1) not used hint: -HashJoin(st_1 st_2) duplication hint: error hint: @@ -4537,18 +4545,18 @@ error hint: (6 rows) --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 ORDER BY t_1.c1; -INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)" -DETAIL: Relation name "t_1" is duplicated. LOG: pg_hint_plan: used hint: +IndexScan(t_1) not used hint: duplication hint: error hint: -HashJoin(t_1 t_1) LOG: pg_hint_plan: used hint: @@ -4572,6 +4580,13 @@ EXPLAIN (COSTS false) 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; +LOG: pg_hint_plan: +used hint: +IndexScan(t_1) +not used hint: +duplication hint: +error hint: + QUERY PLAN --------------------------------------------- Merge Join @@ -4589,8 +4604,8 @@ EXPLAIN (COSTS false) ORDER BY t_1.c1; LOG: pg_hint_plan: used hint: +IndexScan(t_1) not used hint: -HashJoin(t_1 t_1) duplication hint: error hint: @@ -4616,19 +4631,18 @@ HashJoin(t_1 t_1) DROP FUNCTION recall_planner_one_t(int); ERROR: function recall_planner_one_t(integer) does not exist --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 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) ORDER BY t_1.c1; -INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)" -DETAIL: Relation name "t_1" is duplicated. LOG: pg_hint_plan: used hint: +IndexScan(t_1) not used hint: duplication hint: error hint: -HashJoin(t_1 t_1) INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)" DETAIL: Relation name "t_1" is duplicated. @@ -4657,14 +4671,11 @@ EXPLAIN (COSTS false) ORDER BY t_1.c1; INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)" DETAIL: Conflict join method hint. -INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)" -DETAIL: Conflict join method hint. LOG: pg_hint_plan: used hint: -HashJoin(t_1 t_2) +IndexScan(t_1) not used hint: duplication hint: -MergeJoin(t_1 t_2) error hint: LOG: pg_hint_plan: diff --git a/pg_hint_plan.c b/pg_hint_plan.c index b151961..f707463 100644 --- a/pg_hint_plan.c +++ b/pg_hint_plan.c @@ -1816,7 +1816,7 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery) * case of DESCRIBE message handling or EXECUTE command. We may still see a * candidate top-level query in pstate in the case. */ - if (!p && pstate) + if (pstate && pstate->p_sourcetext) p = pstate->p_sourcetext; /* We don't see a query string, return NULL */ @@ -3085,6 +3085,7 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) */ recurse_level++; prev_hint_str = current_hint_str; + current_hint_str = NULL; /* * Use PG_TRY mechanism to recover GUC parameters and current_hint_state to diff --git a/sql/ut-A.sql b/sql/ut-A.sql index 7fa720c..d25137a 100644 --- a/sql/ut-A.sql +++ b/sql/ut-A.sql @@ -1152,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 @@ -1159,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 @@ -1166,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 @@ -1173,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 @@ -1180,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 @@ -1205,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 -- 2.11.0