Index Cond: (id = 1)
(2 rows)
-DO LANGUAGE plpgsql $$
+-- static function
+CREATE FUNCTION testfunc() RETURNS RECORD AS $$
DECLARE
- id integer;
+ ret record;
BEGIN
- SELECT /*+SeqScan(t1)*/ t1.id INTO id FROM t1 WHERE t1.id = 1;
- RETURN;
+ SELECT /*+ SeqScan(t1) */ * INTO ret FROM t1 LIMIT 1;
+ RETURN ret;
END;
-$$;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
duplication hint:
error hint:
-CONTEXT: SQL statement "SELECT /*+SeqScan(t1)*/ t1.id FROM t1 WHERE t1.id = 1"
-PL/pgSQL function inline_code_block line 5 at SQL statement
+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 LIMIT 1"
+PL/pgSQL function testfunc() line 5 at SQL statement
+ testfunc
+----------
+ (1,1)
+(1 row)
+
+-- dynamic function
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS void AS $$
+BEGIN
+ EXECUTE format('/*+ SeqScan(t1) */ SELECT * FROM t1');
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "/*+ SeqScan(t1) */ SELECT * FROM t1"
+PL/pgSQL function testfunc() line 3 at EXECUTE statement
+ testfunc
+----------
+
+(1 row)
+
+-- This should not use SeqScan(t1)
+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ id | val
+----+-----
+ 1 | 1
+(1 row)
+
+-- Perform
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS void AS $$
+BEGIN
+ PERFORM 1, /*+ SeqScan(t1) */ * from t1;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT 1, /*+ SeqScan(t1) */ * from t1"
+PL/pgSQL function testfunc() line 3 at PERFORM
+ testfunc
+----------
+
+(1 row)
+
+-- FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+ sum int;
+ v int;
+BEGIN
+ sum := 0;
+ FOR v IN SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id LOOP
+ sum := sum + v;
+ END LOOP;
+ RETURN v;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id"
+PL/pgSQL function testfunc() line 7 at FOR over SELECT rows
+ testfunc
+----------
+
+(1 row)
+
+-- Dynamic FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+ sum int;
+ v int;
+ i int;
+BEGIN
+ sum := 0;
+ FOR v IN EXECUTE 'SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id' LOOP
+ sum := sum + v;
+ END LOOP;
+ RETURN v;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id"
+PL/pgSQL function testfunc() line 8 at FOR over EXECUTE statement
+ testfunc
+----------
+ 0
+(1 row)
+
+-- Cursor FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+ ref CURSOR FOR SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
+ rec record;
+ sum int := 0;
+BEGIN
+ FOR rec IN ref LOOP
+ sum := sum + rec.val;
+ END LOOP;
+ RETURN sum;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id"
+PL/pgSQL function testfunc() line 7 at FOR over cursor
+ testfunc
+----------
+ 495000
+(1 row)
+
+-- RETURN QUERY
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
+BEGIN
+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
+END;
+$$ LANGUAGE plpgsql;
+SELECT * FROM testfunc() LIMIT 1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id"
+PL/pgSQL function testfunc() line 3 at RETURN QUERY
+ id | val
+----+-----
+ 1 | 1
+(1 row)
+
+-- Test for error exit from inner SQL statement.
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
+BEGIN
+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id;
+END;
+$$ LANGUAGE plpgsql;
+SELECT * FROM testfunc() LIMIT 1;
+ERROR: relation "ttx" does not exist
+LINE 1: SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id
+ ^
+QUERY: SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id
+CONTEXT: PL/pgSQL function testfunc() line 3 at RETURN QUERY
+-- this should not use SeqScan(t1) hint.
+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ id | val
+----+-----
+ 1 | 1
+(1 row)
+
+DROP FUNCTION testfunc();
DROP EXTENSION pg_hint_plan;
--
-- Rows hint tests
static int pg_hint_plan_message_level = INFO;
/* Default is off, to keep backward compatibility. */
static bool pg_hint_plan_enable_hint_table = false;
-static bool hidestmt = false;
+
+/* Internal static variables. */
+static bool hidestmt = false; /* Allow or inhibit STATEMENT: output */
+
+static int plpgsql_recurse_level = 0; /* PLpgSQL recursion level */
+static int hint_inhibit_level = 0; /* Inhibit hinting if this is above 0 */
+ /* (This could not be above 1) */
static const struct config_enum_entry parse_messages_level_options[] = {
{"debug", DEBUG2, true},
{NULL, NULL, HINT_KEYWORD_UNRECOGNIZED}
};
-/*
- * PL/pgSQL plugin for retrieving string representation of each query during
- * function execution.
- */
-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
*/
if (!hstate)
{
elog(pg_hint_plan_message_level,
- "pg_hint_plan%s: HintStateDump:\nno hint", qnostr);
+ "pg_hint_plan%s: HintStateDump: no hint", qnostr);
return;
}
PG_TRY();
{
- ++nested_level;
+ hint_inhibit_level++;
SPI_connect();
SPI_finish();
- --nested_level;
+ hint_inhibit_level--;
}
PG_CATCH();
{
- --nested_level;
+ hint_inhibit_level--;
PG_RE_THROW();
}
PG_END_TRY();
{
const char *p;
- if (stmt_name)
+ if (plpgsql_recurse_level > 0)
+ {
+ /*
+ * This is quite ugly but this is the only point I could find where
+ * we can get the query string.
+ */
+ p = (char*)error_context_stack->arg;
+ }
+ else if (stmt_name)
{
PreparedStatement *entry;
entry = FetchPreparedStatement(stmt_name, true);
p = entry->plansource->query_string;
}
- else if (plpgsql_query_string)
- p = plpgsql_query_string;
else
p = debug_query_string;
* 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 (!pg_hint_plan_enable_hint || hint_inhibit_level > 0)
{
if (debug_level > 1)
ereport(pg_hint_plan_message_level,
* 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 || nested_level > 0)
+ if (!pg_hint_plan_enable_hint || hint_inhibit_level > 0)
{
if (debug_level > 1)
elog(pg_hint_plan_message_level,
"pg_hint_plan%s: planner: enable_hint=%d,"
- " nested_level=%d",
- qnostr, pg_hint_plan_enable_hint, nested_level);
+ " hint_inhibit_level=%d",
+ qnostr, pg_hint_plan_enable_hint, hint_inhibit_level);
hidestmt = true;
goto standard_planner_proc;
* Do nothing if we don't have a valid hint in this context or current
* nesting depth is at SPI calls.
*/
- if (!current_hint || nested_level > 0)
+ if (!current_hint || hint_inhibit_level > 0)
{
if (debug_level > 1)
ereport(pg_hint_plan_message_level,
(errhidestmt(true),
errmsg ("pg_hint_plan%s: get_relation_info"
" no hint to apply: relation=%u(%s), inhparent=%d,"
- " current_hint=%p, nested_level=%d",
+ " current_hint=%p, hint_inhibit_level=%d",
qnostr, relationObjectId,
get_rel_name(relationObjectId),
- inhparent, current_hint, nested_level)));
+ inhparent, current_hint, hint_inhibit_level)));
return;
}
(errhidestmt(true),
errmsg ("pg_hint_plan%s: get_relation_info"
" skipping inh parent: relation=%u(%s), inhparent=%d,"
- " current_hint=%p, nested_level=%d",
+ " current_hint=%p, hint_inhibit_level=%d",
qnostr, relationObjectId,
get_rel_name(relationObjectId),
- inhparent, current_hint, nested_level)));
+ inhparent, current_hint, hint_inhibit_level)));
return;
}
errmsg("pg_hint_plan%s: get_relation_info:"
" index deletion by parent hint: "
"relation=%u(%s), inhparent=%d, current_hint=%p,"
- " nested_level=%d",
+ " hint_inhibit_level=%d",
qnostr, relationObjectId,
get_rel_name(relationObjectId),
- inhparent, current_hint, nested_level)));
+ inhparent, current_hint, hint_inhibit_level)));
return;
}
errmsg ("pg_hint_plan%s: get_relation_info"
" index deletion:"
" relation=%u(%s), inhparent=%d, current_hint=%p,"
- " nested_level=%d, scanmask=0x%x",
+ " hint_inhibit_level=%d, scanmask=0x%x",
qnostr, relationObjectId,
get_rel_name(relationObjectId),
- inhparent, current_hint, nested_level,
+ inhparent, current_hint, hint_inhibit_level,
hint->enforce_mask)));
}
else
errmsg ("pg_hint_plan%s: get_relation_info"
" no hint applied:"
" relation=%u(%s), inhparent=%d, current_hint=%p,"
- " nested_level=%d, scanmask=0x%x",
+ " hint_inhibit_level=%d, scanmask=0x%x",
qnostr, relationObjectId,
get_rel_name(relationObjectId),
- inhparent, current_hint, nested_level,
+ inhparent, current_hint, hint_inhibit_level,
current_hint->init_scan_mask)));
set_scan_config_options(current_hint->init_scan_mask,
current_hint->context);
* valid hint is supplied or current nesting depth is nesting depth of SPI
* calls.
*/
- if (!current_hint || nested_level > 0)
+ if (!current_hint || hint_inhibit_level > 0)
{
if (prev_join_search)
return (*prev_join_search) (root, levels_needed, initial_rels);
static void
pg_hint_plan_plpgsql_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
{
- 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_query_string = expr->query;
- plpgsql_query_string_src = (enum PLpgSQL_stmt_types) stmt->cmd_type;
- }
+ plpgsql_recurse_level++;
}
/*
static void
pg_hint_plan_plpgsql_stmt_end(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
{
- if (plpgsql_query_string &&
- plpgsql_query_string_src == stmt->cmd_type)
- plpgsql_query_string = NULL;
+ plpgsql_recurse_level--;
}
void plpgsql_query_erase_callback(ResourceReleasePhase phase,
{
if (phase != RESOURCE_RELEASE_AFTER_LOCKS)
return;
- /* Force erase stored plpgsql query string */
- plpgsql_query_string = NULL;
+ /* Cancel plpgsql nest level*/
+ plpgsql_recurse_level = 0;
}
#define standard_join_search pg_hint_plan_standard_join_search
-- plpgsql test
EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = 1;
-DO LANGUAGE plpgsql $$
+
+-- static function
+CREATE FUNCTION testfunc() RETURNS RECORD AS $$
+DECLARE
+ ret record;
+BEGIN
+ SELECT /*+ SeqScan(t1) */ * INTO ret FROM t1 LIMIT 1;
+ RETURN ret;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- dynamic function
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS void AS $$
+BEGIN
+ EXECUTE format('/*+ SeqScan(t1) */ SELECT * FROM t1');
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- This should not use SeqScan(t1)
+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
+
+-- Perform
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS void AS $$
+BEGIN
+ PERFORM 1, /*+ SeqScan(t1) */ * from t1;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+ sum int;
+ v int;
+BEGIN
+ sum := 0;
+ FOR v IN SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id LOOP
+ sum := sum + v;
+ END LOOP;
+ RETURN v;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- Dynamic FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
DECLARE
- id integer;
+ sum int;
+ v int;
+ i int;
+BEGIN
+ sum := 0;
+ FOR v IN EXECUTE 'SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id' LOOP
+ sum := sum + v;
+ END LOOP;
+ RETURN v;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- Cursor FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+ ref CURSOR FOR SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
+ rec record;
+ sum int := 0;
+BEGIN
+ FOR rec IN ref LOOP
+ sum := sum + rec.val;
+ END LOOP;
+ RETURN sum;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- RETURN QUERY
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
BEGIN
- SELECT /*+SeqScan(t1)*/ t1.id INTO id FROM t1 WHERE t1.id = 1;
- RETURN;
+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
END;
-$$;
+$$ LANGUAGE plpgsql;
+SELECT * FROM testfunc() LIMIT 1;
+
+-- Test for error exit from inner SQL statement.
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
+BEGIN
+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id;
+END;
+$$ LANGUAGE plpgsql;
+SELECT * FROM testfunc() LIMIT 1;
+
+-- this should not use SeqScan(t1) hint.
+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
+
+DROP FUNCTION testfunc();
DROP EXTENSION pg_hint_plan;
--
OSDN Git Service
