OSDN -- Develop and Download Open Source Software
  • R/O
  • HTTP
  • SSH
  • HTTPS
Fork

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revision0576a9485c3289d9a3bc49b63654cf8dcbe3fd04 (tree)
Time2020-02-14 13:00:01
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
@@ -4436,7 +4436,7 @@ EXPLAIN (COSTS false)
44364436 ORDER BY t_1.c1;
44374437 LOG: pg_hint_plan:
44384438 used hint:
4439-HashJoin(t_1 t_2)
4439+IndexScan(t_1)
44404440 not used hint:
44414441 duplication hint:
44424442 error hint:
@@ -4460,6 +4460,11 @@ error hint:
44604460 (7 rows)
44614461
44624462 --No.13-4-2
4463+-- recall_planner() is reduced to constant while planning using the
4464+-- hint defined in the function. Then the outer query is planned based
4465+-- on the following hint. pg_hint_plan shows the log for the function
4466+-- but the resulting explain output doesn't contain the corresponding
4467+-- plan.
44634468 /*+HashJoin(st_1 st_2)*/
44644469 EXPLAIN (COSTS false)
44654470 SELECT recall_planner() FROM s1.t1 st_1
@@ -4467,8 +4472,8 @@ EXPLAIN (COSTS false)
44674472 ORDER BY st_1.c1;
44684473 LOG: pg_hint_plan:
44694474 used hint:
4475+IndexScan(t_1)
44704476 not used hint:
4471-HashJoin(st_1 st_2)
44724477 duplication hint:
44734478 error hint:
44744479
@@ -4491,6 +4496,7 @@ error hint:
44914496 (7 rows)
44924497
44934498 --No.13-4-3
4499+--See description for No.13-4-2
44944500 /*+HashJoin(t_1 t_2)*/
44954501 EXPLAIN (COSTS false)
44964502 SELECT recall_planner() FROM s1.t1 st_1
@@ -4498,7 +4504,7 @@ EXPLAIN (COSTS false)
44984504 ORDER BY st_1.c1;
44994505 LOG: pg_hint_plan:
45004506 used hint:
4501-HashJoin(t_1 t_2)
4507+IndexScan(t_1)
45024508 not used hint:
45034509 duplication hint:
45044510 error hint:
@@ -4521,6 +4527,7 @@ error hint:
45214527 (6 rows)
45224528
45234529 --No.13-4-4
4530+--See description for No.13-4-2
45244531 /*+HashJoin(st_1 st_2)*/
45254532 EXPLAIN (COSTS false)
45264533 SELECT recall_planner() FROM s1.t1 t_1
@@ -4528,8 +4535,8 @@ EXPLAIN (COSTS false)
45284535 ORDER BY t_1.c1;
45294536 LOG: pg_hint_plan:
45304537 used hint:
4538+IndexScan(t_1)
45314539 not used hint:
4532-HashJoin(st_1 st_2)
45334540 duplication hint:
45344541 error hint:
45354542
@@ -4551,18 +4558,18 @@ error hint:
45514558 (6 rows)
45524559
45534560 --No.13-4-5
4561+-- See description for No.13-4-2. No joins in ths plan, so
4562+-- pg_hint_plan doesn't complain on the wrongly written error hint.
45544563 /*+HashJoin(t_1 t_1)*/
45554564 EXPLAIN (COSTS false)
45564565 SELECT recall_planner() FROM s1.t1 t_1
45574566 ORDER BY t_1.c1;
4558-INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
4559-DETAIL: Relation name "t_1" is duplicated.
45604567 LOG: pg_hint_plan:
45614568 used hint:
4569+IndexScan(t_1)
45624570 not used hint:
45634571 duplication hint:
45644572 error hint:
4565-HashJoin(t_1 t_1)
45664573
45674574 LOG: pg_hint_plan:
45684575 used hint:
@@ -4586,6 +4593,13 @@ EXPLAIN (COSTS false)
45864593 SELECT recall_planner_one_t() FROM s1.t1 t_1
45874594 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
45884595 ORDER BY t_1.c1;
4596+LOG: pg_hint_plan:
4597+used hint:
4598+IndexScan(t_1)
4599+not used hint:
4600+duplication hint:
4601+error hint:
4602+
45894603 QUERY PLAN
45904604 ---------------------------------------------
45914605 Merge Join
@@ -4603,8 +4617,8 @@ EXPLAIN (COSTS false)
46034617 ORDER BY t_1.c1;
46044618 LOG: pg_hint_plan:
46054619 used hint:
4620+IndexScan(t_1)
46064621 not used hint:
4607-HashJoin(t_1 t_1)
46084622 duplication hint:
46094623 error hint:
46104624
@@ -4630,19 +4644,18 @@ HashJoin(t_1 t_1)
46304644 DROP FUNCTION recall_planner_one_t(int);
46314645 ERROR: function recall_planner_one_t(integer) does not exist
46324646 --No.13-4-7
4647+-- See description for No.13-4-2. Complains on the wrongly wrtten hit.
46334648 /*+HashJoin(t_1 t_1)*/
46344649 EXPLAIN (COSTS false)
46354650 SELECT recall_planner() FROM s1.t1 t_1
46364651 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
46374652 ORDER BY t_1.c1;
4638-INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
4639-DETAIL: Relation name "t_1" is duplicated.
46404653 LOG: pg_hint_plan:
46414654 used hint:
4655+IndexScan(t_1)
46424656 not used hint:
46434657 duplication hint:
46444658 error hint:
4645-HashJoin(t_1 t_1)
46464659
46474660 INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
46484661 DETAIL: Relation name "t_1" is duplicated.
@@ -4671,14 +4684,11 @@ EXPLAIN (COSTS false)
46714684 ORDER BY t_1.c1;
46724685 INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)"
46734686 DETAIL: Conflict join method hint.
4674-INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)"
4675-DETAIL: Conflict join method hint.
46764687 LOG: pg_hint_plan:
46774688 used hint:
4678-HashJoin(t_1 t_2)
4689+IndexScan(t_1)
46794690 not used hint:
46804691 duplication hint:
4681-MergeJoin(t_1 t_2)
46824692 error hint:
46834693
46844694 LOG: pg_hint_plan:
@@ -4700,3 +4710,87 @@ error hint:
47004710 -> Seq Scan on t2 t_2
47014711 (7 rows)
47024712
4713+--No.14-1-1 plancache invalidation
4714+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
4715+CREATE INDEX ON s1.tpc(a);
4716+PREPARE p1 AS SELECT * FROM s1.tpc WHERE a < 999;
4717+/*+ IndexScan(tpc) */PREPARE p2 AS SELECT * FROM s1.tpc WHERE a < 999;
4718+/*+ SeqScan(tpc) */PREPARE p3(int) AS SELECT * FROM s1.tpc WHERE a = $1;
4719+EXPLAIN EXECUTE p1;
4720+ QUERY PLAN
4721+------------------------------------------------------
4722+ Seq Scan on tpc (cost=0.00..17.50 rows=333 width=4)
4723+ Filter: (a < 999)
4724+(2 rows)
4725+
4726+EXPLAIN EXECUTE p2;
4727+LOG: pg_hint_plan:
4728+used hint:
4729+IndexScan(tpc)
4730+not used hint:
4731+duplication hint:
4732+error hint:
4733+
4734+ QUERY PLAN
4735+------------------------------------------------------------------------
4736+ Index Scan using tpc_a_idx on tpc (cost=0.28..34.10 rows=333 width=4)
4737+ Index Cond: (a < 999)
4738+(2 rows)
4739+
4740+EXPLAIN EXECUTE p3(500);
4741+LOG: pg_hint_plan:
4742+used hint:
4743+SeqScan(tpc)
4744+not used hint:
4745+duplication hint:
4746+error hint:
4747+
4748+ QUERY PLAN
4749+----------------------------------------------------
4750+ Seq Scan on tpc (cost=0.00..17.50 rows=5 width=4)
4751+ Filter: (a = 500)
4752+(2 rows)
4753+
4754+-- The DROP invalidates the plan caches
4755+DROP TABLE s1.tpc;
4756+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
4757+CREATE INDEX ON s1.tpc(a);
4758+EXPLAIN EXECUTE p1;
4759+ QUERY PLAN
4760+------------------------------------------------------
4761+ Seq Scan on tpc (cost=0.00..17.50 rows=333 width=4)
4762+ Filter: (a < 999)
4763+(2 rows)
4764+
4765+EXPLAIN EXECUTE p2;
4766+LOG: pg_hint_plan:
4767+used hint:
4768+IndexScan(tpc)
4769+not used hint:
4770+duplication hint:
4771+error hint:
4772+
4773+ QUERY PLAN
4774+------------------------------------------------------------------------
4775+ Index Scan using tpc_a_idx on tpc (cost=0.28..34.10 rows=333 width=4)
4776+ Index Cond: (a < 999)
4777+(2 rows)
4778+
4779+EXPLAIN EXECUTE p3(500);
4780+LOG: pg_hint_plan:
4781+used hint:
4782+SeqScan(tpc)
4783+not used hint:
4784+duplication hint:
4785+error hint:
4786+
4787+ QUERY PLAN
4788+----------------------------------------------------
4789+ Seq Scan on tpc (cost=0.00..17.50 rows=5 width=4)
4790+ Filter: (a = 500)
4791+(2 rows)
4792+
4793+DEALLOCATE p1;
4794+DEALLOCATE p2;
4795+DEALLOCATE p3;
4796+DROP TABLE s1.tpc;
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -1820,7 +1820,7 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery)
18201820 * case of DESCRIBE message handling or EXECUTE command. We may still see a
18211821 * candidate top-level query in pstate in the case.
18221822 */
1823- if (!p && pstate)
1823+ if (pstate && pstate->p_sourcetext)
18241824 p = pstate->p_sourcetext;
18251825
18261826 /* We don't see a query string, return NULL */
@@ -1887,13 +1887,24 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery)
18871887 PreparedStatement *entry;
18881888
18891889 entry = FetchPreparedStatement(stmt->name, true);
1890- p = entry->plansource->query_string;
1891- target_query = (Query *) linitial (entry->plansource->query_list);
1890+
1891+ if (entry->plansource->is_valid)
1892+ {
1893+ p = entry->plansource->query_string;
1894+ target_query = (Query *) linitial (entry->plansource->query_list);
1895+ }
1896+ else
1897+ {
1898+ /* igonre the hint for EXECUTE if invalidated */
1899+ p = NULL;
1900+ target_query = NULL;
1901+ }
18921902 }
18931903
18941904 /* JumbleQuery accespts only a non-utility Query */
1895- if (!IsA(target_query, Query) ||
1896- target_query->utilityStmt != NULL)
1905+ if (target_query &&
1906+ (!IsA(target_query, Query) ||
1907+ target_query->utilityStmt != NULL))
18971908 target_query = NULL;
18981909
18991910 if (jumblequery)
@@ -2920,6 +2931,14 @@ get_current_hint_string(ParseState *pstate, Query *query)
29202931 current_hint_str = get_hints_from_comment(query_str);
29212932 MemoryContextSwitchTo(oldcontext);
29222933 }
2934+ else
2935+ {
2936+ /*
2937+ * Failed to get query. We would be in fetching invalidated
2938+ * plancache. Try the next chance.
2939+ */
2940+ current_hint_retrieved = false;
2941+ }
29232942
29242943 if (debug_level > 1)
29252944 {
@@ -2989,7 +3008,7 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
29893008 int save_nestlevel;
29903009 PlannedStmt *result;
29913010 HintState *hstate;
2992- const char *prev_hint_str;
3011+ const char *prev_hint_str = NULL;
29933012
29943013 /*
29953014 * Use standard planner if pg_hint_plan is disabled or current nesting
@@ -3089,6 +3108,7 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
30893108 */
30903109 recurse_level++;
30913110 prev_hint_str = current_hint_str;
3111+ current_hint_str = NULL;
30923112
30933113 /*
30943114 * Use PG_TRY mechanism to recover GUC parameters and current_hint_state to
--- a/sql/ut-A.sql
+++ b/sql/ut-A.sql
@@ -1159,6 +1159,11 @@ EXPLAIN (COSTS false)
11591159 ORDER BY t_1.c1;
11601160
11611161 --No.13-4-2
1162+-- recall_planner() is reduced to constant while planning using the
1163+-- hint defined in the function. Then the outer query is planned based
1164+-- on the following hint. pg_hint_plan shows the log for the function
1165+-- but the resulting explain output doesn't contain the corresponding
1166+-- plan.
11621167 /*+HashJoin(st_1 st_2)*/
11631168 EXPLAIN (COSTS false)
11641169 SELECT recall_planner() FROM s1.t1 st_1
@@ -1166,6 +1171,7 @@ EXPLAIN (COSTS false)
11661171 ORDER BY st_1.c1;
11671172
11681173 --No.13-4-3
1174+--See description for No.13-4-2
11691175 /*+HashJoin(t_1 t_2)*/
11701176 EXPLAIN (COSTS false)
11711177 SELECT recall_planner() FROM s1.t1 st_1
@@ -1173,6 +1179,7 @@ EXPLAIN (COSTS false)
11731179 ORDER BY st_1.c1;
11741180
11751181 --No.13-4-4
1182+--See description for No.13-4-2
11761183 /*+HashJoin(st_1 st_2)*/
11771184 EXPLAIN (COSTS false)
11781185 SELECT recall_planner() FROM s1.t1 t_1
@@ -1180,6 +1187,8 @@ EXPLAIN (COSTS false)
11801187 ORDER BY t_1.c1;
11811188
11821189 --No.13-4-5
1190+-- See description for No.13-4-2. No joins in ths plan, so
1191+-- pg_hint_plan doesn't complain on the wrongly written error hint.
11831192 /*+HashJoin(t_1 t_1)*/
11841193 EXPLAIN (COSTS false)
11851194 SELECT recall_planner() FROM s1.t1 t_1
@@ -1205,6 +1214,7 @@ EXPLAIN (COSTS false)
12051214 DROP FUNCTION recall_planner_one_t(int);
12061215
12071216 --No.13-4-7
1217+-- See description for No.13-4-2. Complains on the wrongly wrtten hit.
12081218 /*+HashJoin(t_1 t_1)*/
12091219 EXPLAIN (COSTS false)
12101220 SELECT recall_planner() FROM s1.t1 t_1
@@ -1217,3 +1227,24 @@ EXPLAIN (COSTS false)
12171227 SELECT recall_planner() FROM s1.t1 t_1
12181228 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
12191229 ORDER BY t_1.c1;
1230+
1231+--No.14-1-1 plancache invalidation
1232+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
1233+CREATE INDEX ON s1.tpc(a);
1234+PREPARE p1 AS SELECT * FROM s1.tpc WHERE a < 999;
1235+/*+ IndexScan(tpc) */PREPARE p2 AS SELECT * FROM s1.tpc WHERE a < 999;
1236+/*+ SeqScan(tpc) */PREPARE p3(int) AS SELECT * FROM s1.tpc WHERE a = $1;
1237+EXPLAIN EXECUTE p1;
1238+EXPLAIN EXECUTE p2;
1239+EXPLAIN EXECUTE p3(500);
1240+-- The DROP invalidates the plan caches
1241+DROP TABLE s1.tpc;
1242+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
1243+CREATE INDEX ON s1.tpc(a);
1244+EXPLAIN EXECUTE p1;
1245+EXPLAIN EXECUTE p2;
1246+EXPLAIN EXECUTE p3(500);
1247+DEALLOCATE p1;
1248+DEALLOCATE p2;
1249+DEALLOCATE p3;
1250+DROP TABLE s1.tpc;
Show on old repository browser