• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: コミット

firtst release


コミットメタ情報

リビジョン6f424c5ad98ef6575df0e07098d0107b15ec64d9 (tree)
日時2017-07-27 19:19:10
作者Kyotaro Horiguchi <horiguchi.kyotaro@lab....>
コミッターKyotaro Horiguchi

ログメッセージ

Fixed a crash bug by DECLARE CURSOR and enable_hint_table = on

The previous version causes assertion failure by DECLARE CURSOR syntax
when table hint is activated. The cause is that the version forgot the
fact that DelcareCursorStmt is in a bit strange shape. Add support of
DECLARE CURSOR and regression test for table hinting.

変更サマリ

差分

--- a/Makefile
+++ b/Makefile
@@ -7,7 +7,7 @@
77 MODULES = pg_hint_plan
88 HINTPLANVER = 1.1.4
99
10-REGRESS = init base_plan pg_hint_plan ut-init ut-A ut-S ut-J ut-L ut-G ut-R ut-fdw ut-fini
10+REGRESS = init base_plan pg_hint_plan ut-init ut-A ut-S ut-J ut-L ut-G ut-R ut-fdw ut-T ut-fini
1111
1212 REGRESSION_EXPECTED = expected/init.out expected/base_plan.out expected/pg_hint_plan.out expected/ut-A.out expected/ut-S.out expected/ut-J.out expected/ut-L.out expected/ut-G.out
1313
--- /dev/null
+++ b/expected/ut-T.out
@@ -0,0 +1,131 @@
1+-- ut-T: tests for table hints
2+-- This test is focusing on hint retrieval from table
3+LOAD 'pg_hint_plan';
4+SET pg_hint_plan.enable_hint TO on;
5+SET pg_hint_plan.debug_print TO on;
6+SET client_min_messages TO LOG;
7+SET search_path TO public;
8+-- test for get_query_string
9+INSERT INTO hint_plan.hints VALUES(DEFAULT,'EXPLAIN SELECT * FROM t1 WHERE id = ?;', '', 'SeqScan(t1)');
10+INSERT INTO hint_plan.hints VALUES(DEFAULT,'PREPARE p1 AS SELECT * FROM t1 WHERE id = ?;', '', 'SeqScan(t1)');
11+INSERT INTO hint_plan.hints VALUES(DEFAULT,'EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1 WHERE id = ?;', '', 'SeqScan(t1)');
12+INSERT INTO hint_plan.hints VALUES(DEFAULT,'EXPLAIN CREATE TABLE ct1 AS SELECT * FROM t1 WHERE id = ?;', '', 'SeqScan(t1)');
13+PREPARE p1 AS SELECT * FROM t1 WHERE id = 100;
14+-- These queries uses IndexScan without hints
15+SET pg_hint_plan.enable_hint_table to off;
16+EXPLAIN SELECT * FROM t1 WHERE id = 100;
17+ QUERY PLAN
18+------------------------------------------------------------------
19+ Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=8)
20+ Index Cond: (id = 100)
21+(2 rows)
22+
23+EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1 WHERE id = 100;
24+ QUERY PLAN
25+------------------------------------------------------------------
26+ Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=8)
27+ Index Cond: (id = 100)
28+(2 rows)
29+
30+EXPLAIN CREATE TABLE ct1 AS SELECT * FROM t1 WHERE id = 100;
31+ QUERY PLAN
32+------------------------------------------------------------------
33+ Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=8)
34+ Index Cond: (id = 100)
35+(2 rows)
36+
37+EXPLAIN EXECUTE p1;
38+ QUERY PLAN
39+------------------------------------------------------------------
40+ Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=8)
41+ Index Cond: (id = 100)
42+(2 rows)
43+
44+DEALLOCATE p1;
45+PREPARE p1 AS SELECT * FROM t1 WHERE id = 100;
46+EXPLAIN CREATE TABLE ct1 AS EXECUTE p1;
47+ QUERY PLAN
48+------------------------------------------------------------------
49+ Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=8)
50+ Index Cond: (id = 100)
51+(2 rows)
52+
53+DEALLOCATE p1;
54+PREPARE p1 AS SELECT * FROM t1 WHERE id = 100;
55+-- Forced to use SeqScan by table hints
56+SET pg_hint_plan.enable_hint_table to on;
57+EXPLAIN SELECT * FROM t1 WHERE id = 100;
58+LOG: pg_hint_plan:
59+used hint:
60+SeqScan(t1)
61+not used hint:
62+duplication hint:
63+error hint:
64+
65+ QUERY PLAN
66+----------------------------------------------------
67+ Seq Scan on t1 (cost=0.00..170.00 rows=1 width=8)
68+ Filter: (id = 100)
69+(2 rows)
70+
71+EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1 WHERE id = 100;
72+LOG: pg_hint_plan:
73+used hint:
74+SeqScan(t1)
75+not used hint:
76+duplication hint:
77+error hint:
78+
79+ QUERY PLAN
80+----------------------------------------------------
81+ Seq Scan on t1 (cost=0.00..170.00 rows=1 width=8)
82+ Filter: (id = 100)
83+(2 rows)
84+
85+EXPLAIN CREATE TABLE ct1 AS SELECT * FROM t1 WHERE id = 100;
86+LOG: pg_hint_plan:
87+used hint:
88+SeqScan(t1)
89+not used hint:
90+duplication hint:
91+error hint:
92+
93+ QUERY PLAN
94+----------------------------------------------------
95+ Seq Scan on t1 (cost=0.00..170.00 rows=1 width=8)
96+ Filter: (id = 100)
97+(2 rows)
98+
99+EXPLAIN EXECUTE p1;
100+LOG: pg_hint_plan:
101+used hint:
102+SeqScan(t1)
103+not used hint:
104+duplication hint:
105+error hint:
106+
107+ QUERY PLAN
108+----------------------------------------------------
109+ Seq Scan on t1 (cost=0.00..170.00 rows=1 width=8)
110+ Filter: (id = 100)
111+(2 rows)
112+
113+DEALLOCATE p1;
114+PREPARE p1 AS SELECT * FROM t1 WHERE id = 100;
115+EXPLAIN CREATE TABLE ct1 AS EXECUTE p1;
116+LOG: pg_hint_plan:
117+used hint:
118+SeqScan(t1)
119+not used hint:
120+duplication hint:
121+error hint:
122+
123+ QUERY PLAN
124+----------------------------------------------------
125+ Seq Scan on t1 (cost=0.00..170.00 rows=1 width=8)
126+ Filter: (id = 100)
127+(2 rows)
128+
129+DEALLOCATE p1;
130+SET pg_hint_plan.enable_hint_table to off;
131+DELETE FROM hint_plan.hints;
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -1647,7 +1647,11 @@ get_hints_from_table(const char *client_query, const char *client_application)
16471647 }
16481648
16491649 /*
1650- * Get client-supplied query string.
1650+ * Get client-supplied query string. Addtion to that the jumbled query is
1651+ * supplied if the caller requested. From the restriction of JumbleQuery, some
1652+ * kind of query needs special amendments. Reutrns NULL if this query doesn't
1653+ * change the current hint. This function returns NULL also when something
1654+ * wrong has happend and let the caller continue using the current hints.
16511655 */
16521656 static const char *
16531657 get_query_string(ParseState *pstate, Query *query, Query **jumblequery)
@@ -1657,15 +1661,22 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery)
16571661 if (jumblequery != NULL)
16581662 *jumblequery = query;
16591663
1660- if (query->commandType == CMD_UTILITY)
1664+ /* Query for DeclareCursorStmt is CMD_SELECT and has query->utilityStmt */
1665+ if (query->commandType == CMD_UTILITY || query->utilityStmt)
16611666 {
16621667 Query *target_query = query;
16631668
1664- /* Use the target query if EXPLAIN */
1665- if (IsA(query->utilityStmt, ExplainStmt))
1669+ /*
1670+ * Some utility statements have a subquery that we can hint on. Since
1671+ * EXPLAIN can be placed before other kind of utility statements and
1672+ * EXECUTE can be contained other kind of utility statements, these
1673+ * conditions are not mutually exclusive and should be considered in
1674+ * this order.
1675+ */
1676+ if (IsA(target_query->utilityStmt, ExplainStmt))
16661677 {
1667- ExplainStmt *stmt = (ExplainStmt *)(query->utilityStmt);
1668-
1678+ ExplainStmt *stmt = (ExplainStmt *)target_query->utilityStmt;
1679+
16691680 Assert(IsA(stmt->query, Query));
16701681 target_query = (Query *)stmt->query;
16711682
@@ -1675,34 +1686,43 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery)
16751686 target_query = (Query *)target_query->utilityStmt;
16761687 }
16771688
1678- if (IsA(target_query, CreateTableAsStmt))
1689+ /*
1690+ * JumbleQuery does not accept a Query that has utilityStmt. On the
1691+ * other hand DeclareCursorStmt is in a bit strange shape that is
1692+ * flipped upside down.
1693+ */
1694+ if (IsA(target_query, Query) &&
1695+ target_query->utilityStmt &&
1696+ IsA(target_query->utilityStmt, DeclareCursorStmt))
16791697 {
16801698 /*
1681- * Use the the body query for CREATE AS. The Query for jumble also
1682- * replaced with the corresponding one.
1699+ * The given Query cannot be modified so copy it and modify so that
1700+ * JumbleQuery can accept it.
16831701 */
1702+ Assert(IsA(target_query, Query) &&
1703+ target_query->commandType == CMD_SELECT);
1704+ target_query = copyObject(target_query);
1705+ target_query->utilityStmt = NULL;
1706+ }
1707+
1708+ if (IsA(target_query, CreateTableAsStmt))
1709+ {
16841710 CreateTableAsStmt *stmt = (CreateTableAsStmt *) target_query;
1685- PreparedStatement *entry;
1686- Query *tmp_query;
16871711
16881712 Assert(IsA(stmt->query, Query));
1689- tmp_query = (Query *) stmt->query;
1713+ target_query = (Query *) stmt->query;
16901714
1691- if (tmp_query->commandType == CMD_UTILITY &&
1692- IsA(tmp_query->utilityStmt, ExecuteStmt))
1693- {
1694- ExecuteStmt *estmt = (ExecuteStmt *) tmp_query->utilityStmt;
1695- entry = FetchPreparedStatement(estmt->name, true);
1696- p = entry->plansource->query_string;
1697- target_query = (Query *) linitial (entry->plansource->query_list);
1698- }
1715+ /* strip out the top-level query for further processing */
1716+ if (target_query->commandType == CMD_UTILITY &&
1717+ target_query->utilityStmt != NULL)
1718+ target_query = (Query *)target_query->utilityStmt;
16991719 }
1700- else
1720+
17011721 if (IsA(target_query, ExecuteStmt))
17021722 {
17031723 /*
1704- * Use the prepared query for EXECUTE. The Query for jumble also
1705- * replaced with the corresponding one.
1724+ * Use the prepared query for EXECUTE. The Query for jumble
1725+ * also replaced with the corresponding one.
17061726 */
17071727 ExecuteStmt *stmt = (ExecuteStmt *)target_query;
17081728 PreparedStatement *entry;
@@ -1711,15 +1731,16 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery)
17111731 p = entry->plansource->query_string;
17121732 target_query = (Query *) linitial (entry->plansource->query_list);
17131733 }
1714-
1715- /* We don't accept other than a Query other than a CMD_UTILITY */
1734+
1735+ /* JumbleQuery accespts only a non-utility Query */
17161736 if (!IsA(target_query, Query) ||
1717- target_query->commandType == CMD_UTILITY)
1737+ target_query->utilityStmt != NULL)
17181738 target_query = NULL;
17191739
17201740 if (jumblequery)
17211741 *jumblequery = target_query;
17221742 }
1743+
17231744 /* Return NULL if the pstate is not identical to the top-level query */
17241745 else if (strcmp(pstate->p_sourcetext, p) != 0)
17251746 p = NULL;
@@ -2525,7 +2546,7 @@ pg_hint_plan_post_parse_analyze(ParseState *pstate, Query *query)
25252546 }
25262547 }
25272548
2528- /* retrun if we have hint here*/
2549+ /* retrun if we have hint here */
25292550 if (current_hint_str)
25302551 return;
25312552 }
--- /dev/null
+++ b/sql/ut-T.sql
@@ -0,0 +1,45 @@
1+-- ut-T: tests for table hints
2+-- This test is focusing on hint retrieval from table
3+
4+LOAD 'pg_hint_plan';
5+SET pg_hint_plan.enable_hint TO on;
6+SET pg_hint_plan.debug_print TO on;
7+SET client_min_messages TO LOG;
8+SET search_path TO public;
9+
10+-- test for get_query_string
11+INSERT INTO hint_plan.hints VALUES(DEFAULT,'EXPLAIN SELECT * FROM t1 WHERE id = ?;', '', 'SeqScan(t1)');
12+INSERT INTO hint_plan.hints VALUES(DEFAULT,'PREPARE p1 AS SELECT * FROM t1 WHERE id = ?;', '', 'SeqScan(t1)');
13+INSERT INTO hint_plan.hints VALUES(DEFAULT,'EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1 WHERE id = ?;', '', 'SeqScan(t1)');
14+INSERT INTO hint_plan.hints VALUES(DEFAULT,'EXPLAIN CREATE TABLE ct1 AS SELECT * FROM t1 WHERE id = ?;', '', 'SeqScan(t1)');
15+
16+PREPARE p1 AS SELECT * FROM t1 WHERE id = 100;
17+
18+-- These queries uses IndexScan without hints
19+SET pg_hint_plan.enable_hint_table to off;
20+EXPLAIN SELECT * FROM t1 WHERE id = 100;
21+EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1 WHERE id = 100;
22+EXPLAIN CREATE TABLE ct1 AS SELECT * FROM t1 WHERE id = 100;
23+
24+EXPLAIN EXECUTE p1;
25+DEALLOCATE p1;
26+PREPARE p1 AS SELECT * FROM t1 WHERE id = 100;
27+EXPLAIN CREATE TABLE ct1 AS EXECUTE p1;
28+
29+DEALLOCATE p1;
30+PREPARE p1 AS SELECT * FROM t1 WHERE id = 100;
31+
32+-- Forced to use SeqScan by table hints
33+SET pg_hint_plan.enable_hint_table to on;
34+EXPLAIN SELECT * FROM t1 WHERE id = 100;
35+EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1 WHERE id = 100;
36+EXPLAIN CREATE TABLE ct1 AS SELECT * FROM t1 WHERE id = 100;
37+EXPLAIN EXECUTE p1;
38+DEALLOCATE p1;
39+PREPARE p1 AS SELECT * FROM t1 WHERE id = 100;
40+EXPLAIN CREATE TABLE ct1 AS EXECUTE p1;
41+
42+DEALLOCATE p1;
43+
44+SET pg_hint_plan.enable_hint_table to off;
45+DELETE FROM hint_plan.hints;
旧リポジトリブラウザで表示