• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: コミット

firtst release


コミットメタ情報

リビジョン2c739758bc6473fd22cf9fd6753106e08377be93 (tree)
日時2014-12-22 20:44:42
作者Kyotaro Horiguchi <horiguchi.kyotaro@lab....>
コミッターKyotaro Horiguchi

ログメッセージ

Fixed a bug related to SQL statements in PL/pgSQL functions.

At least since 9.1, true query strings of SQL statements executed in
PL/pgSQL functions were found that were not obtained correctly by the
previous implement, it is because that PLpgSQL_stmt variable did not
have proper query string to be read for hints. Instead, it is changed
to read them from the top of error_context_stack in
pg_hint_plan_planner(). This change made a slight difference in
behavior which doesn't affect its work so a part of the regtest was
also changed. And added some regression tests for it.

変更サマリ

差分

--- a/expected/pg_hint_plan.out
+++ b/expected/pg_hint_plan.out
@@ -7943,14 +7943,16 @@ EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = 1;
79437943 Index Cond: (id = 1)
79447944 (2 rows)
79457945
7946-DO LANGUAGE plpgsql $$
7946+-- static function
7947+CREATE FUNCTION testfunc() RETURNS RECORD AS $$
79477948 DECLARE
7948- id integer;
7949+ ret record;
79497950 BEGIN
7950- SELECT /*+SeqScan(t1)*/ t1.id INTO id FROM t1 WHERE t1.id = 1;
7951- RETURN;
7951+ SELECT /*+ SeqScan(t1) */ * INTO ret FROM t1 LIMIT 1;
7952+ RETURN ret;
79527953 END;
7953-$$;
7954+$$ LANGUAGE plpgsql;
7955+SELECT testfunc();
79547956 LOG: pg_hint_plan:
79557957 used hint:
79567958 SeqScan(t1)
@@ -7958,8 +7960,209 @@ not used hint:
79587960 duplication hint:
79597961 error hint:
79607962
7961-CONTEXT: SQL statement "SELECT /*+SeqScan(t1)*/ t1.id FROM t1 WHERE t1.id = 1"
7962-PL/pgSQL function inline_code_block line 5 at SQL statement
7963+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 LIMIT 1"
7964+PL/pgSQL function testfunc() line 5 at SQL statement
7965+ testfunc
7966+----------
7967+ (1,1)
7968+(1 row)
7969+
7970+-- dynamic function
7971+DROP FUNCTION testfunc();
7972+CREATE FUNCTION testfunc() RETURNS void AS $$
7973+BEGIN
7974+ EXECUTE format('/*+ SeqScan(t1) */ SELECT * FROM t1');
7975+END;
7976+$$ LANGUAGE plpgsql;
7977+SELECT testfunc();
7978+LOG: pg_hint_plan:
7979+used hint:
7980+SeqScan(t1)
7981+not used hint:
7982+duplication hint:
7983+error hint:
7984+
7985+CONTEXT: SQL statement "/*+ SeqScan(t1) */ SELECT * FROM t1"
7986+PL/pgSQL function testfunc() line 3 at EXECUTE statement
7987+ testfunc
7988+----------
7989+
7990+(1 row)
7991+
7992+-- This should not use SeqScan(t1)
7993+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
7994+LOG: pg_hint_plan:
7995+used hint:
7996+IndexScan(t1)
7997+not used hint:
7998+duplication hint:
7999+error hint:
8000+
8001+ id | val
8002+----+-----
8003+ 1 | 1
8004+(1 row)
8005+
8006+-- Perform
8007+DROP FUNCTION testfunc();
8008+CREATE FUNCTION testfunc() RETURNS void AS $$
8009+BEGIN
8010+ PERFORM 1, /*+ SeqScan(t1) */ * from t1;
8011+END;
8012+$$ LANGUAGE plpgsql;
8013+SELECT testfunc();
8014+LOG: pg_hint_plan:
8015+used hint:
8016+SeqScan(t1)
8017+not used hint:
8018+duplication hint:
8019+error hint:
8020+
8021+CONTEXT: SQL statement "SELECT 1, /*+ SeqScan(t1) */ * from t1"
8022+PL/pgSQL function testfunc() line 3 at PERFORM
8023+ testfunc
8024+----------
8025+
8026+(1 row)
8027+
8028+-- FOR loop
8029+DROP FUNCTION testfunc();
8030+CREATE FUNCTION testfunc() RETURNS int AS $$
8031+DECLARE
8032+ sum int;
8033+ v int;
8034+BEGIN
8035+ sum := 0;
8036+ FOR v IN SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id LOOP
8037+ sum := sum + v;
8038+ END LOOP;
8039+ RETURN v;
8040+END;
8041+$$ LANGUAGE plpgsql;
8042+SELECT testfunc();
8043+LOG: pg_hint_plan:
8044+used hint:
8045+SeqScan(t1)
8046+not used hint:
8047+duplication hint:
8048+error hint:
8049+
8050+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id"
8051+PL/pgSQL function testfunc() line 7 at FOR over SELECT rows
8052+ testfunc
8053+----------
8054+
8055+(1 row)
8056+
8057+-- Dynamic FOR loop
8058+DROP FUNCTION testfunc();
8059+CREATE FUNCTION testfunc() RETURNS int AS $$
8060+DECLARE
8061+ sum int;
8062+ v int;
8063+ i int;
8064+BEGIN
8065+ sum := 0;
8066+ FOR v IN EXECUTE 'SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id' LOOP
8067+ sum := sum + v;
8068+ END LOOP;
8069+ RETURN v;
8070+END;
8071+$$ LANGUAGE plpgsql;
8072+SELECT testfunc();
8073+LOG: pg_hint_plan:
8074+used hint:
8075+SeqScan(t1)
8076+not used hint:
8077+duplication hint:
8078+error hint:
8079+
8080+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id"
8081+PL/pgSQL function testfunc() line 8 at FOR over EXECUTE statement
8082+ testfunc
8083+----------
8084+ 0
8085+(1 row)
8086+
8087+-- Cursor FOR loop
8088+DROP FUNCTION testfunc();
8089+CREATE FUNCTION testfunc() RETURNS int AS $$
8090+DECLARE
8091+ ref CURSOR FOR SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
8092+ rec record;
8093+ sum int := 0;
8094+BEGIN
8095+ FOR rec IN ref LOOP
8096+ sum := sum + rec.val;
8097+ END LOOP;
8098+ RETURN sum;
8099+END;
8100+$$ LANGUAGE plpgsql;
8101+SELECT testfunc();
8102+LOG: pg_hint_plan:
8103+used hint:
8104+SeqScan(t1)
8105+not used hint:
8106+duplication hint:
8107+error hint:
8108+
8109+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id"
8110+PL/pgSQL function testfunc() line 7 at FOR over cursor
8111+ testfunc
8112+----------
8113+ 495000
8114+(1 row)
8115+
8116+-- RETURN QUERY
8117+DROP FUNCTION testfunc();
8118+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
8119+BEGIN
8120+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
8121+END;
8122+$$ LANGUAGE plpgsql;
8123+SELECT * FROM testfunc() LIMIT 1;
8124+LOG: pg_hint_plan:
8125+used hint:
8126+SeqScan(t1)
8127+not used hint:
8128+duplication hint:
8129+error hint:
8130+
8131+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id"
8132+PL/pgSQL function testfunc() line 3 at RETURN QUERY
8133+ id | val
8134+----+-----
8135+ 1 | 1
8136+(1 row)
8137+
8138+-- Test for error exit from inner SQL statement.
8139+DROP FUNCTION testfunc();
8140+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
8141+BEGIN
8142+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id;
8143+END;
8144+$$ LANGUAGE plpgsql;
8145+SELECT * FROM testfunc() LIMIT 1;
8146+ERROR: relation "ttx" does not exist
8147+LINE 1: SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id
8148+ ^
8149+QUERY: SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id
8150+CONTEXT: PL/pgSQL function testfunc() line 3 at RETURN QUERY
8151+-- this should not use SeqScan(t1) hint.
8152+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
8153+LOG: pg_hint_plan:
8154+used hint:
8155+IndexScan(t1)
8156+not used hint:
8157+duplication hint:
8158+error hint:
8159+
8160+ id | val
8161+----+-----
8162+ 1 | 1
8163+(1 row)
8164+
8165+DROP FUNCTION testfunc();
79638166 DROP EXTENSION pg_hint_plan;
79648167 --
79658168 -- Rows hint tests
--- a/expected/ut-A.out
+++ b/expected/ut-A.out
@@ -4258,37 +4258,7 @@ SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1)
42584258 ORDER BY t_1.c1 LIMIT 1"
42594259 PL/pgSQL function nested_planner(integer) line 12 at SQL statement
42604260 LOG: pg_hint_plan:
4261-used hint:
4262-not used hint:
4263-IndexScan(t_1)
4264-duplication hint:
4265-error hint:
4266-
4267-CONTEXT: SQL statement "SELECT 0"
4268-PL/pgSQL function nested_planner(integer) line 9 at RETURN
4269-SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1
4270- JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4271- ORDER BY t_1.c1 LIMIT 1"
4272-PL/pgSQL function nested_planner(integer) line 12 at SQL statement
4273-SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1
4274- JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4275- ORDER BY t_1.c1 LIMIT 1"
4276-PL/pgSQL function nested_planner(integer) line 12 at SQL statement
4277-SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1
4278- JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4279- ORDER BY t_1.c1 LIMIT 1"
4280-PL/pgSQL function nested_planner(integer) line 12 at SQL statement
4281-SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1
4282- JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4283- ORDER BY t_1.c1 LIMIT 1"
4284-PL/pgSQL function nested_planner(integer) line 12 at SQL statement
4285-LOG: pg_hint_plan:
4286-used hint:
4287-IndexScan(t_1)
4288-not used hint:
4289-duplication hint:
4290-error hint:
4291-
4261+no hint
42924262 CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1
42934263 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
42944264 ORDER BY t_1.c1 LIMIT 1"
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -432,7 +432,13 @@ static int debug_level = 0;
432432 static int pg_hint_plan_message_level = INFO;
433433 /* Default is off, to keep backward compatibility. */
434434 static bool pg_hint_plan_enable_hint_table = false;
435-static bool hidestmt = false;
435+
436+/* Internal static variables. */
437+static bool hidestmt = false; /* Allow or inhibit STATEMENT: output */
438+
439+static int plpgsql_recurse_level = 0; /* PLpgSQL recursion level */
440+static int hint_inhibit_level = 0; /* Inhibit hinting if this is above 0 */
441+ /* (This could not be above 1) */
436442
437443 static const struct config_enum_entry parse_messages_level_options[] = {
438444 {"debug", DEBUG2, true},
@@ -518,13 +524,6 @@ static const HintParser parsers[] = {
518524 {NULL, NULL, HINT_KEYWORD_UNRECOGNIZED}
519525 };
520526
521-/*
522- * PL/pgSQL plugin for retrieving string representation of each query during
523- * function execution.
524- */
525-static const char *plpgsql_query_string = NULL;
526-static enum PLpgSQL_stmt_types plpgsql_query_string_src;
527-
528527 PLpgSQL_plugin plugin_funcs = {
529528 NULL,
530529 NULL,
@@ -535,9 +534,6 @@ PLpgSQL_plugin plugin_funcs = {
535534 NULL,
536535 };
537536
538-/* Current nesting depth of SPI calls, used to prevent recursive calls */
539-static int nested_level = 0;
540-
541537 /*
542538 * Module load callbacks
543539 */
@@ -1126,7 +1122,7 @@ HintStateDump2(HintState *hstate)
11261122 if (!hstate)
11271123 {
11281124 elog(pg_hint_plan_message_level,
1129- "pg_hint_plan%s: HintStateDump:\nno hint", qnostr);
1125+ "pg_hint_plan%s: HintStateDump: no hint", qnostr);
11301126 return;
11311127 }
11321128
@@ -1589,7 +1585,7 @@ get_hints_from_table(const char *client_query, const char *client_application)
15891585
15901586 PG_TRY();
15911587 {
1592- ++nested_level;
1588+ hint_inhibit_level++;
15931589
15941590 SPI_connect();
15951591
@@ -1627,11 +1623,11 @@ get_hints_from_table(const char *client_query, const char *client_application)
16271623
16281624 SPI_finish();
16291625
1630- --nested_level;
1626+ hint_inhibit_level--;
16311627 }
16321628 PG_CATCH();
16331629 {
1634- --nested_level;
1630+ hint_inhibit_level--;
16351631 PG_RE_THROW();
16361632 }
16371633 PG_END_TRY();
@@ -1647,15 +1643,21 @@ get_query_string(void)
16471643 {
16481644 const char *p;
16491645
1650- if (stmt_name)
1646+ if (plpgsql_recurse_level > 0)
1647+ {
1648+ /*
1649+ * This is quite ugly but this is the only point I could find where
1650+ * we can get the query string.
1651+ */
1652+ p = (char*)error_context_stack->arg;
1653+ }
1654+ else if (stmt_name)
16511655 {
16521656 PreparedStatement *entry;
16531657
16541658 entry = FetchPreparedStatement(stmt_name, true);
16551659 p = entry->plansource->query_string;
16561660 }
1657- else if (plpgsql_query_string)
1658- p = plpgsql_query_string;
16591661 else
16601662 p = debug_query_string;
16611663
@@ -2319,7 +2321,7 @@ pg_hint_plan_ProcessUtility(Node *parsetree, const char *queryString,
23192321 * Use standard planner if pg_hint_plan is disabled or current nesting
23202322 * depth is nesting depth of SPI calls.
23212323 */
2322- if (!pg_hint_plan_enable_hint || nested_level > 0)
2324+ if (!pg_hint_plan_enable_hint || hint_inhibit_level > 0)
23232325 {
23242326 if (debug_level > 1)
23252327 ereport(pg_hint_plan_message_level,
@@ -2487,13 +2489,13 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
24872489 * depth is nesting depth of SPI calls. Other hook functions try to change
24882490 * plan with current_hint if any, so set it to NULL.
24892491 */
2490- if (!pg_hint_plan_enable_hint || nested_level > 0)
2492+ if (!pg_hint_plan_enable_hint || hint_inhibit_level > 0)
24912493 {
24922494 if (debug_level > 1)
24932495 elog(pg_hint_plan_message_level,
24942496 "pg_hint_plan%s: planner: enable_hint=%d,"
2495- " nested_level=%d",
2496- qnostr, pg_hint_plan_enable_hint, nested_level);
2497+ " hint_inhibit_level=%d",
2498+ qnostr, pg_hint_plan_enable_hint, hint_inhibit_level);
24972499 hidestmt = true;
24982500
24992501 goto standard_planner_proc;
@@ -3095,17 +3097,17 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
30953097 * Do nothing if we don't have a valid hint in this context or current
30963098 * nesting depth is at SPI calls.
30973099 */
3098- if (!current_hint || nested_level > 0)
3100+ if (!current_hint || hint_inhibit_level > 0)
30993101 {
31003102 if (debug_level > 1)
31013103 ereport(pg_hint_plan_message_level,
31023104 (errhidestmt(true),
31033105 errmsg ("pg_hint_plan%s: get_relation_info"
31043106 " no hint to apply: relation=%u(%s), inhparent=%d,"
3105- " current_hint=%p, nested_level=%d",
3107+ " current_hint=%p, hint_inhibit_level=%d",
31063108 qnostr, relationObjectId,
31073109 get_rel_name(relationObjectId),
3108- inhparent, current_hint, nested_level)));
3110+ inhparent, current_hint, hint_inhibit_level)));
31093111 return;
31103112 }
31113113
@@ -3123,10 +3125,10 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
31233125 (errhidestmt(true),
31243126 errmsg ("pg_hint_plan%s: get_relation_info"
31253127 " skipping inh parent: relation=%u(%s), inhparent=%d,"
3126- " current_hint=%p, nested_level=%d",
3128+ " current_hint=%p, hint_inhibit_level=%d",
31273129 qnostr, relationObjectId,
31283130 get_rel_name(relationObjectId),
3129- inhparent, current_hint, nested_level)));
3131+ inhparent, current_hint, hint_inhibit_level)));
31303132 return;
31313133 }
31323134
@@ -3225,10 +3227,10 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
32253227 errmsg("pg_hint_plan%s: get_relation_info:"
32263228 " index deletion by parent hint: "
32273229 "relation=%u(%s), inhparent=%d, current_hint=%p,"
3228- " nested_level=%d",
3230+ " hint_inhibit_level=%d",
32293231 qnostr, relationObjectId,
32303232 get_rel_name(relationObjectId),
3231- inhparent, current_hint, nested_level)));
3233+ inhparent, current_hint, hint_inhibit_level)));
32323234 return;
32333235 }
32343236
@@ -3246,10 +3248,10 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
32463248 errmsg ("pg_hint_plan%s: get_relation_info"
32473249 " index deletion:"
32483250 " relation=%u(%s), inhparent=%d, current_hint=%p,"
3249- " nested_level=%d, scanmask=0x%x",
3251+ " hint_inhibit_level=%d, scanmask=0x%x",
32503252 qnostr, relationObjectId,
32513253 get_rel_name(relationObjectId),
3252- inhparent, current_hint, nested_level,
3254+ inhparent, current_hint, hint_inhibit_level,
32533255 hint->enforce_mask)));
32543256 }
32553257 else
@@ -3260,10 +3262,10 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
32603262 errmsg ("pg_hint_plan%s: get_relation_info"
32613263 " no hint applied:"
32623264 " relation=%u(%s), inhparent=%d, current_hint=%p,"
3263- " nested_level=%d, scanmask=0x%x",
3265+ " hint_inhibit_level=%d, scanmask=0x%x",
32643266 qnostr, relationObjectId,
32653267 get_rel_name(relationObjectId),
3266- inhparent, current_hint, nested_level,
3268+ inhparent, current_hint, hint_inhibit_level,
32673269 current_hint->init_scan_mask)));
32683270 set_scan_config_options(current_hint->init_scan_mask,
32693271 current_hint->context);
@@ -3933,7 +3935,7 @@ pg_hint_plan_join_search(PlannerInfo *root, int levels_needed,
39333935 * valid hint is supplied or current nesting depth is nesting depth of SPI
39343936 * calls.
39353937 */
3936- if (!current_hint || nested_level > 0)
3938+ if (!current_hint || hint_inhibit_level > 0)
39373939 {
39383940 if (prev_join_search)
39393941 return (*prev_join_search) (root, levels_needed, initial_rels);
@@ -4029,48 +4031,7 @@ set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
40294031 static void
40304032 pg_hint_plan_plpgsql_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
40314033 {
4032- PLpgSQL_expr *expr = NULL;
4033-
4034- switch ((enum PLpgSQL_stmt_types) stmt->cmd_type)
4035- {
4036- case PLPGSQL_STMT_FORS:
4037- expr = ((PLpgSQL_stmt_fors *) stmt)->query;
4038- break;
4039- case PLPGSQL_STMT_FORC:
4040- expr = ((PLpgSQL_var *) (estate->datums[((PLpgSQL_stmt_forc *)stmt)->curvar]))->cursor_explicit_expr;
4041- break;
4042- case PLPGSQL_STMT_RETURN_QUERY:
4043- if (((PLpgSQL_stmt_return_query *) stmt)->query != NULL)
4044- expr = ((PLpgSQL_stmt_return_query *) stmt)->query;
4045- else
4046- expr = ((PLpgSQL_stmt_return_query *) stmt)->dynquery;
4047- break;
4048- case PLPGSQL_STMT_EXECSQL:
4049- expr = ((PLpgSQL_stmt_execsql *) stmt)->sqlstmt;
4050- break;
4051- case PLPGSQL_STMT_DYNEXECUTE:
4052- expr = ((PLpgSQL_stmt_dynexecute *) stmt)->query;
4053- break;
4054- case PLPGSQL_STMT_DYNFORS:
4055- expr = ((PLpgSQL_stmt_dynfors *) stmt)->query;
4056- break;
4057- case PLPGSQL_STMT_OPEN:
4058- if (((PLpgSQL_stmt_open *) stmt)->query != NULL)
4059- expr = ((PLpgSQL_stmt_open *) stmt)->query;
4060- else if (((PLpgSQL_stmt_open *) stmt)->dynquery != NULL)
4061- expr = ((PLpgSQL_stmt_open *) stmt)->dynquery;
4062- else
4063- expr = ((PLpgSQL_var *) (estate->datums[((PLpgSQL_stmt_open *)stmt)->curvar]))->cursor_explicit_expr;
4064- break;
4065- default:
4066- break;
4067- }
4068-
4069- if (expr)
4070- {
4071- plpgsql_query_string = expr->query;
4072- plpgsql_query_string_src = (enum PLpgSQL_stmt_types) stmt->cmd_type;
4073- }
4034+ plpgsql_recurse_level++;
40744035 }
40754036
40764037 /*
@@ -4081,9 +4042,7 @@ pg_hint_plan_plpgsql_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
40814042 static void
40824043 pg_hint_plan_plpgsql_stmt_end(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
40834044 {
4084- if (plpgsql_query_string &&
4085- plpgsql_query_string_src == stmt->cmd_type)
4086- plpgsql_query_string = NULL;
4045+ plpgsql_recurse_level--;
40874046 }
40884047
40894048 void plpgsql_query_erase_callback(ResourceReleasePhase phase,
@@ -4093,8 +4052,8 @@ void plpgsql_query_erase_callback(ResourceReleasePhase phase,
40934052 {
40944053 if (phase != RESOURCE_RELEASE_AFTER_LOCKS)
40954054 return;
4096- /* Force erase stored plpgsql query string */
4097- plpgsql_query_string = NULL;
4055+ /* Cancel plpgsql nest level*/
4056+ plpgsql_recurse_level = 0;
40984057 }
40994058
41004059 #define standard_join_search pg_hint_plan_standard_join_search
--- a/sql/pg_hint_plan.sql
+++ b/sql/pg_hint_plan.sql
@@ -836,14 +836,110 @@ VACUUM ANALYZE hint_plan.hints;
836836
837837 -- plpgsql test
838838 EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = 1;
839-DO LANGUAGE plpgsql $$
839+
840+-- static function
841+CREATE FUNCTION testfunc() RETURNS RECORD AS $$
842+DECLARE
843+ ret record;
844+BEGIN
845+ SELECT /*+ SeqScan(t1) */ * INTO ret FROM t1 LIMIT 1;
846+ RETURN ret;
847+END;
848+$$ LANGUAGE plpgsql;
849+SELECT testfunc();
850+
851+-- dynamic function
852+DROP FUNCTION testfunc();
853+CREATE FUNCTION testfunc() RETURNS void AS $$
854+BEGIN
855+ EXECUTE format('/*+ SeqScan(t1) */ SELECT * FROM t1');
856+END;
857+$$ LANGUAGE plpgsql;
858+SELECT testfunc();
859+
860+-- This should not use SeqScan(t1)
861+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
862+
863+-- Perform
864+DROP FUNCTION testfunc();
865+CREATE FUNCTION testfunc() RETURNS void AS $$
866+BEGIN
867+ PERFORM 1, /*+ SeqScan(t1) */ * from t1;
868+END;
869+$$ LANGUAGE plpgsql;
870+SELECT testfunc();
871+
872+-- FOR loop
873+DROP FUNCTION testfunc();
874+CREATE FUNCTION testfunc() RETURNS int AS $$
875+DECLARE
876+ sum int;
877+ v int;
878+BEGIN
879+ sum := 0;
880+ FOR v IN SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id LOOP
881+ sum := sum + v;
882+ END LOOP;
883+ RETURN v;
884+END;
885+$$ LANGUAGE plpgsql;
886+SELECT testfunc();
887+
888+-- Dynamic FOR loop
889+DROP FUNCTION testfunc();
890+CREATE FUNCTION testfunc() RETURNS int AS $$
840891 DECLARE
841- id integer;
892+ sum int;
893+ v int;
894+ i int;
895+BEGIN
896+ sum := 0;
897+ FOR v IN EXECUTE 'SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id' LOOP
898+ sum := sum + v;
899+ END LOOP;
900+ RETURN v;
901+END;
902+$$ LANGUAGE plpgsql;
903+SELECT testfunc();
904+
905+-- Cursor FOR loop
906+DROP FUNCTION testfunc();
907+CREATE FUNCTION testfunc() RETURNS int AS $$
908+DECLARE
909+ ref CURSOR FOR SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
910+ rec record;
911+ sum int := 0;
912+BEGIN
913+ FOR rec IN ref LOOP
914+ sum := sum + rec.val;
915+ END LOOP;
916+ RETURN sum;
917+END;
918+$$ LANGUAGE plpgsql;
919+SELECT testfunc();
920+
921+-- RETURN QUERY
922+DROP FUNCTION testfunc();
923+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
842924 BEGIN
843- SELECT /*+SeqScan(t1)*/ t1.id INTO id FROM t1 WHERE t1.id = 1;
844- RETURN;
925+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
845926 END;
846-$$;
927+$$ LANGUAGE plpgsql;
928+SELECT * FROM testfunc() LIMIT 1;
929+
930+-- Test for error exit from inner SQL statement.
931+DROP FUNCTION testfunc();
932+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
933+BEGIN
934+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id;
935+END;
936+$$ LANGUAGE plpgsql;
937+SELECT * FROM testfunc() LIMIT 1;
938+
939+-- this should not use SeqScan(t1) hint.
940+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
941+
942+DROP FUNCTION testfunc();
847943 DROP EXTENSION pg_hint_plan;
848944
849945 --
旧リポジトリブラウザで表示