#include "executor/spi.h"
#include "catalog/pg_type.h"
/*
- * PG9.1でもPL/pgSQLのクエリにヒントを適用するために、ソースディレクトリに
- * plpgsql.hを加えた。
- * PG9.2以降ではplpgsql.hが外部モジュールでも使えるように公開されたので、公開さ
- * れたほうを参照する。
+ * We have our own header file "plpgsql-9.1", which is necessary to support
+ * hints for queries in PL/pgSQL blocks, in pg_hint_plan source package,
+ * because PostgreSQL 9.1 doesn't provide the header file as a part of
+ * installation. This header file is a copy of src/pl/plpgsql/src/plpgsql.h in
+ * PostgreSQL 9.1.9 source tree,
+ *
+ * On the other hand, 9.2 installation provides that header file for external
+ * modules, so we include the header in ordinary place.
*/
#if PG_VERSION_NUM >= 90200
#include "plpgsql.h"
Index parent_relid; /* inherit parent table relid */
Oid parent_rel_oid; /* inherit parent table relid */
ScanMethodHint *parent_hint; /* inherit parent table scan hint */
- List *parent_index_infos; /* infomation of inherit parent table's
+ List *parent_index_infos; /* information of inherit parent table's
* index */
/* for join method hints */
static bool pg_hint_plan_enable_hint = true;
static bool pg_hint_plan_debug_print = false;
static int pg_hint_plan_parse_messages = INFO;
+/* Default is off, to keep backward compatibility. */
+static bool pg_hint_plan_enable_hint_table = false;
static const struct config_enum_entry parse_messages_level_options[] = {
{"debug", DEBUG2, true},
* PL/pgSQL plugin for retrieving string representation of each query during
* function execution.
*/
-const char *plpgsql_query_string = NULL;
+static const char *plpgsql_query_string = NULL;
+static enum PLpgSQL_stmt_types plpgsql_query_string_src;
+
PLpgSQL_plugin plugin_funcs = {
NULL,
NULL,
NULL,
};
+/* Current nesting depth of SPI calls, used to prevent recursive calls */
+static int nested_level = 0;
+
/*
* Module load callbacks
*/
NULL,
NULL);
+ DefineCustomBoolVariable("pg_hint_plan.enable_hint_table",
+ "Force planner to not get hint by using table lookups.",
+ NULL,
+ &pg_hint_plan_enable_hint_table,
+ false,
+ PGC_USERSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
/* Install hooks. */
prev_ProcessUtility = ProcessUtility_hook;
ProcessUtility_hook = pg_hint_plan_ProcessUtility;
}
/*
- * Append string which repserents all hints in a given state to buf, with
+ * Append string which represents all hints in a given state to buf, with
* preceding title with them.
*/
static void
" OR application_name = '' ) "
" ORDER BY application_name DESC";
static SPIPlanPtr plan = NULL;
- int ret;
char *hints = NULL;
Oid argtypes[2] = { TEXTOID, TEXTOID };
Datum values[2];
text *qry;
text *app;
- ret = SPI_connect();
- if (ret != SPI_OK_CONNECT)
- elog(ERROR, "pg_hint_plan: SPI_connect => %d", ret);
-
- if (plan == NULL)
+ PG_TRY();
{
- SPIPlanPtr p;
- p = SPI_prepare(search_query, 2, argtypes);
- if (p == NULL)
- elog(ERROR, "pg_hint_plan: SPI_prepare => %d", SPI_result);
- plan = SPI_saveplan(p);
- SPI_freeplan(p);
+ ++nested_level;
+
+ SPI_connect();
+
+ if (plan == NULL)
+ {
+ SPIPlanPtr p;
+ p = SPI_prepare(search_query, 2, argtypes);
+ plan = SPI_saveplan(p);
+ SPI_freeplan(p);
+ }
+
+ qry = cstring_to_text(client_query);
+ app = cstring_to_text(client_application);
+ values[0] = PointerGetDatum(qry);
+ values[1] = PointerGetDatum(app);
+
+ SPI_execute_plan(plan, values, nulls, true, 1);
+
+ if (SPI_processed > 0)
+ {
+ char *buf;
+
+ hints = SPI_getvalue(SPI_tuptable->vals[0],
+ SPI_tuptable->tupdesc, 1);
+ /*
+ * Here we use SPI_palloc to ensure that hints string is valid even
+ * after SPI_finish call. We can't use simple palloc because it
+ * allocates memory in SPI's context and that context is deleted in
+ * SPI_finish.
+ */
+ buf = SPI_palloc(strlen(hints) + 1);
+ strcpy(buf, hints);
+ hints = buf;
+ }
+
+ SPI_finish();
+
+ --nested_level;
}
-
- qry = cstring_to_text(client_query);
- app = cstring_to_text(client_application);
- values[0] = PointerGetDatum(qry);
- values[1] = PointerGetDatum(app);
-
- pg_hint_plan_enable_hint = false;
- ret = SPI_execute_plan(plan, values, nulls, true, 1);
- pg_hint_plan_enable_hint = true;
- if (ret != SPI_OK_SELECT)
- elog(ERROR, "pg_hint_plan: SPI_execute_plan => %d", ret);
-
- if (SPI_processed > 0)
+ PG_CATCH();
{
- char *buf;
-
- hints = SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1);
- /*
- * Here we use SPI_palloc to ensure that hints string is valid even
- * after SPI_finish call. We can't use simple palloc because it
- * allocates memory in SPI's context and that context is deleted in
- * SPI_finish.
- */
- buf = SPI_palloc(strlen(hints) + 1);
- strcpy(buf, hints);
- hints = buf;
+ --nested_level;
+ PG_RE_THROW();
}
-
- SPI_finish();
+ PG_END_TRY();
return hints;
}
/*
* If an object (or a set of objects) has multiple hints of same hint-type,
- * only the last hint is valid and others are igonred in planning.
+ * only the last hint is valid and others are ignored in planning.
* Hints except the last are marked as 'duplicated' to remember the order.
*/
for (i = 0; i < hstate->nall_hints - 1; i++)
{
Node *node;
- if (!pg_hint_plan_enable_hint)
+ /*
+ * Use standard planner if pg_hint_plan is disabled or current nesting
+ * depth is nesting depth of SPI calls.
+ */
+ if (!pg_hint_plan_enable_hint || nested_level > 0)
{
if (prev_ProcessUtility)
(*prev_ProcessUtility) (parsetree, queryString, params,
static PlannedStmt *
pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
{
- const char *hints;
+ const char *hints = NULL;
const char *query;
char *norm_query;
pgssJumbleState jstate;
HintState *hstate;
/*
- * Use standard planner if pg_hint_plan is disabled. Other hook functions
- * try to change plan with current_hint if any, so set it to NULL.
+ * Use standard planner if pg_hint_plan is disabled or current nesting
+ * depth is nesting depth of SPI calls. Other hook functions try to change
+ * plan with current_hint if any, so set it to NULL.
*/
- if (!pg_hint_plan_enable_hint)
+ if (!pg_hint_plan_enable_hint || nested_level > 0)
goto standard_planner_proc;
/* Create hint struct from client-supplied query string. */
query = get_query_string();
/*
- * Search hint information which is stored for the query and the
- * application. Query string is normalized before using in condition
- * in order to allow fuzzy matching.
+ * Create hintstate from hint specified for the query, if any.
*
- * XXX: normalizing code is copied from pg_stat_statements.c, so be careful
- * when supporting PostgreSQL's version up.
- */
- jstate.jumble = (unsigned char *) palloc(JUMBLE_SIZE);
- jstate.jumble_len = 0;
- jstate.clocations_buf_size = 32;
- jstate.clocations = (pgssLocationLen *)
- palloc(jstate.clocations_buf_size * sizeof(pgssLocationLen));
- jstate.clocations_count = 0;
- JumbleQuery(&jstate, parse);
- /*
- * generate_normalized_query() copies exact given query_len bytes, so we
- * add 1 byte for null-termination here. As comments on
- * generate_normalized_query says, generate_normalized_query doesn't take
- * care of null-terminate, but additional 1 byte ensures that '\0' byte in
- * the source buffer to be copied into norm_query.
+ * First we lookup hint in pg_hint.hints table by normalized query string,
+ * unless pg_hint_plan.enable_hint_table is OFF.
+ * This parameter provides option to avoid overhead of table lookup during
+ * planning.
+ *
+ * If no hint was found, then we try to get hint from special query comment.
*/
- query_len = strlen(query) + 1;
- norm_query = generate_normalized_query(&jstate,
- query,
- &query_len,
- GetDatabaseEncoding());
- hints = get_hints_from_table(norm_query, application_name);
- elog(DEBUG1,
- "pg_hint_plan: get_hints_from_table [%s][%s]=>[%s]",
- norm_query, application_name,
- hints ? hints : "(none)");
+ if (pg_hint_plan_enable_hint_table)
+ {
+ /*
+ * Search hint information which is stored for the query and the
+ * application. Query string is normalized before using in condition
+ * in order to allow fuzzy matching.
+ *
+ * XXX: normalizing code is copied from pg_stat_statements.c, so be
+ * careful when supporting PostgreSQL's version up.
+ */
+ jstate.jumble = (unsigned char *) palloc(JUMBLE_SIZE);
+ jstate.jumble_len = 0;
+ jstate.clocations_buf_size = 32;
+ jstate.clocations = (pgssLocationLen *)
+ palloc(jstate.clocations_buf_size * sizeof(pgssLocationLen));
+ jstate.clocations_count = 0;
+ JumbleQuery(&jstate, parse);
+ /*
+ * generate_normalized_query() copies exact given query_len bytes, so we
+ * add 1 byte for null-termination here. As comments on
+ * generate_normalized_query says, generate_normalized_query doesn't
+ * take care of null-terminate, but additional 1 byte ensures that '\0'
+ * byte in the source buffer to be copied into norm_query.
+ */
+ query_len = strlen(query) + 1;
+ norm_query = generate_normalized_query(&jstate,
+ query,
+ &query_len,
+ GetDatabaseEncoding());
+ hints = get_hints_from_table(norm_query, application_name);
+ elog(DEBUG1,
+ "pg_hint_plan: get_hints_from_table [%s][%s]=>[%s]",
+ norm_query, application_name, hints ? hints : "(none)");
+ }
if (hints == NULL)
hints = get_hints_from_comment(query);
hstate = create_hintstate(parse, hints);
* Return scan method hint which matches given aliasname.
*/
static ScanMethodHint *
-find_scan_hint(PlannerInfo *root, RelOptInfo *rel)
+find_scan_hint(PlannerInfo *root, Index relid, RelOptInfo *rel)
{
RangeTblEntry *rte;
int i;
* - not a base relation
* - not an ordinary relation (such as join and subquery)
*/
- if (rel->reloptkind != RELOPT_BASEREL || rel->rtekind != RTE_RELATION)
+ if (rel && (rel->reloptkind != RELOPT_BASEREL || rel->rtekind != RTE_RELATION))
return NULL;
- rte = root->simple_rte_array[rel->relid];
+ rte = root->simple_rte_array[relid];
/* We can't force scan method of foreign tables */
if (rte->relkind == RELKIND_FOREIGN_TABLE)
}
}
- /* Check to match the predicate's paraameter of index */
+ /* Check to match the predicate's parameter of index */
if (p_info->indpred_str &&
!heap_attisnull(ht_idx, Anum_pg_index_indpred))
{
Datum result;
/*
- * to change the predicate's parabeter of child's
+ * to change the predicate's parameter of child's
* index to strings
*/
predDatum = SysCacheGetAttr(INDEXRELID, ht_idx,
}
/*
- * to check to match the expression's paraameter of index with child indexes
+ * to check to match the expression's parameter of index with child indexes
*/
p_info->expression_str = NULL;
if(!heap_attisnull(indexRelation->rd_indextuple, Anum_pg_index_indexprs))
}
/*
- * to check to match the predicate's paraameter of index with child indexes
+ * to check to match the predicate's parameter of index with child indexes
*/
p_info->indpred_str = NULL;
if(!heap_attisnull(indexRelation->rd_indextuple, Anum_pg_index_indpred))
pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
bool inhparent, RelOptInfo *rel)
{
- ScanMethodHint *hint;
+ ScanMethodHint *hint = NULL;
if (prev_get_relation_info)
(*prev_get_relation_info) (root, relationObjectId, inhparent, rel);
- /* Do nothing if we don't have valid hint in this context. */
- if (!current_hint)
+ /*
+ * Do nothing if we don't have valid hint in this context or current
+ * nesting depth is nesting depth of SPI calls.
+ */
+ if (!current_hint || nested_level > 0)
return;
if (inhparent)
{
/* store does relids of parent table. */
current_hint->parent_relid = rel->relid;
- current_hint->parent_rel_oid = relationObjectId;
+
+ }
+ else
+ {
+ /*
+ * Inheritance planner doesn't request information for the parent
+ * relation so we should check if this relation has a parent. We can
+ * ignore nested inheritace case because inheritance planner doesn't
+ * meet it.
+ */
+ ListCell *l;
+ foreach (l, root->append_rel_list)
+ {
+ AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l);
+
+ if (appinfo->child_relid == rel->relid)
+ {
+ if (current_hint->parent_relid != appinfo->parent_relid)
+ {
+ inhparent = true;
+ current_hint->parent_relid = appinfo->parent_relid;
+ }
+ break;
+ }
+ }
+
+ }
+
+ if (inhparent)
+ {
+ Relation parent_rel;
+ List *indexoidlist;
+ ListCell *l;
+ Oid parentrel_oid;
+
+ /*
+ * Get and apply the hint for theparent_rel if the new parent has been
+ * found. This relation should be an ordinary relation so calling
+ * find_scan_hint with rel == NULL is safe.
+ */
+ if ((hint = find_scan_hint(root, current_hint->parent_relid,
+ NULL)) == NULL)
+ {
+ set_scan_config_options(current_hint->init_scan_mask,
+ current_hint->context);
+ }
+ else
+ {
+ set_scan_config_options(hint->enforce_mask, current_hint->context);
+ hint->base.state = HINT_STATE_USED;
+ }
+
+ current_hint->parent_hint = hint;
+
+ /* Resolve index name mask (if any) using this parent. */
+ if (hint && hint->indexnames)
+ {
+ parentrel_oid =
+ root->simple_rte_array[current_hint->parent_relid]->relid;
+ parent_rel = heap_open(parentrel_oid, NoLock);
+
+ /*
+ * Search for indexes match the hint for this parent
+ */
+ indexoidlist = RelationGetIndexList(parent_rel);
+
+ foreach(l, indexoidlist)
+ {
+ Oid indexoid = lfirst_oid(l);
+ char *indexname = get_rel_name(indexoid);
+ bool use_index = false;
+ ListCell *lc;
+ ParentIndexInfo *parent_index_info;
+
+ foreach(lc, hint->indexnames)
+ {
+ if (RelnameCmp(&indexname, &lfirst(lc)) == 0)
+ {
+ use_index = true;
+ break;
+ }
+ }
+ if (!use_index)
+ continue;
+
+ parent_index_info = get_parent_index_info(indexoid,
+ parentrel_oid);
+ current_hint->parent_index_infos =
+ lappend(current_hint->parent_index_infos, parent_index_info);
+ }
+ heap_close(parent_rel, NoLock);
+ }
+
+ if (current_hint->parent_relid == rel->relid)
+ {
+ /* This rel is a inheritance parent, which won't be scanned. */
+ return;
+ }
}
- else if (current_hint->parent_relid != 0)
+
+ if (current_hint->parent_hint != 0)
{
/*
- * We use the same GUC parameter if this table is the child table of a
- * table called pg_hint_plan_get_relation_info just before that.
+ * If inheritance parent is registered, check if it is really my
+ * parent.
*/
ListCell *l;
- /* append_rel_list contains all append rels; ignore others */
foreach(l, root->append_rel_list)
{
AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l);
delete_indexes(current_hint->parent_hint, rel,
relationObjectId);
+ /* Scan fixation status is the same to the parent. */
return;
}
}
- /* This rel is not inherit table. */
+ /* This rel is not a child of the current parent. */
current_hint->parent_relid = 0;
current_hint->parent_rel_oid = InvalidOid;
current_hint->parent_hint = NULL;
}
- /*
- * If scan method hint was given, reset GUC parameters which control
- * planner behavior about choosing scan methods.
- */
- if ((hint = find_scan_hint(root, rel)) == NULL)
+ /* This table doesn't have a parent. Apply its own hints */
+ if ((hint = find_scan_hint(root, rel->relid, rel)) == NULL)
{
set_scan_config_options(current_hint->init_scan_mask,
current_hint->context);
set_scan_config_options(hint->enforce_mask, current_hint->context);
hint->base.state = HINT_STATE_USED;
- if (inhparent)
- {
- Relation relation;
- List *indexoidlist;
- ListCell *l;
-
- current_hint->parent_hint = hint;
-
- relation = heap_open(relationObjectId, NoLock);
- indexoidlist = RelationGetIndexList(relation);
-
- foreach(l, indexoidlist)
- {
- Oid indexoid = lfirst_oid(l);
- char *indexname = get_rel_name(indexoid);
- bool use_index = false;
- ListCell *lc;
- ParentIndexInfo *parent_index_info;
-
- foreach(lc, hint->indexnames)
- {
- if (RelnameCmp(&indexname, &lfirst(lc)) == 0)
- {
- use_index = true;
- break;
- }
- }
- if (!use_index)
- continue;
-
- parent_index_info = get_parent_index_info(indexoid,
- relationObjectId);
- current_hint->parent_index_infos =
- lappend(current_hint->parent_index_infos, parent_index_info);
- }
- heap_close(relation, NoLock);
- }
- else
- delete_indexes(hint, rel, InvalidOid);
+ delete_indexes(hint, rel, InvalidOid);
}
/*
* planner if scan method hint is not specified, otherwise use
* specified hints and mark the hint as used.
*/
- if ((hint = find_scan_hint(root, rel)) == NULL)
+ if ((hint = find_scan_hint(root, rel->relid, rel)) == NULL)
set_scan_config_options(hstate->init_scan_mask,
hstate->context);
else
JoinMethodHint *join_hint;
int save_nestlevel;
- if ((scan_hint = find_scan_hint(root, innerrel)) != NULL)
+ if ((scan_hint = find_scan_hint(root, innerrel->relid, innerrel)) != NULL)
{
set_scan_config_options(scan_hint->enforce_mask, current_hint->context);
scan_hint->base.state = HINT_STATE_USED;
/*
* Use standard planner (or geqo planner) if pg_hint_plan is disabled or no
- * valid hint is supplied.
+ * valid hint is supplied or current nesting depth is nesting depth of SPI
+ * calls.
*/
- if (!current_hint)
+ if (!current_hint || nested_level > 0)
{
if (prev_join_search)
return (*prev_join_search) (root, levels_needed, initial_rels);
* plpgsql_query_string to use it in planner hook. It's safe to use one global
* variable for the purpose, because its content is only necessary until
* planner hook is called for the query, so recursive PL/pgSQL function calls
- * don't harm this mechanismk.
+ * don't harm this mechanism.
*/
static void
pg_hint_plan_plpgsql_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
{
- if ((enum PLpgSQL_stmt_types) stmt->cmd_type == PLPGSQL_STMT_EXECSQL)
+ PLpgSQL_expr *expr = NULL;
+
+ switch ((enum PLpgSQL_stmt_types) stmt->cmd_type)
+ {
+ case PLPGSQL_STMT_FORS:
+ expr = ((PLpgSQL_stmt_fors *) stmt)->query;
+ break;
+ case PLPGSQL_STMT_FORC:
+ expr = ((PLpgSQL_var *) (estate->datums[((PLpgSQL_stmt_forc *)stmt)->curvar]))->cursor_explicit_expr;
+ break;
+ case PLPGSQL_STMT_RETURN_QUERY:
+ if (((PLpgSQL_stmt_return_query *) stmt)->query != NULL)
+ expr = ((PLpgSQL_stmt_return_query *) stmt)->query;
+ else
+ expr = ((PLpgSQL_stmt_return_query *) stmt)->dynquery;
+ break;
+ case PLPGSQL_STMT_EXECSQL:
+ expr = ((PLpgSQL_stmt_execsql *) stmt)->sqlstmt;
+ break;
+ case PLPGSQL_STMT_DYNEXECUTE:
+ expr = ((PLpgSQL_stmt_dynexecute *) stmt)->query;
+ break;
+ case PLPGSQL_STMT_DYNFORS:
+ expr = ((PLpgSQL_stmt_dynfors *) stmt)->query;
+ break;
+ case PLPGSQL_STMT_OPEN:
+ if (((PLpgSQL_stmt_open *) stmt)->query != NULL)
+ expr = ((PLpgSQL_stmt_open *) stmt)->query;
+ else if (((PLpgSQL_stmt_open *) stmt)->dynquery != NULL)
+ expr = ((PLpgSQL_stmt_open *) stmt)->dynquery;
+ else
+ expr = ((PLpgSQL_var *) (estate->datums[((PLpgSQL_stmt_open *)stmt)->curvar]))->cursor_explicit_expr;
+ break;
+ default:
+ break;
+ }
+
+ if (expr)
{
- PLpgSQL_expr *expr = ((PLpgSQL_stmt_execsql *) stmt)->sqlstmt;
plpgsql_query_string = expr->query;
+ plpgsql_query_string_src = (enum PLpgSQL_stmt_types) stmt->cmd_type;
}
}
static void
pg_hint_plan_plpgsql_stmt_end(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
{
- if ((enum PLpgSQL_stmt_types) stmt->cmd_type == PLPGSQL_STMT_EXECSQL)
+ if (plpgsql_query_string &&
+ plpgsql_query_string_src == stmt->cmd_type)
plpgsql_query_string = NULL;
}
OSDN Git Service
