From 9e2f8b6127c0f0cf9204529b2e83d967876f0ae1 Mon Sep 17 00:00:00 2001 From: Daniil Anisimov Date: Tue, 18 Jan 2022 17:40:35 +0900 Subject: [PATCH] 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 | 4 ++-- doc/pg_hint_plan-ja.html | 2 ++ doc/pg_hint_plan.html | 2 ++ pg_hint_plan.c | 62 +++++++++++++++++++++++++++++------------------- 4 files changed, 44 insertions(+), 26 deletions(-) diff --git a/Makefile b/Makefile index 3e27311..66b6ab0 100644 --- a/Makefile +++ b/Makefile @@ -7,9 +7,9 @@ MODULES = pg_hint_plan HINTPLANVER = 1.4 -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 +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 -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 +#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 REGRESS_OPTS = --encoding=UTF8 diff --git a/doc/pg_hint_plan-ja.html b/doc/pg_hint_plan-ja.html index 783600f..667ce14 100755 --- a/doc/pg_hint_plan-ja.html +++ b/doc/pg_hint_plan-ja.html @@ -368,6 +368,8 @@ EXPLAIN SELECT * FROM a, b WHERE a.val = b.val; 動作状況を示すログメッセージの出力を制御します。指定可能な値は off, on, verbose, detailed です。off pg_hint_plan.message_level 動作ログメッセージのログレベルを指定します。有効な値は、debug5、debug4、debug3、debug2、debug1、log、info、notice、warning、またはerrorです。LOG +pg_hint_plan.hints_anywhere + On の場合、pg_hint_planはSQL構文を無視してヒント文字列の読み取りを行います。この設定ではヒントをSQL文のどこにでも記述することができますが、意図しない文字列がヒントととして読み取られる可能性がある点に注意してください。off diff --git a/doc/pg_hint_plan.html b/doc/pg_hint_plan.html index 1773885..9bcd10b 100755 --- a/doc/pg_hint_plan.html +++ b/doc/pg_hint_plan.html @@ -240,6 +240,8 @@ postgres-# SELECT * FROM table1 t1 WHERE key = 'value'; Controls debug print and verbosity. Valid values are off, on, detailed and verbose.off pg_hint_plan.message_level Specifies message level of debug print. Valid values are error, warning, notice, info, log, debug.LOG +pg_hint_plan.hints_anywhere + 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.off

Installation

diff --git a/pg_hint_plan.c b/pg_hint_plan.c index 7cdfbe8..0852f56 100644 --- a/pg_hint_plan.c +++ b/pg_hint_plan.c @@ -530,6 +530,7 @@ static int pg_hint_plan_parse_message_level = INFO; static int pg_hint_plan_debug_message_level = LOG; /* Default is off, to keep backward compatibility. */ static bool pg_hint_plan_enable_hint_table = false; +static bool pg_hint_plan_hints_anywhere = false; static int plpgsql_recurse_level = 0; /* PLpgSQL recursion level */ static int recurse_level = 0; /* recursion level incl. direct SPI calls */ @@ -701,6 +702,17 @@ _PG_init(void) assign_enable_hint_table, NULL); + DefineCustomBoolVariable("pg_hint_plan.hints_anywhere", + "Read hints from anywhere in a query.", + "This option lets pg_hint_plan ignore syntax so be cautious for false reads.", + &pg_hint_plan_hints_anywhere, + false, + PGC_USERSET, + 0, + NULL, + NULL, + NULL); + EmitWarningsOnPlaceholders("pg_hint_plan"); /* Install hooks. */ @@ -1881,33 +1893,35 @@ get_hints_from_comment(const char *p) hint_head = strstr(p, HINT_START); if (hint_head == NULL) return NULL; - for (;p < hint_head; p++) + if (!pg_hint_plan_hints_anywhere) { - /* - * Allow these characters precedes hint comment: - * - digits - * - alphabets which are in ASCII range - * - space, tabs and new-lines - * - underscores, for identifier - * - commas, for SELECT clause, EXPLAIN and PREPARE - * - parentheses, for EXPLAIN and PREPARE - * - * Note that we don't use isalpha() nor isalnum() in ctype.h here to - * avoid behavior which depends on locale setting. - */ - if (!(*p >= '0' && *p <= '9') && - !(*p >= 'A' && *p <= 'Z') && - !(*p >= 'a' && *p <= 'z') && - !isspace(*p) && - *p != '_' && - *p != ',' && - *p != '(' && *p != ')') - return NULL; + for (;p < hint_head; p++) + { + /* + * Allow these characters precedes hint comment: + * - digits + * - alphabets which are in ASCII range + * - space, tabs and new-lines + * - underscores, for identifier + * - commas, for SELECT clause, EXPLAIN and PREPARE + * - parentheses, for EXPLAIN and PREPARE + * + * Note that we don't use isalpha() nor isalnum() in ctype.h here to + * avoid behavior which depends on locale setting. + */ + if (!(*p >= '0' && *p <= '9') && + !(*p >= 'A' && *p <= 'Z') && + !(*p >= 'a' && *p <= 'z') && + !isspace(*p) && + *p != '_' && + *p != ',' && + *p != '(' && *p != ')') + return NULL; + } } - len = strlen(HINT_START); - head = (char *) p; - p += len; + head = (char *)hint_head; + p = head + strlen(HINT_START); skip_space(p); /* find hint end keyword. */ -- 2.11.0