• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: コミット

firtst release


コミットメタ情報

リビジョンe59da6d0f383aec3033d6492bf96ed8148673414 (tree)
日時2017-07-27 19:20:42
作者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.2.1
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-W 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-W 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
@@ -1786,8 +1786,9 @@ get_hints_from_table(const char *client_query, const char *client_application)
17861786 /*
17871787 * Get client-supplied query string. Addtion to that the jumbled query is
17881788 * supplied if the caller requested. From the restriction of JumbleQuery, some
1789- * kind of query needs special amendments. Reutrns NULL if the current hint
1790- * string is still valid.
1789+ * kind of query needs special amendments. Reutrns NULL if this query doesn't
1790+ * change the current hint. This function returns NULL also when something
1791+ * wrong has happend and let the caller continue using the current hints.
17911792 */
17921793 static const char *
17931794 get_query_string(ParseState *pstate, Query *query, Query **jumblequery)
@@ -1797,15 +1798,22 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery)
17971798 if (jumblequery != NULL)
17981799 *jumblequery = query;
17991800
1800- if (query->commandType == CMD_UTILITY)
1801+ /* Query for DeclareCursorStmt is CMD_SELECT and has query->utilityStmt */
1802+ if (query->commandType == CMD_UTILITY || query->utilityStmt)
18011803 {
18021804 Query *target_query = query;
18031805
1804- /* Use the target query if EXPLAIN */
1805- if (IsA(query->utilityStmt, ExplainStmt))
1806+ /*
1807+ * Some utility statements have a subquery that we can hint on. Since
1808+ * EXPLAIN can be placed before other kind of utility statements and
1809+ * EXECUTE can be contained other kind of utility statements, these
1810+ * conditions are not mutually exclusive and should be considered in
1811+ * this order.
1812+ */
1813+ if (IsA(target_query->utilityStmt, ExplainStmt))
18061814 {
1807- ExplainStmt *stmt = (ExplainStmt *)(query->utilityStmt);
1808-
1815+ ExplainStmt *stmt = (ExplainStmt *)target_query->utilityStmt;
1816+
18091817 Assert(IsA(stmt->query, Query));
18101818 target_query = (Query *)stmt->query;
18111819
@@ -1815,34 +1823,43 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery)
18151823 target_query = (Query *)target_query->utilityStmt;
18161824 }
18171825
1818- if (IsA(target_query, CreateTableAsStmt))
1826+ /*
1827+ * JumbleQuery does not accept a Query that has utilityStmt. On the
1828+ * other hand DeclareCursorStmt is in a bit strange shape that is
1829+ * flipped upside down.
1830+ */
1831+ if (IsA(target_query, Query) &&
1832+ target_query->utilityStmt &&
1833+ IsA(target_query->utilityStmt, DeclareCursorStmt))
18191834 {
18201835 /*
1821- * Use the the body query for CREATE AS. The Query for jumble also
1822- * replaced with the corresponding one.
1836+ * The given Query cannot be modified so copy it and modify so that
1837+ * JumbleQuery can accept it.
18231838 */
1839+ Assert(IsA(target_query, Query) &&
1840+ target_query->commandType == CMD_SELECT);
1841+ target_query = copyObject(target_query);
1842+ target_query->utilityStmt = NULL;
1843+ }
1844+
1845+ if (IsA(target_query, CreateTableAsStmt))
1846+ {
18241847 CreateTableAsStmt *stmt = (CreateTableAsStmt *) target_query;
1825- PreparedStatement *entry;
1826- Query *tmp_query;
18271848
18281849 Assert(IsA(stmt->query, Query));
1829- tmp_query = (Query *) stmt->query;
1850+ target_query = (Query *) stmt->query;
18301851
1831- if (tmp_query->commandType == CMD_UTILITY &&
1832- IsA(tmp_query->utilityStmt, ExecuteStmt))
1833- {
1834- ExecuteStmt *estmt = (ExecuteStmt *) tmp_query->utilityStmt;
1835- entry = FetchPreparedStatement(estmt->name, true);
1836- p = entry->plansource->query_string;
1837- target_query = (Query *) linitial (entry->plansource->query_list);
1838- }
1852+ /* strip out the top-level query for further processing */
1853+ if (target_query->commandType == CMD_UTILITY &&
1854+ target_query->utilityStmt != NULL)
1855+ target_query = (Query *)target_query->utilityStmt;
18391856 }
1840- else
1857+
18411858 if (IsA(target_query, ExecuteStmt))
18421859 {
18431860 /*
1844- * Use the prepared query for EXECUTE. The Query for jumble also
1845- * replaced with the corresponding one.
1861+ * Use the prepared query for EXECUTE. The Query for jumble
1862+ * also replaced with the corresponding one.
18461863 */
18471864 ExecuteStmt *stmt = (ExecuteStmt *)target_query;
18481865 PreparedStatement *entry;
@@ -1851,15 +1868,16 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery)
18511868 p = entry->plansource->query_string;
18521869 target_query = (Query *) linitial (entry->plansource->query_list);
18531870 }
1854-
1855- /* We don't accept other than a Query other than a CMD_UTILITY */
1871+
1872+ /* JumbleQuery accespts only a non-utility Query */
18561873 if (!IsA(target_query, Query) ||
1857- target_query->commandType == CMD_UTILITY)
1874+ target_query->utilityStmt != NULL)
18581875 target_query = NULL;
18591876
18601877 if (jumblequery)
18611878 *jumblequery = target_query;
18621879 }
1880+
18631881 /* Return NULL if the pstate is not identical to the top-level query */
18641882 else if (strcmp(pstate->p_sourcetext, p) != 0)
18651883 p = NULL;
@@ -2849,7 +2867,7 @@ pg_hint_plan_post_parse_analyze(ParseState *pstate, Query *query)
28492867 }
28502868 }
28512869
2852- /* retrun if we have hint here*/
2870+ /* retrun if we have hint here */
28532871 if (current_hint_str)
28542872 return;
28552873 }
--- /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;
旧リポジトリブラウザで表示