• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revisionceb5af4e8d1baaf04faeee21c602bddb1dc75e4c (tree)
Time2020-02-14 15:43:50
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
@@ -4464,6 +4464,11 @@ CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$
44644464 ORDER BY t_1.c1 LIMIT 1;
44654465 $$ LANGUAGE SQL IMMUTABLE;
44664466 --No.13-4-1
4467+-- recall_planner() is reduced to constant while planning using the
4468+-- hint defined in the function. Then the outer query is planned based
4469+-- on the following hint. pg_hint_plan shows the log for the function
4470+-- but the resulting explain output doesn't contain the corresponding
4471+-- plan.
44674472 /*+HashJoin(t_1 t_2)*/
44684473 EXPLAIN (COSTS false)
44694474 SELECT recall_planner() FROM s1.t1 t_1
@@ -4471,7 +4476,7 @@ EXPLAIN (COSTS false)
44714476 ORDER BY t_1.c1;
44724477 LOG: pg_hint_plan:
44734478 used hint:
4474-HashJoin(t_1 t_2)
4479+IndexScan(t_1)
44754480 not used hint:
44764481 duplication hint:
44774482 error hint:
@@ -4496,6 +4501,7 @@ error hint:
44964501 (7 rows)
44974502
44984503 --No.13-4-2
4504+--See description for No.13-4-1
44994505 /*+HashJoin(st_1 st_2)*/
45004506 EXPLAIN (COSTS false)
45014507 SELECT recall_planner() FROM s1.t1 st_1
@@ -4503,8 +4509,8 @@ EXPLAIN (COSTS false)
45034509 ORDER BY st_1.c1;
45044510 LOG: pg_hint_plan:
45054511 used hint:
4512+IndexScan(t_1)
45064513 not used hint:
4507-HashJoin(st_1 st_2)
45084514 duplication hint:
45094515 error hint:
45104516
@@ -4528,6 +4534,7 @@ error hint:
45284534 (7 rows)
45294535
45304536 --No.13-4-3
4537+--See description for No.13-4-1
45314538 /*+HashJoin(t_1 t_2)*/
45324539 EXPLAIN (COSTS false)
45334540 SELECT recall_planner() FROM s1.t1 st_1
@@ -4535,7 +4542,7 @@ EXPLAIN (COSTS false)
45354542 ORDER BY st_1.c1;
45364543 LOG: pg_hint_plan:
45374544 used hint:
4538-HashJoin(t_1 t_2)
4545+IndexScan(t_1)
45394546 not used hint:
45404547 duplication hint:
45414548 error hint:
@@ -4559,6 +4566,7 @@ error hint:
45594566 (6 rows)
45604567
45614568 --No.13-4-4
4569+--See description for No.13-4-1
45624570 /*+HashJoin(st_1 st_2)*/
45634571 EXPLAIN (COSTS false)
45644572 SELECT recall_planner() FROM s1.t1 t_1
@@ -4566,8 +4574,8 @@ EXPLAIN (COSTS false)
45664574 ORDER BY t_1.c1;
45674575 LOG: pg_hint_plan:
45684576 used hint:
4577+IndexScan(t_1)
45694578 not used hint:
4570-HashJoin(st_1 st_2)
45714579 duplication hint:
45724580 error hint:
45734581
@@ -4590,19 +4598,18 @@ error hint:
45904598 (6 rows)
45914599
45924600 --No.13-4-5
4601+-- See description for No.13-4-1. No joins in ths plan, so
4602+-- pg_hint_plan doesn't complain on the wrongly written error hint.
45934603 /*+HashJoin(t_1 t_1)*/
45944604 EXPLAIN (COSTS false)
45954605 SELECT recall_planner() FROM s1.t1 t_1
45964606 ORDER BY t_1.c1;
4597-INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
4598-DETAIL: Relation name "t_1" is duplicated.
4599-CONTEXT: SQL function "recall_planner" during startup
46004607 LOG: pg_hint_plan:
46014608 used hint:
4609+IndexScan(t_1)
46024610 not used hint:
46034611 duplication hint:
46044612 error hint:
4605-HashJoin(t_1 t_1)
46064613
46074614 CONTEXT: SQL function "recall_planner" during startup
46084615 LOG: pg_hint_plan:
@@ -4627,6 +4634,14 @@ EXPLAIN (COSTS false)
46274634 SELECT recall_planner_one_t() FROM s1.t1 t_1
46284635 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
46294636 ORDER BY t_1.c1;
4637+LOG: pg_hint_plan:
4638+used hint:
4639+IndexScan(t_1)
4640+not used hint:
4641+duplication hint:
4642+error hint:
4643+
4644+CONTEXT: SQL function "recall_planner_one_t" during startup
46304645 QUERY PLAN
46314646 ---------------------------------------------
46324647 Merge Join
@@ -4644,8 +4659,8 @@ EXPLAIN (COSTS false)
46444659 ORDER BY t_1.c1;
46454660 LOG: pg_hint_plan:
46464661 used hint:
4662+IndexScan(t_1)
46474663 not used hint:
4648-HashJoin(t_1 t_1)
46494664 duplication hint:
46504665 error hint:
46514666
@@ -4672,20 +4687,18 @@ HashJoin(t_1 t_1)
46724687 DROP FUNCTION recall_planner_one_t(int);
46734688 ERROR: function recall_planner_one_t(integer) does not exist
46744689 --No.13-4-7
4690+-- See description for No.13-4-1. Complains on the wrongly written hint.
46754691 /*+HashJoin(t_1 t_1)*/
46764692 EXPLAIN (COSTS false)
46774693 SELECT recall_planner() FROM s1.t1 t_1
46784694 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
46794695 ORDER BY t_1.c1;
4680-INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
4681-DETAIL: Relation name "t_1" is duplicated.
4682-CONTEXT: SQL function "recall_planner" during startup
46834696 LOG: pg_hint_plan:
46844697 used hint:
4698+IndexScan(t_1)
46854699 not used hint:
46864700 duplication hint:
46874701 error hint:
4688-HashJoin(t_1 t_1)
46894702
46904703 CONTEXT: SQL function "recall_planner" during startup
46914704 INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
@@ -4715,15 +4728,11 @@ EXPLAIN (COSTS false)
47154728 ORDER BY t_1.c1;
47164729 INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)"
47174730 DETAIL: Conflict join method hint.
4718-INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)"
4719-DETAIL: Conflict join method hint.
4720-CONTEXT: SQL function "recall_planner" during startup
47214731 LOG: pg_hint_plan:
47224732 used hint:
4723-HashJoin(t_1 t_2)
4733+IndexScan(t_1)
47244734 not used hint:
47254735 duplication hint:
4726-MergeJoin(t_1 t_2)
47274736 error hint:
47284737
47294738 CONTEXT: SQL function "recall_planner" during startup
@@ -4746,3 +4755,93 @@ error hint:
47464755 -> Seq Scan on t2 t_2
47474756 (7 rows)
47484757
4758+--No.14-1-1 plancache invalidation
4759+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
4760+CREATE INDEX ON s1.tpc(a);
4761+PREPARE p1 AS SELECT * FROM s1.tpc WHERE a < 999;
4762+/*+ IndexScan(tpc) */PREPARE p2 AS SELECT * FROM s1.tpc WHERE a < 999;
4763+/*+ SeqScan(tpc) */PREPARE p3(int) AS SELECT * FROM s1.tpc WHERE a = $1;
4764+EXPLAIN EXECUTE p1;
4765+ QUERY PLAN
4766+------------------------------------------------------
4767+ Seq Scan on tpc (cost=0.00..17.50 rows=333 width=4)
4768+ Filter: (a < 999)
4769+(2 rows)
4770+
4771+EXPLAIN EXECUTE p2;
4772+LOG: pg_hint_plan:
4773+used hint:
4774+IndexScan(tpc)
4775+not used hint:
4776+duplication hint:
4777+error hint:
4778+
4779+ QUERY PLAN
4780+------------------------------------------------------------------------
4781+ Index Scan using tpc_a_idx on tpc (cost=0.28..34.10 rows=333 width=4)
4782+ Index Cond: (a < 999)
4783+(2 rows)
4784+
4785+EXPLAIN EXECUTE p3(500);
4786+LOG: pg_hint_plan:
4787+used hint:
4788+SeqScan(tpc)
4789+not used hint:
4790+duplication hint:
4791+error hint:
4792+
4793+ QUERY PLAN
4794+----------------------------------------------------
4795+ Seq Scan on tpc (cost=0.00..17.50 rows=5 width=4)
4796+ Filter: (a = 500)
4797+(2 rows)
4798+
4799+-- The DROP invalidates the plan caches
4800+DROP TABLE s1.tpc;
4801+EXPLAIN EXECUTE p1;
4802+ERROR: relation "s1.tpc" does not exist
4803+EXPLAIN EXECUTE p2;
4804+ERROR: relation "s1.tpc" does not exist
4805+EXPLAIN EXECUTE p3(500);
4806+ERROR: relation "s1.tpc" does not exist
4807+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
4808+CREATE INDEX ON s1.tpc(a);
4809+EXPLAIN EXECUTE p1;
4810+ QUERY PLAN
4811+------------------------------------------------------
4812+ Seq Scan on tpc (cost=0.00..17.50 rows=333 width=4)
4813+ Filter: (a < 999)
4814+(2 rows)
4815+
4816+EXPLAIN EXECUTE p2;
4817+LOG: pg_hint_plan:
4818+used hint:
4819+IndexScan(tpc)
4820+not used hint:
4821+duplication hint:
4822+error hint:
4823+
4824+ QUERY PLAN
4825+------------------------------------------------------------------------
4826+ Index Scan using tpc_a_idx on tpc (cost=0.28..34.10 rows=333 width=4)
4827+ Index Cond: (a < 999)
4828+(2 rows)
4829+
4830+EXPLAIN EXECUTE p3(500);
4831+LOG: pg_hint_plan:
4832+used hint:
4833+SeqScan(tpc)
4834+not used hint:
4835+duplication hint:
4836+error hint:
4837+
4838+ QUERY PLAN
4839+----------------------------------------------------
4840+ Seq Scan on tpc (cost=0.00..17.50 rows=5 width=4)
4841+ Filter: (a = 500)
4842+(2 rows)
4843+
4844+DEALLOCATE p1;
4845+DEALLOCATE p2;
4846+DEALLOCATE p3;
4847+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
@@ -1679,7 +1680,7 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery)
16791680 * case of DESCRIBE message handling or EXECUTE command. We may still see a
16801681 * candidate top-level query in pstate in the case.
16811682 */
1682- if (!p && pstate)
1683+ if (pstate && pstate->p_sourcetext)
16831684 p = pstate->p_sourcetext;
16841685
16851686 /* We don't see a query string, return NULL */
@@ -1756,13 +1757,24 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery)
17561757 PreparedStatement *entry;
17571758
17581759 entry = FetchPreparedStatement(stmt->name, true);
1759- p = entry->plansource->query_string;
1760- target_query = (Query *) linitial (entry->plansource->query_list);
1760+
1761+ if (entry->plansource->is_valid)
1762+ {
1763+ p = entry->plansource->query_string;
1764+ target_query = (Query *) linitial (entry->plansource->query_list);
1765+ }
1766+ else
1767+ {
1768+ /* igonre the hint for EXECUTE if invalidated */
1769+ p = NULL;
1770+ target_query = NULL;
1771+ }
17611772 }
17621773
17631774 /* JumbleQuery accespts only a non-utility Query */
1764- if (!IsA(target_query, Query) ||
1765- target_query->utilityStmt != NULL)
1775+ if (target_query &&
1776+ (!IsA(target_query, Query) ||
1777+ target_query->utilityStmt != NULL))
17661778 target_query = NULL;
17671779
17681780 if (jumblequery)
@@ -2601,6 +2613,14 @@ get_current_hint_string(ParseState *pstate, Query *query)
26012613 current_hint_str = get_hints_from_comment(query_str);
26022614 MemoryContextSwitchTo(oldcontext);
26032615 }
2616+ else
2617+ {
2618+ /*
2619+ * Failed to get query. We would be in fetching invalidated
2620+ * plancache. Try the next chance.
2621+ */
2622+ current_hint_retrieved = false;
2623+ }
26042624
26052625 if (debug_level > 1)
26062626 {
@@ -2669,6 +2689,7 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
26692689 int save_nestlevel;
26702690 PlannedStmt *result;
26712691 HintState *hstate;
2692+ const char *prev_hint_str = NULL;
26722693
26732694 /*
26742695 * Use standard planner if pg_hint_plan is disabled or current nesting
@@ -2761,8 +2782,17 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
27612782 }
27622783
27632784 /*
2764- * Use PG_TRY mechanism to recover GUC parameters and current_hint to the
2765- * state when this planner started when error occurred in planner.
2785+ * The planner call below may replace current_hint_str. Store and restore
2786+ * it so that the subsequent planning in the upper level doesn't get
2787+ * confused.
2788+ */
2789+ recurse_level++;
2790+ prev_hint_str = current_hint_str;
2791+ current_hint_str = NULL;
2792+
2793+ /*
2794+ * Use PG_TRY mechanism to recover GUC parameters and current_hint_state to
2795+ * the state when this planner started when error occurred in planner.
27662796 */
27672797 PG_TRY();
27682798 {
@@ -2770,6 +2800,9 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
27702800 result = (*prev_planner) (parse, cursorOptions, boundParams);
27712801 else
27722802 result = standard_planner(parse, cursorOptions, boundParams);
2803+
2804+ current_hint_str = prev_hint_str;
2805+ recurse_level--;
27732806 }
27742807 PG_CATCH();
27752808 {
@@ -2777,6 +2810,8 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
27772810 * Rollback changes of GUC parameters, and pop current hint context
27782811 * from hint stack to rewind the state.
27792812 */
2813+ current_hint_str = prev_hint_str;
2814+ recurse_level--;
27802815 AtEOXact_GUC(true, save_nestlevel);
27812816 pop_hint();
27822817 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