• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revision43fe34425fb81d26ee29a6b3a1f1866122cb3887 (tree)
Time2020-02-14 15:46:43
AuthorKyotaro Horiguchi <horikyota.ntt@gmai...>
CommiterKyotaro Horiguchi

Log Message

Fix crash bug caused by plancache invalidation

https://github.com/ossc-db/pg_hint_plan/issues/41

After plancache is invalidated then revaliated, get_query_string
accesses query_list of invalid plansource then crash. Ignore invalid
plancache and get the correct hint string and Query node at the next
planning time during revalidation.

On the way fixing this, a bug related to planner reentrance is
fixed. That fix causes behavioral change for nested
planning. Previously outer-level hint (wrongly) overrides inner-level
query but currenlty outer-level hint no longer affects inner-level
query.

Change Summary

Incremental Difference

--- a/expected/ut-A.out
+++ b/expected/ut-A.out
@@ -4459,6 +4459,11 @@ CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$
44594459 ORDER BY t_1.c1 LIMIT 1;
44604460 $$ LANGUAGE SQL IMMUTABLE;
44614461 --No.13-4-1
4462+-- recall_planner() is reduced to constant while planning using the
4463+-- hint defined in the function. Then the outer query is planned based
4464+-- on the following hint. pg_hint_plan shows the log for the function
4465+-- but the resulting explain output doesn't contain the corresponding
4466+-- plan.
44624467 /*+HashJoin(t_1 t_2)*/
44634468 EXPLAIN (COSTS false)
44644469 SELECT recall_planner() FROM s1.t1 t_1
@@ -4466,7 +4471,7 @@ EXPLAIN (COSTS false)
44664471 ORDER BY t_1.c1;
44674472 LOG: pg_hint_plan:
44684473 used hint:
4469-HashJoin(t_1 t_2)
4474+IndexScan(t_1)
44704475 not used hint:
44714476 duplication hint:
44724477 error hint:
@@ -4491,6 +4496,7 @@ error hint:
44914496 (7 rows)
44924497
44934498 --No.13-4-2
4499+--See description for No.13-4-1
44944500 /*+HashJoin(st_1 st_2)*/
44954501 EXPLAIN (COSTS false)
44964502 SELECT recall_planner() FROM s1.t1 st_1
@@ -4498,8 +4504,8 @@ EXPLAIN (COSTS false)
44984504 ORDER BY st_1.c1;
44994505 LOG: pg_hint_plan:
45004506 used hint:
4507+IndexScan(t_1)
45014508 not used hint:
4502-HashJoin(st_1 st_2)
45034509 duplication hint:
45044510 error hint:
45054511
@@ -4523,6 +4529,7 @@ error hint:
45234529 (7 rows)
45244530
45254531 --No.13-4-3
4532+--See description for No.13-4-1
45264533 /*+HashJoin(t_1 t_2)*/
45274534 EXPLAIN (COSTS false)
45284535 SELECT recall_planner() FROM s1.t1 st_1
@@ -4530,7 +4537,7 @@ EXPLAIN (COSTS false)
45304537 ORDER BY st_1.c1;
45314538 LOG: pg_hint_plan:
45324539 used hint:
4533-HashJoin(t_1 t_2)
4540+IndexScan(t_1)
45344541 not used hint:
45354542 duplication hint:
45364543 error hint:
@@ -4554,6 +4561,7 @@ error hint:
45544561 (6 rows)
45554562
45564563 --No.13-4-4
4564+--See description for No.13-4-1
45574565 /*+HashJoin(st_1 st_2)*/
45584566 EXPLAIN (COSTS false)
45594567 SELECT recall_planner() FROM s1.t1 t_1
@@ -4561,8 +4569,8 @@ EXPLAIN (COSTS false)
45614569 ORDER BY t_1.c1;
45624570 LOG: pg_hint_plan:
45634571 used hint:
4572+IndexScan(t_1)
45644573 not used hint:
4565-HashJoin(st_1 st_2)
45664574 duplication hint:
45674575 error hint:
45684576
@@ -4585,19 +4593,18 @@ error hint:
45854593 (6 rows)
45864594
45874595 --No.13-4-5
4596+-- See description for No.13-4-1. No joins in ths plan, so
4597+-- pg_hint_plan doesn't complain on the wrongly written error hint.
45884598 /*+HashJoin(t_1 t_1)*/
45894599 EXPLAIN (COSTS false)
45904600 SELECT recall_planner() FROM s1.t1 t_1
45914601 ORDER BY t_1.c1;
4592-INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
4593-DETAIL: Relation name "t_1" is duplicated.
4594-CONTEXT: SQL function "recall_planner" during startup
45954602 LOG: pg_hint_plan:
45964603 used hint:
4604+IndexScan(t_1)
45974605 not used hint:
45984606 duplication hint:
45994607 error hint:
4600-HashJoin(t_1 t_1)
46014608
46024609 CONTEXT: SQL function "recall_planner" during startup
46034610 LOG: pg_hint_plan:
@@ -4622,6 +4629,14 @@ EXPLAIN (COSTS false)
46224629 SELECT recall_planner_one_t() FROM s1.t1 t_1
46234630 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
46244631 ORDER BY t_1.c1;
4632+LOG: pg_hint_plan:
4633+used hint:
4634+IndexScan(t_1)
4635+not used hint:
4636+duplication hint:
4637+error hint:
4638+
4639+CONTEXT: SQL function "recall_planner_one_t" during startup
46254640 QUERY PLAN
46264641 ---------------------------------------------
46274642 Merge Join
@@ -4639,8 +4654,8 @@ EXPLAIN (COSTS false)
46394654 ORDER BY t_1.c1;
46404655 LOG: pg_hint_plan:
46414656 used hint:
4657+IndexScan(t_1)
46424658 not used hint:
4643-HashJoin(t_1 t_1)
46444659 duplication hint:
46454660 error hint:
46464661
@@ -4667,20 +4682,18 @@ HashJoin(t_1 t_1)
46674682 DROP FUNCTION recall_planner_one_t(int);
46684683 ERROR: function recall_planner_one_t(integer) does not exist
46694684 --No.13-4-7
4685+-- See description for No.13-4-1. Complains on the wrongly written hint.
46704686 /*+HashJoin(t_1 t_1)*/
46714687 EXPLAIN (COSTS false)
46724688 SELECT recall_planner() FROM s1.t1 t_1
46734689 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
46744690 ORDER BY t_1.c1;
4675-INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
4676-DETAIL: Relation name "t_1" is duplicated.
4677-CONTEXT: SQL function "recall_planner" during startup
46784691 LOG: pg_hint_plan:
46794692 used hint:
4693+IndexScan(t_1)
46804694 not used hint:
46814695 duplication hint:
46824696 error hint:
4683-HashJoin(t_1 t_1)
46844697
46854698 CONTEXT: SQL function "recall_planner" during startup
46864699 INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
@@ -4710,15 +4723,11 @@ EXPLAIN (COSTS false)
47104723 ORDER BY t_1.c1;
47114724 INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)"
47124725 DETAIL: Conflict join method hint.
4713-INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)"
4714-DETAIL: Conflict join method hint.
4715-CONTEXT: SQL function "recall_planner" during startup
47164726 LOG: pg_hint_plan:
47174727 used hint:
4718-HashJoin(t_1 t_2)
4728+IndexScan(t_1)
47194729 not used hint:
47204730 duplication hint:
4721-MergeJoin(t_1 t_2)
47224731 error hint:
47234732
47244733 CONTEXT: SQL function "recall_planner" during startup
@@ -4741,3 +4750,93 @@ error hint:
47414750 -> Seq Scan on t2 t_2
47424751 (7 rows)
47434752
4753+--No.14-1-1 plancache invalidation
4754+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
4755+CREATE INDEX ON s1.tpc(a);
4756+PREPARE p1 AS SELECT * FROM s1.tpc WHERE a < 999;
4757+/*+ IndexScan(tpc) */PREPARE p2 AS SELECT * FROM s1.tpc WHERE a < 999;
4758+/*+ SeqScan(tpc) */PREPARE p3(int) AS SELECT * FROM s1.tpc WHERE a = $1;
4759+EXPLAIN EXECUTE p1;
4760+ QUERY PLAN
4761+------------------------------------------------------
4762+ Seq Scan on tpc (cost=0.00..17.50 rows=333 width=4)
4763+ Filter: (a < 999)
4764+(2 rows)
4765+
4766+EXPLAIN EXECUTE p2;
4767+LOG: pg_hint_plan:
4768+used hint:
4769+IndexScan(tpc)
4770+not used hint:
4771+duplication hint:
4772+error hint:
4773+
4774+ QUERY PLAN
4775+------------------------------------------------------------------------
4776+ Index Scan using tpc_a_idx on tpc (cost=0.28..34.10 rows=333 width=4)
4777+ Index Cond: (a < 999)
4778+(2 rows)
4779+
4780+EXPLAIN EXECUTE p3(500);
4781+LOG: pg_hint_plan:
4782+used hint:
4783+SeqScan(tpc)
4784+not used hint:
4785+duplication hint:
4786+error hint:
4787+
4788+ QUERY PLAN
4789+----------------------------------------------------
4790+ Seq Scan on tpc (cost=0.00..17.50 rows=5 width=4)
4791+ Filter: (a = 500)
4792+(2 rows)
4793+
4794+-- The DROP invalidates the plan caches
4795+DROP TABLE s1.tpc;
4796+EXPLAIN EXECUTE p1;
4797+ERROR: relation "s1.tpc" does not exist
4798+EXPLAIN EXECUTE p2;
4799+ERROR: relation "s1.tpc" does not exist
4800+EXPLAIN EXECUTE p3(500);
4801+ERROR: relation "s1.tpc" does not exist
4802+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
4803+CREATE INDEX ON s1.tpc(a);
4804+EXPLAIN EXECUTE p1;
4805+ QUERY PLAN
4806+------------------------------------------------------
4807+ Seq Scan on tpc (cost=0.00..17.50 rows=333 width=4)
4808+ Filter: (a < 999)
4809+(2 rows)
4810+
4811+EXPLAIN EXECUTE p2;
4812+LOG: pg_hint_plan:
4813+used hint:
4814+IndexScan(tpc)
4815+not used hint:
4816+duplication hint:
4817+error hint:
4818+
4819+ QUERY PLAN
4820+------------------------------------------------------------------------
4821+ Index Scan using tpc_a_idx on tpc (cost=0.28..34.10 rows=333 width=4)
4822+ Index Cond: (a < 999)
4823+(2 rows)
4824+
4825+EXPLAIN EXECUTE p3(500);
4826+LOG: pg_hint_plan:
4827+used hint:
4828+SeqScan(tpc)
4829+not used hint:
4830+duplication hint:
4831+error hint:
4832+
4833+ QUERY PLAN
4834+----------------------------------------------------
4835+ Seq Scan on tpc (cost=0.00..17.50 rows=5 width=4)
4836+ Filter: (a = 500)
4837+(2 rows)
4838+
4839+DEALLOCATE p1;
4840+DEALLOCATE p2;
4841+DEALLOCATE p3;
4842+DROP TABLE s1.tpc;
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -451,6 +451,7 @@ static int pg_hint_plan_debug_message_level = LOG;
451451 static bool pg_hint_plan_enable_hint_table = false;
452452
453453 static int plpgsql_recurse_level = 0; /* PLpgSQL recursion level */
454+static int recurse_level = 0; /* recursion level incl. direct SPI calls */
454455 static int hint_inhibit_level = 0; /* Inhibit hinting if this is above 0 */
455456 /* (This could not be above 1) */
456457
@@ -1678,7 +1679,7 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery)
16781679 * case of DESCRIBE message handling or EXECUTE command. We may still see a
16791680 * candidate top-level query in pstate in the case.
16801681 */
1681- if (!p && pstate)
1682+ if (pstate && pstate->p_sourcetext)
16821683 p = pstate->p_sourcetext;
16831684
16841685 /* We don't see a query string, return NULL */
@@ -1755,13 +1756,24 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery)
17551756 PreparedStatement *entry;
17561757
17571758 entry = FetchPreparedStatement(stmt->name, true);
1758- p = entry->plansource->query_string;
1759- target_query = (Query *) linitial (entry->plansource->query_list);
1759+
1760+ if (entry->plansource->is_valid)
1761+ {
1762+ p = entry->plansource->query_string;
1763+ target_query = (Query *) linitial (entry->plansource->query_list);
1764+ }
1765+ else
1766+ {
1767+ /* igonre the hint for EXECUTE if invalidated */
1768+ p = NULL;
1769+ target_query = NULL;
1770+ }
17601771 }
17611772
17621773 /* JumbleQuery accespts only a non-utility Query */
1763- if (!IsA(target_query, Query) ||
1764- target_query->utilityStmt != NULL)
1774+ if (target_query &&
1775+ (!IsA(target_query, Query) ||
1776+ target_query->utilityStmt != NULL))
17651777 target_query = NULL;
17661778
17671779 if (jumblequery)
@@ -2598,6 +2610,14 @@ get_current_hint_string(ParseState *pstate, Query *query)
25982610 current_hint_str = get_hints_from_comment(query_str);
25992611 MemoryContextSwitchTo(oldcontext);
26002612 }
2613+ else
2614+ {
2615+ /*
2616+ * Failed to get query. We would be in fetching invalidated
2617+ * plancache. Try the next chance.
2618+ */
2619+ current_hint_retrieved = false;
2620+ }
26012621
26022622 if (debug_level > 1)
26032623 {
@@ -2664,6 +2684,7 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
26642684 int save_nestlevel;
26652685 PlannedStmt *result;
26662686 HintState *hstate;
2687+ const char *prev_hint_str = NULL;
26672688
26682689 /*
26692690 * Use standard planner if pg_hint_plan is disabled or current nesting
@@ -2756,8 +2777,17 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
27562777 }
27572778
27582779 /*
2759- * Use PG_TRY mechanism to recover GUC parameters and current_hint to the
2760- * state when this planner started when error occurred in planner.
2780+ * The planner call below may replace current_hint_str. Store and restore
2781+ * it so that the subsequent planning in the upper level doesn't get
2782+ * confused.
2783+ */
2784+ recurse_level++;
2785+ prev_hint_str = current_hint_str;
2786+ current_hint_str = NULL;
2787+
2788+ /*
2789+ * Use PG_TRY mechanism to recover GUC parameters and current_hint_state to
2790+ * the state when this planner started when error occurred in planner.
27612791 */
27622792 PG_TRY();
27632793 {
@@ -2765,6 +2795,9 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
27652795 result = (*prev_planner) (parse, cursorOptions, boundParams);
27662796 else
27672797 result = standard_planner(parse, cursorOptions, boundParams);
2798+
2799+ current_hint_str = prev_hint_str;
2800+ recurse_level--;
27682801 }
27692802 PG_CATCH();
27702803 {
@@ -2772,6 +2805,8 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
27722805 * Rollback changes of GUC parameters, and pop current hint context
27732806 * from hint stack to rewind the state.
27742807 */
2808+ current_hint_str = prev_hint_str;
2809+ recurse_level--;
27752810 AtEOXact_GUC(true, save_nestlevel);
27762811 pop_hint();
27772812 PG_RE_THROW();
--- a/sql/ut-A.sql
+++ b/sql/ut-A.sql
@@ -1150,6 +1150,11 @@ CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$
11501150 $$ LANGUAGE SQL IMMUTABLE;
11511151
11521152 --No.13-4-1
1153+-- recall_planner() is reduced to constant while planning using the
1154+-- hint defined in the function. Then the outer query is planned based
1155+-- on the following hint. pg_hint_plan shows the log for the function
1156+-- but the resulting explain output doesn't contain the corresponding
1157+-- plan.
11531158 /*+HashJoin(t_1 t_2)*/
11541159 EXPLAIN (COSTS false)
11551160 SELECT recall_planner() FROM s1.t1 t_1
@@ -1157,6 +1162,7 @@ EXPLAIN (COSTS false)
11571162 ORDER BY t_1.c1;
11581163
11591164 --No.13-4-2
1165+--See description for No.13-4-1
11601166 /*+HashJoin(st_1 st_2)*/
11611167 EXPLAIN (COSTS false)
11621168 SELECT recall_planner() FROM s1.t1 st_1
@@ -1164,6 +1170,7 @@ EXPLAIN (COSTS false)
11641170 ORDER BY st_1.c1;
11651171
11661172 --No.13-4-3
1173+--See description for No.13-4-1
11671174 /*+HashJoin(t_1 t_2)*/
11681175 EXPLAIN (COSTS false)
11691176 SELECT recall_planner() FROM s1.t1 st_1
@@ -1171,6 +1178,7 @@ EXPLAIN (COSTS false)
11711178 ORDER BY st_1.c1;
11721179
11731180 --No.13-4-4
1181+--See description for No.13-4-1
11741182 /*+HashJoin(st_1 st_2)*/
11751183 EXPLAIN (COSTS false)
11761184 SELECT recall_planner() FROM s1.t1 t_1
@@ -1178,6 +1186,8 @@ EXPLAIN (COSTS false)
11781186 ORDER BY t_1.c1;
11791187
11801188 --No.13-4-5
1189+-- See description for No.13-4-1. No joins in ths plan, so
1190+-- pg_hint_plan doesn't complain on the wrongly written error hint.
11811191 /*+HashJoin(t_1 t_1)*/
11821192 EXPLAIN (COSTS false)
11831193 SELECT recall_planner() FROM s1.t1 t_1
@@ -1203,6 +1213,7 @@ EXPLAIN (COSTS false)
12031213 DROP FUNCTION recall_planner_one_t(int);
12041214
12051215 --No.13-4-7
1216+-- See description for No.13-4-1. Complains on the wrongly written hint.
12061217 /*+HashJoin(t_1 t_1)*/
12071218 EXPLAIN (COSTS false)
12081219 SELECT recall_planner() FROM s1.t1 t_1
@@ -1215,3 +1226,27 @@ EXPLAIN (COSTS false)
12151226 SELECT recall_planner() FROM s1.t1 t_1
12161227 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
12171228 ORDER BY t_1.c1;
1229+
1230+--No.14-1-1 plancache invalidation
1231+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
1232+CREATE INDEX ON s1.tpc(a);
1233+PREPARE p1 AS SELECT * FROM s1.tpc WHERE a < 999;
1234+/*+ IndexScan(tpc) */PREPARE p2 AS SELECT * FROM s1.tpc WHERE a < 999;
1235+/*+ SeqScan(tpc) */PREPARE p3(int) AS SELECT * FROM s1.tpc WHERE a = $1;
1236+EXPLAIN EXECUTE p1;
1237+EXPLAIN EXECUTE p2;
1238+EXPLAIN EXECUTE p3(500);
1239+-- The DROP invalidates the plan caches
1240+DROP TABLE s1.tpc;
1241+EXPLAIN EXECUTE p1;
1242+EXPLAIN EXECUTE p2;
1243+EXPLAIN EXECUTE p3(500);
1244+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
1245+CREATE INDEX ON s1.tpc(a);
1246+EXPLAIN EXECUTE p1;
1247+EXPLAIN EXECUTE p2;
1248+EXPLAIN EXECUTE p3(500);
1249+DEALLOCATE p1;
1250+DEALLOCATE p2;
1251+DEALLOCATE p3;
1252+DROP TABLE s1.tpc;
Show on old repository browser