• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revisionca7d497e3ebddf9c2a4837cc05baa501136fc364 (tree)
Time2020-02-14 16:31:26
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
@@ -4571,3 +4571,93 @@ error hint:
45714571 -> Seq Scan on t2 t_2
45724572 (7 rows)
45734573
4574+--No.14-1-1 plancache invalidation
4575+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
4576+CREATE INDEX ON s1.tpc(a);
4577+PREPARE p1 AS SELECT * FROM s1.tpc WHERE a < 999;
4578+/*+ IndexScan(tpc) */PREPARE p2 AS SELECT * FROM s1.tpc WHERE a < 999;
4579+/*+ SeqScan(tpc) */PREPARE p3(int) AS SELECT * FROM s1.tpc WHERE a = $1;
4580+EXPLAIN EXECUTE p1;
4581+ QUERY PLAN
4582+------------------------------------------------------
4583+ Seq Scan on tpc (cost=0.00..17.50 rows=333 width=4)
4584+ Filter: (a < 999)
4585+(2 rows)
4586+
4587+EXPLAIN EXECUTE p2;
4588+LOG: pg_hint_plan:
4589+used hint:
4590+IndexScan(tpc)
4591+not used hint:
4592+duplication hint:
4593+error hint:
4594+
4595+ QUERY PLAN
4596+------------------------------------------------------------------------
4597+ Index Scan using tpc_a_idx on tpc (cost=0.28..34.10 rows=333 width=4)
4598+ Index Cond: (a < 999)
4599+(2 rows)
4600+
4601+EXPLAIN EXECUTE p3(500);
4602+LOG: pg_hint_plan:
4603+used hint:
4604+SeqScan(tpc)
4605+not used hint:
4606+duplication hint:
4607+error hint:
4608+
4609+ QUERY PLAN
4610+----------------------------------------------------
4611+ Seq Scan on tpc (cost=0.00..17.50 rows=5 width=4)
4612+ Filter: (a = 500)
4613+(2 rows)
4614+
4615+-- The DROP invalidates the plan caches
4616+DROP TABLE s1.tpc;
4617+EXPLAIN EXECUTE p1;
4618+ERROR: relation "s1.tpc" does not exist
4619+EXPLAIN EXECUTE p2;
4620+ERROR: relation "s1.tpc" does not exist
4621+EXPLAIN EXECUTE p3(500);
4622+ERROR: relation "s1.tpc" does not exist
4623+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
4624+CREATE INDEX ON s1.tpc(a);
4625+EXPLAIN EXECUTE p1;
4626+ QUERY PLAN
4627+------------------------------------------------------
4628+ Seq Scan on tpc (cost=0.00..17.50 rows=333 width=4)
4629+ Filter: (a < 999)
4630+(2 rows)
4631+
4632+EXPLAIN EXECUTE p2;
4633+LOG: pg_hint_plan:
4634+used hint:
4635+IndexScan(tpc)
4636+not used hint:
4637+duplication hint:
4638+error hint:
4639+
4640+ QUERY PLAN
4641+------------------------------------------------------------------------
4642+ Index Scan using tpc_a_idx on tpc (cost=0.28..34.10 rows=333 width=4)
4643+ Index Cond: (a < 999)
4644+(2 rows)
4645+
4646+EXPLAIN EXECUTE p3(500);
4647+LOG: pg_hint_plan:
4648+used hint:
4649+SeqScan(tpc)
4650+not used hint:
4651+duplication hint:
4652+error hint:
4653+
4654+ QUERY PLAN
4655+----------------------------------------------------
4656+ Seq Scan on tpc (cost=0.00..17.50 rows=5 width=4)
4657+ Filter: (a = 500)
4658+(2 rows)
4659+
4660+DEALLOCATE p1;
4661+DEALLOCATE p2;
4662+DEALLOCATE p3;
4663+DROP TABLE s1.tpc;
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -1892,13 +1892,24 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery)
18921892 PreparedStatement *entry;
18931893
18941894 entry = FetchPreparedStatement(stmt->name, true);
1895- p = entry->plansource->query_string;
1896- target_query = (Query *) linitial (entry->plansource->query_list);
1895+
1896+ if (entry->plansource->is_valid)
1897+ {
1898+ p = entry->plansource->query_string;
1899+ target_query = (Query *) linitial (entry->plansource->query_list);
1900+ }
1901+ else
1902+ {
1903+ /* igonre the hint for EXECUTE if invalidated */
1904+ p = NULL;
1905+ target_query = NULL;
1906+ }
18971907 }
18981908
18991909 /* JumbleQuery accespts only a non-utility Query */
1900- if (!IsA(target_query, Query) ||
1901- target_query->utilityStmt != NULL)
1910+ if (target_query &&
1911+ (!IsA(target_query, Query) ||
1912+ target_query->utilityStmt != NULL))
19021913 target_query = NULL;
19031914
19041915 if (jumblequery)
@@ -2925,6 +2936,14 @@ get_current_hint_string(ParseState *pstate, Query *query)
29252936 current_hint_str = get_hints_from_comment(query_str);
29262937 MemoryContextSwitchTo(oldcontext);
29272938 }
2939+ else
2940+ {
2941+ /*
2942+ * Failed to get query. We would be in fetching invalidated
2943+ * plancache. Try the next chance.
2944+ */
2945+ current_hint_retrieved = false;
2946+ }
29282947
29292948 if (debug_level > 1)
29302949 {
@@ -2994,7 +3013,7 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
29943013 int save_nestlevel;
29953014 PlannedStmt *result;
29963015 HintState *hstate;
2997- const char *prev_hint_str;
3016+ const char *prev_hint_str = NULL;
29983017
29993018 /*
30003019 * Use standard planner if pg_hint_plan is disabled or current nesting
--- a/sql/ut-A.sql
+++ b/sql/ut-A.sql
@@ -1228,3 +1228,27 @@ EXPLAIN (COSTS false)
12281228 SELECT recall_planner() FROM s1.t1 t_1
12291229 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
12301230 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