• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revision2c20820a666f9d78ea0dc1e20bb827ed6537a862 (tree)
Time2020-02-14 15:36:04
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
@@ -4346,6 +4346,11 @@ CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$
43464346 ORDER BY t_1.c1 LIMIT 1;
43474347 $$ LANGUAGE SQL IMMUTABLE;
43484348 --No.13-4-1
4349+-- recall_planner() is reduced to constant while planning using the
4350+-- hint defined in the function. Then the outer query is planned based
4351+-- on the following hint. pg_hint_plan shows the log for the function
4352+-- but the resulting explain output doesn't contain the corresponding
4353+-- plan.
43494354 /*+HashJoin(t_1 t_2)*/
43504355 EXPLAIN (COSTS false)
43514356 SELECT recall_planner() FROM s1.t1 t_1
@@ -4353,7 +4358,7 @@ EXPLAIN (COSTS false)
43534358 ORDER BY t_1.c1;
43544359 LOG: pg_hint_plan:
43554360 used hint:
4356-HashJoin(t_1 t_2)
4361+IndexScan(t_1)
43574362 not used hint:
43584363 duplication hint:
43594364 error hint:
@@ -4377,6 +4382,7 @@ error hint:
43774382 (7 rows)
43784383
43794384 --No.13-4-2
4385+--See description for No.13-4-1
43804386 /*+HashJoin(st_1 st_2)*/
43814387 EXPLAIN (COSTS false)
43824388 SELECT recall_planner() FROM s1.t1 st_1
@@ -4384,8 +4390,8 @@ EXPLAIN (COSTS false)
43844390 ORDER BY st_1.c1;
43854391 LOG: pg_hint_plan:
43864392 used hint:
4393+IndexScan(t_1)
43874394 not used hint:
4388-HashJoin(st_1 st_2)
43894395 duplication hint:
43904396 error hint:
43914397
@@ -4408,6 +4414,7 @@ error hint:
44084414 (7 rows)
44094415
44104416 --No.13-4-3
4417+--See description for No.13-4-1
44114418 /*+HashJoin(t_1 t_2)*/
44124419 EXPLAIN (COSTS false)
44134420 SELECT recall_planner() FROM s1.t1 st_1
@@ -4415,7 +4422,7 @@ EXPLAIN (COSTS false)
44154422 ORDER BY st_1.c1;
44164423 LOG: pg_hint_plan:
44174424 used hint:
4418-HashJoin(t_1 t_2)
4425+IndexScan(t_1)
44194426 not used hint:
44204427 duplication hint:
44214428 error hint:
@@ -4438,6 +4445,7 @@ error hint:
44384445 (6 rows)
44394446
44404447 --No.13-4-4
4448+--See description for No.13-4-1
44414449 /*+HashJoin(st_1 st_2)*/
44424450 EXPLAIN (COSTS false)
44434451 SELECT recall_planner() FROM s1.t1 t_1
@@ -4445,8 +4453,8 @@ EXPLAIN (COSTS false)
44454453 ORDER BY t_1.c1;
44464454 LOG: pg_hint_plan:
44474455 used hint:
4456+IndexScan(t_1)
44484457 not used hint:
4449-HashJoin(st_1 st_2)
44504458 duplication hint:
44514459 error hint:
44524460
@@ -4468,18 +4476,18 @@ error hint:
44684476 (6 rows)
44694477
44704478 --No.13-4-5
4479+-- See description for No.13-4-1. No joins in ths plan, so
4480+-- pg_hint_plan doesn't complain on the wrongly written error hint.
44714481 /*+HashJoin(t_1 t_1)*/
44724482 EXPLAIN (COSTS false)
44734483 SELECT recall_planner() FROM s1.t1 t_1
44744484 ORDER BY t_1.c1;
4475-INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
4476-DETAIL: Relation name "t_1" is duplicated.
44774485 LOG: pg_hint_plan:
44784486 used hint:
4487+IndexScan(t_1)
44794488 not used hint:
44804489 duplication hint:
44814490 error hint:
4482-HashJoin(t_1 t_1)
44834491
44844492 LOG: pg_hint_plan:
44854493 used hint:
@@ -4503,6 +4511,13 @@ EXPLAIN (COSTS false)
45034511 SELECT recall_planner_one_t() FROM s1.t1 t_1
45044512 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
45054513 ORDER BY t_1.c1;
4514+LOG: pg_hint_plan:
4515+used hint:
4516+IndexScan(t_1)
4517+not used hint:
4518+duplication hint:
4519+error hint:
4520+
45064521 QUERY PLAN
45074522 ---------------------------------------------
45084523 Merge Join
@@ -4520,8 +4535,8 @@ EXPLAIN (COSTS false)
45204535 ORDER BY t_1.c1;
45214536 LOG: pg_hint_plan:
45224537 used hint:
4538+IndexScan(t_1)
45234539 not used hint:
4524-HashJoin(t_1 t_1)
45254540 duplication hint:
45264541 error hint:
45274542
@@ -4547,19 +4562,18 @@ HashJoin(t_1 t_1)
45474562 DROP FUNCTION recall_planner_one_t(int);
45484563 ERROR: function recall_planner_one_t(integer) does not exist
45494564 --No.13-4-7
4565+-- See description for No.13-4-1. Complains on the wrongly written hint.
45504566 /*+HashJoin(t_1 t_1)*/
45514567 EXPLAIN (COSTS false)
45524568 SELECT recall_planner() FROM s1.t1 t_1
45534569 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
45544570 ORDER BY t_1.c1;
4555-INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
4556-DETAIL: Relation name "t_1" is duplicated.
45574571 LOG: pg_hint_plan:
45584572 used hint:
4573+IndexScan(t_1)
45594574 not used hint:
45604575 duplication hint:
45614576 error hint:
4562-HashJoin(t_1 t_1)
45634577
45644578 INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
45654579 DETAIL: Relation name "t_1" is duplicated.
@@ -4588,14 +4602,11 @@ EXPLAIN (COSTS false)
45884602 ORDER BY t_1.c1;
45894603 INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)"
45904604 DETAIL: Conflict join method hint.
4591-INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)"
4592-DETAIL: Conflict join method hint.
45934605 LOG: pg_hint_plan:
45944606 used hint:
4595-HashJoin(t_1 t_2)
4607+IndexScan(t_1)
45964608 not used hint:
45974609 duplication hint:
4598-MergeJoin(t_1 t_2)
45994610 error hint:
46004611
46014612 LOG: pg_hint_plan:
@@ -4617,3 +4628,93 @@ error hint:
46174628 -> Seq Scan on t2 t_2
46184629 (7 rows)
46194630
4631+--No.14-1-1 plancache invalidation
4632+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
4633+CREATE INDEX ON s1.tpc(a);
4634+PREPARE p1 AS SELECT * FROM s1.tpc WHERE a < 999;
4635+/*+ IndexScan(tpc) */PREPARE p2 AS SELECT * FROM s1.tpc WHERE a < 999;
4636+/*+ SeqScan(tpc) */PREPARE p3(int) AS SELECT * FROM s1.tpc WHERE a = $1;
4637+EXPLAIN EXECUTE p1;
4638+ QUERY PLAN
4639+------------------------------------------------------
4640+ Seq Scan on tpc (cost=0.00..17.50 rows=333 width=4)
4641+ Filter: (a < 999)
4642+(2 rows)
4643+
4644+EXPLAIN EXECUTE p2;
4645+LOG: pg_hint_plan:
4646+used hint:
4647+IndexScan(tpc)
4648+not used hint:
4649+duplication hint:
4650+error hint:
4651+
4652+ QUERY PLAN
4653+------------------------------------------------------------------------
4654+ Index Scan using tpc_a_idx on tpc (cost=0.28..34.10 rows=333 width=4)
4655+ Index Cond: (a < 999)
4656+(2 rows)
4657+
4658+EXPLAIN EXECUTE p3(500);
4659+LOG: pg_hint_plan:
4660+used hint:
4661+SeqScan(tpc)
4662+not used hint:
4663+duplication hint:
4664+error hint:
4665+
4666+ QUERY PLAN
4667+----------------------------------------------------
4668+ Seq Scan on tpc (cost=0.00..17.50 rows=5 width=4)
4669+ Filter: (a = 500)
4670+(2 rows)
4671+
4672+-- The DROP invalidates the plan caches
4673+DROP TABLE s1.tpc;
4674+EXPLAIN EXECUTE p1;
4675+ERROR: relation "s1.tpc" does not exist
4676+EXPLAIN EXECUTE p2;
4677+ERROR: relation "s1.tpc" does not exist
4678+EXPLAIN EXECUTE p3(500);
4679+ERROR: relation "s1.tpc" does not exist
4680+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
4681+CREATE INDEX ON s1.tpc(a);
4682+EXPLAIN EXECUTE p1;
4683+ QUERY PLAN
4684+------------------------------------------------------
4685+ Seq Scan on tpc (cost=0.00..17.50 rows=333 width=4)
4686+ Filter: (a < 999)
4687+(2 rows)
4688+
4689+EXPLAIN EXECUTE p2;
4690+LOG: pg_hint_plan:
4691+used hint:
4692+IndexScan(tpc)
4693+not used hint:
4694+duplication hint:
4695+error hint:
4696+
4697+ QUERY PLAN
4698+------------------------------------------------------------------------
4699+ Index Scan using tpc_a_idx on tpc (cost=0.28..34.10 rows=333 width=4)
4700+ Index Cond: (a < 999)
4701+(2 rows)
4702+
4703+EXPLAIN EXECUTE p3(500);
4704+LOG: pg_hint_plan:
4705+used hint:
4706+SeqScan(tpc)
4707+not used hint:
4708+duplication hint:
4709+error hint:
4710+
4711+ QUERY PLAN
4712+----------------------------------------------------
4713+ Seq Scan on tpc (cost=0.00..17.50 rows=5 width=4)
4714+ Filter: (a = 500)
4715+(2 rows)
4716+
4717+DEALLOCATE p1;
4718+DEALLOCATE p2;
4719+DEALLOCATE p3;
4720+DROP TABLE s1.tpc;
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -1816,7 +1816,7 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery)
18161816 * case of DESCRIBE message handling or EXECUTE command. We may still see a
18171817 * candidate top-level query in pstate in the case.
18181818 */
1819- if (!p && pstate)
1819+ if (pstate && pstate->p_sourcetext)
18201820 p = pstate->p_sourcetext;
18211821
18221822 /* We don't see a query string, return NULL */
@@ -1893,13 +1893,24 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery)
18931893 PreparedStatement *entry;
18941894
18951895 entry = FetchPreparedStatement(stmt->name, true);
1896- p = entry->plansource->query_string;
1897- target_query = (Query *) linitial (entry->plansource->query_list);
1896+
1897+ if (entry->plansource->is_valid)
1898+ {
1899+ p = entry->plansource->query_string;
1900+ target_query = (Query *) linitial (entry->plansource->query_list);
1901+ }
1902+ else
1903+ {
1904+ /* igonre the hint for EXECUTE if invalidated */
1905+ p = NULL;
1906+ target_query = NULL;
1907+ }
18981908 }
18991909
19001910 /* JumbleQuery accespts only a non-utility Query */
1901- if (!IsA(target_query, Query) ||
1902- target_query->utilityStmt != NULL)
1911+ if (target_query &&
1912+ (!IsA(target_query, Query) ||
1913+ target_query->utilityStmt != NULL))
19031914 target_query = NULL;
19041915
19051916 if (jumblequery)
@@ -2919,6 +2930,14 @@ get_current_hint_string(ParseState *pstate, Query *query)
29192930 current_hint_str = get_hints_from_comment(query_str);
29202931 MemoryContextSwitchTo(oldcontext);
29212932 }
2933+ else
2934+ {
2935+ /*
2936+ * Failed to get query. We would be in fetching invalidated
2937+ * plancache. Try the next chance.
2938+ */
2939+ current_hint_retrieved = false;
2940+ }
29222941
29232942 if (debug_level > 1)
29242943 {
@@ -2987,7 +3006,7 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
29873006 int save_nestlevel;
29883007 PlannedStmt *result;
29893008 HintState *hstate;
2990- const char *prev_hint_str;
3009+ const char *prev_hint_str = NULL;
29913010
29923011 /*
29933012 * Use standard planner if pg_hint_plan is disabled or current nesting
@@ -3084,6 +3103,7 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
30843103 */
30853104 recurse_level++;
30863105 prev_hint_str = current_hint_str;
3106+ current_hint_str = NULL;
30873107
30883108 /*
30893109 * Use PG_TRY mechanism to recover GUC parameters and current_hint_state to
--- a/sql/ut-A.sql
+++ b/sql/ut-A.sql
@@ -1152,6 +1152,11 @@ CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$
11521152 $$ LANGUAGE SQL IMMUTABLE;
11531153
11541154 --No.13-4-1
1155+-- recall_planner() is reduced to constant while planning using the
1156+-- hint defined in the function. Then the outer query is planned based
1157+-- on the following hint. pg_hint_plan shows the log for the function
1158+-- but the resulting explain output doesn't contain the corresponding
1159+-- plan.
11551160 /*+HashJoin(t_1 t_2)*/
11561161 EXPLAIN (COSTS false)
11571162 SELECT recall_planner() FROM s1.t1 t_1
@@ -1159,6 +1164,7 @@ EXPLAIN (COSTS false)
11591164 ORDER BY t_1.c1;
11601165
11611166 --No.13-4-2
1167+--See description for No.13-4-1
11621168 /*+HashJoin(st_1 st_2)*/
11631169 EXPLAIN (COSTS false)
11641170 SELECT recall_planner() FROM s1.t1 st_1
@@ -1166,6 +1172,7 @@ EXPLAIN (COSTS false)
11661172 ORDER BY st_1.c1;
11671173
11681174 --No.13-4-3
1175+--See description for No.13-4-1
11691176 /*+HashJoin(t_1 t_2)*/
11701177 EXPLAIN (COSTS false)
11711178 SELECT recall_planner() FROM s1.t1 st_1
@@ -1173,6 +1180,7 @@ EXPLAIN (COSTS false)
11731180 ORDER BY st_1.c1;
11741181
11751182 --No.13-4-4
1183+--See description for No.13-4-1
11761184 /*+HashJoin(st_1 st_2)*/
11771185 EXPLAIN (COSTS false)
11781186 SELECT recall_planner() FROM s1.t1 t_1
@@ -1180,6 +1188,8 @@ EXPLAIN (COSTS false)
11801188 ORDER BY t_1.c1;
11811189
11821190 --No.13-4-5
1191+-- See description for No.13-4-1. No joins in ths plan, so
1192+-- pg_hint_plan doesn't complain on the wrongly written error hint.
11831193 /*+HashJoin(t_1 t_1)*/
11841194 EXPLAIN (COSTS false)
11851195 SELECT recall_planner() FROM s1.t1 t_1
@@ -1205,6 +1215,7 @@ EXPLAIN (COSTS false)
12051215 DROP FUNCTION recall_planner_one_t(int);
12061216
12071217 --No.13-4-7
1218+-- See description for No.13-4-1. Complains on the wrongly written hint.
12081219 /*+HashJoin(t_1 t_1)*/
12091220 EXPLAIN (COSTS false)
12101221 SELECT recall_planner() FROM s1.t1 t_1
@@ -1217,3 +1228,27 @@ EXPLAIN (COSTS false)
12171228 SELECT recall_planner() FROM s1.t1 t_1
12181229 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
12191230 ORDER BY t_1.c1;
1231+
1232+--No.14-1-1 plancache invalidation
1233+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
1234+CREATE INDEX ON s1.tpc(a);
1235+PREPARE p1 AS SELECT * FROM s1.tpc WHERE a < 999;
1236+/*+ IndexScan(tpc) */PREPARE p2 AS SELECT * FROM s1.tpc WHERE a < 999;
1237+/*+ SeqScan(tpc) */PREPARE p3(int) AS SELECT * FROM s1.tpc WHERE a = $1;
1238+EXPLAIN EXECUTE p1;
1239+EXPLAIN EXECUTE p2;
1240+EXPLAIN EXECUTE p3(500);
1241+-- The DROP invalidates the plan caches
1242+DROP TABLE s1.tpc;
1243+EXPLAIN EXECUTE p1;
1244+EXPLAIN EXECUTE p2;
1245+EXPLAIN EXECUTE p3(500);
1246+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
1247+CREATE INDEX ON s1.tpc(a);
1248+EXPLAIN EXECUTE p1;
1249+EXPLAIN EXECUTE p2;
1250+EXPLAIN EXECUTE p3(500);
1251+DEALLOCATE p1;
1252+DEALLOCATE p2;
1253+DEALLOCATE p3;
1254+DROP TABLE s1.tpc;
Show on old repository browser