| Revision | 9e2f8b6127c0f0cf9204529b2e83d967876f0ae1 (tree) |
|---|---|
| Time | 2022-01-18 17:40:35 |
| Author | Daniil Anisimov <anisimow.d@gmai...> |
| Commiter | Kyotaro Horiguchi |
Allow hints to be placed anywhere in query
Hints description is restriected to be placed before certain
characters. That is effectively at the beginning of a query, or after
EXPLAIN or PREPARE. This commit adds a new setting parameter
pg_hint_plan.hints_anywhere, which gets rid of that restriction. When
it is on, pg_hint_plan ignores SQL syntax at all while reading hints
so there's no restricion on where hint string is placed in a query
string. On the other hand it may lead to false reads from a non-hint
strings.
Makefile (diff)
doc/pg_hint_plan-ja.html (diff) doc/pg_hint_plan.html (diff)
pg_hint_plan.c (diff)| @@ -7,9 +7,9 @@ | ||
| 7 | 7 | MODULES = pg_hint_plan |
| 8 | 8 | HINTPLANVER = 1.4 |
| 9 | 9 | |
| 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 | |
| 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 hints_anywhere | |
| 11 | 11 | |
| 12 | -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 | |
| 12 | +#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 expected/hints_anywhere.out | |
| 13 | 13 | |
| 14 | 14 | REGRESS_OPTS = --encoding=UTF8 |
| 15 | 15 |
| @@ -368,6 +368,8 @@ EXPLAIN SELECT * FROM a, b WHERE a.val = b.val; | ||
| 368 | 368 | <td>動作状況を示すログメッセージの出力を制御します。指定可能な値は off, on, verbose, detailed です。</td><td>off</td></tr> |
| 369 | 369 | <tr><td>pg_hint_plan.message_level</td> |
| 370 | 370 | <td>動作ログメッセージのログレベルを指定します。有効な値は、debug5、debug4、debug3、debug2、debug1、log、info、notice、warning、またはerrorです。</td><td>LOG</td></tr> |
| 371 | +<tr><td>pg_hint_plan.hints_anywhere</td> | |
| 372 | + <td>On の場合、pg_hint_planはSQL構文を無視してヒント文字列の読み取りを行います。この設定ではヒントをSQL文のどこにでも記述することができますが、意図しない文字列がヒントととして読み取られる可能性がある点に注意してください。</td><td>off</td></tr> | |
| 371 | 373 | |
| 372 | 374 | </tbody> |
| 373 | 375 | </table> |
| @@ -240,6 +240,8 @@ postgres-# SELECT * FROM table1 t1 WHERE key = 'value'; | ||
| 240 | 240 | <td>Controls debug print and verbosity. Valid values are off, on, detailed and verbose.</td><td>off</td></tr> |
| 241 | 241 | <tr><td>pg_hint_plan.message_level</td> |
| 242 | 242 | <td>Specifies message level of debug print. Valid values are error, warning, notice, info, log, debug<n>.</td><td>LOG</td></tr> |
| 243 | +<tr><td>pg_hint_plan.hints_anywhere</td> | |
| 244 | + <td>If it is on, pg_hint_plan reads hints ignoring SQL syntax. This allows to hints to be placed anywhere in a query but be cautious of false reads.</td><td>off</td></tr> | |
| 243 | 245 | </tbody> |
| 244 | 246 | </table> |
| 245 | 247 | <h2 id="install">Installation</h2> |
| @@ -530,6 +530,7 @@ static int pg_hint_plan_parse_message_level = INFO; | ||
| 530 | 530 | static int pg_hint_plan_debug_message_level = LOG; |
| 531 | 531 | /* Default is off, to keep backward compatibility. */ |
| 532 | 532 | static bool pg_hint_plan_enable_hint_table = false; |
| 533 | +static bool pg_hint_plan_hints_anywhere = false; | |
| 533 | 534 | |
| 534 | 535 | static int plpgsql_recurse_level = 0; /* PLpgSQL recursion level */ |
| 535 | 536 | static int recurse_level = 0; /* recursion level incl. direct SPI calls */ |
| @@ -701,6 +702,17 @@ _PG_init(void) | ||
| 701 | 702 | assign_enable_hint_table, |
| 702 | 703 | NULL); |
| 703 | 704 | |
| 705 | + DefineCustomBoolVariable("pg_hint_plan.hints_anywhere", | |
| 706 | + "Read hints from anywhere in a query.", | |
| 707 | + "This option lets pg_hint_plan ignore syntax so be cautious for false reads.", | |
| 708 | + &pg_hint_plan_hints_anywhere, | |
| 709 | + false, | |
| 710 | + PGC_USERSET, | |
| 711 | + 0, | |
| 712 | + NULL, | |
| 713 | + NULL, | |
| 714 | + NULL); | |
| 715 | + | |
| 704 | 716 | EmitWarningsOnPlaceholders("pg_hint_plan"); |
| 705 | 717 | |
| 706 | 718 | /* Install hooks. */ |
| @@ -1881,33 +1893,35 @@ get_hints_from_comment(const char *p) | ||
| 1881 | 1893 | hint_head = strstr(p, HINT_START); |
| 1882 | 1894 | if (hint_head == NULL) |
| 1883 | 1895 | return NULL; |
| 1884 | - for (;p < hint_head; p++) | |
| 1896 | + if (!pg_hint_plan_hints_anywhere) | |
| 1885 | 1897 | { |
| 1886 | - /* | |
| 1887 | - * Allow these characters precedes hint comment: | |
| 1888 | - * - digits | |
| 1889 | - * - alphabets which are in ASCII range | |
| 1890 | - * - space, tabs and new-lines | |
| 1891 | - * - underscores, for identifier | |
| 1892 | - * - commas, for SELECT clause, EXPLAIN and PREPARE | |
| 1893 | - * - parentheses, for EXPLAIN and PREPARE | |
| 1894 | - * | |
| 1895 | - * Note that we don't use isalpha() nor isalnum() in ctype.h here to | |
| 1896 | - * avoid behavior which depends on locale setting. | |
| 1897 | - */ | |
| 1898 | - if (!(*p >= '0' && *p <= '9') && | |
| 1899 | - !(*p >= 'A' && *p <= 'Z') && | |
| 1900 | - !(*p >= 'a' && *p <= 'z') && | |
| 1901 | - !isspace(*p) && | |
| 1902 | - *p != '_' && | |
| 1903 | - *p != ',' && | |
| 1904 | - *p != '(' && *p != ')') | |
| 1905 | - return NULL; | |
| 1898 | + for (;p < hint_head; p++) | |
| 1899 | + { | |
| 1900 | + /* | |
| 1901 | + * Allow these characters precedes hint comment: | |
| 1902 | + * - digits | |
| 1903 | + * - alphabets which are in ASCII range | |
| 1904 | + * - space, tabs and new-lines | |
| 1905 | + * - underscores, for identifier | |
| 1906 | + * - commas, for SELECT clause, EXPLAIN and PREPARE | |
| 1907 | + * - parentheses, for EXPLAIN and PREPARE | |
| 1908 | + * | |
| 1909 | + * Note that we don't use isalpha() nor isalnum() in ctype.h here to | |
| 1910 | + * avoid behavior which depends on locale setting. | |
| 1911 | + */ | |
| 1912 | + if (!(*p >= '0' && *p <= '9') && | |
| 1913 | + !(*p >= 'A' && *p <= 'Z') && | |
| 1914 | + !(*p >= 'a' && *p <= 'z') && | |
| 1915 | + !isspace(*p) && | |
| 1916 | + *p != '_' && | |
| 1917 | + *p != ',' && | |
| 1918 | + *p != '(' && *p != ')') | |
| 1919 | + return NULL; | |
| 1920 | + } | |
| 1906 | 1921 | } |
| 1907 | 1922 | |
| 1908 | - len = strlen(HINT_START); | |
| 1909 | - head = (char *) p; | |
| 1910 | - p += len; | |
| 1923 | + head = (char *)hint_head; | |
| 1924 | + p = head + strlen(HINT_START); | |
| 1911 | 1925 | skip_space(p); |
| 1912 | 1926 | |
| 1913 | 1927 | /* find hint end keyword. */ |
Fork