• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revisionb715e7a19a83f6ecb7d6b7e7aeedec6393525011 (tree)
Time2014-12-22 20:46:04
AuthorKyotaro Horiguchi <horiguchi.kyotaro@lab....>
CommiterKyotaro Horiguchi

Log Message

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.

Change Summary

Incremental Difference

--- a/expected/pg_hint_plan.out
+++ b/expected/pg_hint_plan.out
@@ -7975,14 +7975,16 @@ EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = 1;
79757975 Index Cond: (id = 1)
79767976 (2 rows)
79777977
7978-DO LANGUAGE plpgsql $$
7978+-- static function
7979+CREATE FUNCTION testfunc() RETURNS RECORD AS $$
79797980 DECLARE
7980- id integer;
7981+ ret record;
79817982 BEGIN
7982- SELECT /*+SeqScan(t1)*/ t1.id INTO id FROM t1 WHERE t1.id = 1;
7983- RETURN;
7983+ SELECT /*+ SeqScan(t1) */ * INTO ret FROM t1 LIMIT 1;
7984+ RETURN ret;
79847985 END;
7985-$$;
7986+$$ LANGUAGE plpgsql;
7987+SELECT testfunc();
79867988 LOG: pg_hint_plan:
79877989 used hint:
79887990 SeqScan(t1)
@@ -7990,8 +7992,209 @@ not used hint:
79907992 duplication hint:
79917993 error hint:
79927994
7993-CONTEXT: SQL statement "SELECT /*+SeqScan(t1)*/ t1.id FROM t1 WHERE t1.id = 1"
7994-PL/pgSQL function inline_code_block line 5 at SQL statement
7995+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 LIMIT 1"
7996+PL/pgSQL function testfunc() line 5 at SQL statement
7997+ testfunc
7998+----------
7999+ (1,1)
8000+(1 row)
8001+
8002+-- dynamic function
8003+DROP FUNCTION testfunc();
8004+CREATE FUNCTION testfunc() RETURNS void AS $$
8005+BEGIN
8006+ EXECUTE format('/*+ SeqScan(t1) */ SELECT * FROM t1');
8007+END;
8008+$$ LANGUAGE plpgsql;
8009+SELECT testfunc();
8010+LOG: pg_hint_plan:
8011+used hint:
8012+SeqScan(t1)
8013+not used hint:
8014+duplication hint:
8015+error hint:
8016+
8017+CONTEXT: SQL statement "/*+ SeqScan(t1) */ SELECT * FROM t1"
8018+PL/pgSQL function testfunc() line 3 at EXECUTE statement
8019+ testfunc
8020+----------
8021+
8022+(1 row)
8023+
8024+-- This should not use SeqScan(t1)
8025+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
8026+LOG: pg_hint_plan:
8027+used hint:
8028+IndexScan(t1)
8029+not used hint:
8030+duplication hint:
8031+error hint:
8032+
8033+ id | val
8034+----+-----
8035+ 1 | 1
8036+(1 row)
8037+
8038+-- Perform
8039+DROP FUNCTION testfunc();
8040+CREATE FUNCTION testfunc() RETURNS void AS $$
8041+BEGIN
8042+ PERFORM 1, /*+ SeqScan(t1) */ * from t1;
8043+END;
8044+$$ LANGUAGE plpgsql;
8045+SELECT testfunc();
8046+LOG: pg_hint_plan:
8047+used hint:
8048+SeqScan(t1)
8049+not used hint:
8050+duplication hint:
8051+error hint:
8052+
8053+CONTEXT: SQL statement "SELECT 1, /*+ SeqScan(t1) */ * from t1"
8054+PL/pgSQL function testfunc() line 3 at PERFORM
8055+ testfunc
8056+----------
8057+
8058+(1 row)
8059+
8060+-- FOR loop
8061+DROP FUNCTION testfunc();
8062+CREATE FUNCTION testfunc() RETURNS int AS $$
8063+DECLARE
8064+ sum int;
8065+ v int;
8066+BEGIN
8067+ sum := 0;
8068+ FOR v IN SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id LOOP
8069+ sum := sum + v;
8070+ END LOOP;
8071+ RETURN v;
8072+END;
8073+$$ LANGUAGE plpgsql;
8074+SELECT testfunc();
8075+LOG: pg_hint_plan:
8076+used hint:
8077+SeqScan(t1)
8078+not used hint:
8079+duplication hint:
8080+error hint:
8081+
8082+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id"
8083+PL/pgSQL function testfunc() line 7 at FOR over SELECT rows
8084+ testfunc
8085+----------
8086+
8087+(1 row)
8088+
8089+-- Dynamic FOR loop
8090+DROP FUNCTION testfunc();
8091+CREATE FUNCTION testfunc() RETURNS int AS $$
8092+DECLARE
8093+ sum int;
8094+ v int;
8095+ i int;
8096+BEGIN
8097+ sum := 0;
8098+ FOR v IN EXECUTE 'SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id' LOOP
8099+ sum := sum + v;
8100+ END LOOP;
8101+ RETURN v;
8102+END;
8103+$$ LANGUAGE plpgsql;
8104+SELECT testfunc();
8105+LOG: pg_hint_plan:
8106+used hint:
8107+SeqScan(t1)
8108+not used hint:
8109+duplication hint:
8110+error hint:
8111+
8112+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id"
8113+PL/pgSQL function testfunc() line 8 at FOR over EXECUTE statement
8114+ testfunc
8115+----------
8116+ 0
8117+(1 row)
8118+
8119+-- Cursor FOR loop
8120+DROP FUNCTION testfunc();
8121+CREATE FUNCTION testfunc() RETURNS int AS $$
8122+DECLARE
8123+ ref CURSOR FOR SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
8124+ rec record;
8125+ sum int := 0;
8126+BEGIN
8127+ FOR rec IN ref LOOP
8128+ sum := sum + rec.val;
8129+ END LOOP;
8130+ RETURN sum;
8131+END;
8132+$$ LANGUAGE plpgsql;
8133+SELECT testfunc();
8134+LOG: pg_hint_plan:
8135+used hint:
8136+SeqScan(t1)
8137+not used hint:
8138+duplication hint:
8139+error hint:
8140+
8141+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id"
8142+PL/pgSQL function testfunc() line 7 at FOR over cursor
8143+ testfunc
8144+----------
8145+ 495000
8146+(1 row)
8147+
8148+-- RETURN QUERY
8149+DROP FUNCTION testfunc();
8150+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
8151+BEGIN
8152+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
8153+END;
8154+$$ LANGUAGE plpgsql;
8155+SELECT * FROM testfunc() LIMIT 1;
8156+LOG: pg_hint_plan:
8157+used hint:
8158+SeqScan(t1)
8159+not used hint:
8160+duplication hint:
8161+error hint:
8162+
8163+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id"
8164+PL/pgSQL function testfunc() line 3 at RETURN QUERY
8165+ id | val
8166+----+-----
8167+ 1 | 1
8168+(1 row)
8169+
8170+-- Test for error exit from inner SQL statement.
8171+DROP FUNCTION testfunc();
8172+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
8173+BEGIN
8174+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id;
8175+END;
8176+$$ LANGUAGE plpgsql;
8177+SELECT * FROM testfunc() LIMIT 1;
8178+ERROR: relation "ttx" does not exist
8179+LINE 1: SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id
8180+ ^
8181+QUERY: SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id
8182+CONTEXT: PL/pgSQL function testfunc() line 3 at RETURN QUERY
8183+-- this should not use SeqScan(t1) hint.
8184+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
8185+LOG: pg_hint_plan:
8186+used hint:
8187+IndexScan(t1)
8188+not used hint:
8189+duplication hint:
8190+error hint:
8191+
8192+ id | val
8193+----+-----
8194+ 1 | 1
8195+(1 row)
8196+
8197+DROP FUNCTION testfunc();
79958198 DROP EXTENSION pg_hint_plan;
79968199 --
79978200 -- Rows hint tests
--- a/expected/ut-A.out
+++ b/expected/ut-A.out
@@ -4354,37 +4354,7 @@ SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1)
43544354 ORDER BY t_1.c1 LIMIT 1"
43554355 PL/pgSQL function nested_planner(integer) line 12 at SQL statement
43564356 LOG: pg_hint_plan:
4357-used hint:
4358-not used hint:
4359-IndexScan(t_1)
4360-duplication hint:
4361-error hint:
4362-
4363-CONTEXT: SQL statement "SELECT 0"
4364-PL/pgSQL function nested_planner(integer) line 9 at RETURN
4365-SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1
4366- JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4367- ORDER BY t_1.c1 LIMIT 1"
4368-PL/pgSQL function nested_planner(integer) line 12 at SQL statement
4369-SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1
4370- JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4371- ORDER BY t_1.c1 LIMIT 1"
4372-PL/pgSQL function nested_planner(integer) line 12 at SQL statement
4373-SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1
4374- JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4375- ORDER BY t_1.c1 LIMIT 1"
4376-PL/pgSQL function nested_planner(integer) line 12 at SQL statement
4377-SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1
4378- JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4379- ORDER BY t_1.c1 LIMIT 1"
4380-PL/pgSQL function nested_planner(integer) line 12 at SQL statement
4381-LOG: pg_hint_plan:
4382-used hint:
4383-IndexScan(t_1)
4384-not used hint:
4385-duplication hint:
4386-error hint:
4387-
4357+no hint
43884358 CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1
43894359 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
43904360 ORDER BY t_1.c1 LIMIT 1"
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -442,7 +442,13 @@ static int debug_level = 0;
442442 static int pg_hint_plan_message_level = INFO;
443443 /* Default is off, to keep backward compatibility. */
444444 static bool pg_hint_plan_enable_hint_table = false;
445-static bool hidestmt = false;
445+
446+/* Internal static variables. */
447+static bool hidestmt = false; /* Allow or inhibit STATEMENT: output */
448+
449+static int plpgsql_recurse_level = 0; /* PLpgSQL recursion level */
450+static int hint_inhibit_level = 0; /* Inhibit hinting if this is above 0 */
451+ /* (This could not be above 1) */
446452
447453 static const struct config_enum_entry parse_messages_level_options[] = {
448454 {"debug", DEBUG2, true},
@@ -528,13 +534,6 @@ static const HintParser parsers[] = {
528534 {NULL, NULL, HINT_KEYWORD_UNRECOGNIZED}
529535 };
530536
531-/*
532- * PL/pgSQL plugin for retrieving string representation of each query during
533- * function execution.
534- */
535-static const char *plpgsql_query_string = NULL;
536-static enum PLpgSQL_stmt_types plpgsql_query_string_src;
537-
538537 PLpgSQL_plugin plugin_funcs = {
539538 NULL,
540539 NULL,
@@ -545,9 +544,6 @@ PLpgSQL_plugin plugin_funcs = {
545544 NULL,
546545 };
547546
548-/* Current nesting depth of SPI calls, used to prevent recursive calls */
549-static int nested_level = 0;
550-
551547 /*
552548 * Module load callbacks
553549 */
@@ -1136,7 +1132,7 @@ HintStateDump2(HintState *hstate)
11361132 if (!hstate)
11371133 {
11381134 elog(pg_hint_plan_message_level,
1139- "pg_hint_plan%s: HintStateDump:\nno hint", qnostr);
1135+ "pg_hint_plan%s: HintStateDump: no hint", qnostr);
11401136 return;
11411137 }
11421138
@@ -1599,7 +1595,7 @@ get_hints_from_table(const char *client_query, const char *client_application)
15991595
16001596 PG_TRY();
16011597 {
1602- ++nested_level;
1598+ hint_inhibit_level++;
16031599
16041600 SPI_connect();
16051601
@@ -1637,11 +1633,11 @@ get_hints_from_table(const char *client_query, const char *client_application)
16371633
16381634 SPI_finish();
16391635
1640- --nested_level;
1636+ hint_inhibit_level--;
16411637 }
16421638 PG_CATCH();
16431639 {
1644- --nested_level;
1640+ hint_inhibit_level--;
16451641 PG_RE_THROW();
16461642 }
16471643 PG_END_TRY();
@@ -1657,15 +1653,21 @@ get_query_string(void)
16571653 {
16581654 const char *p;
16591655
1660- if (stmt_name)
1656+ if (plpgsql_recurse_level > 0)
1657+ {
1658+ /*
1659+ * This is quite ugly but this is the only point I could find where
1660+ * we can get the query string.
1661+ */
1662+ p = (char*)error_context_stack->arg;
1663+ }
1664+ else if (stmt_name)
16611665 {
16621666 PreparedStatement *entry;
16631667
16641668 entry = FetchPreparedStatement(stmt_name, true);
16651669 p = entry->plansource->query_string;
16661670 }
1667- else if (plpgsql_query_string)
1668- p = plpgsql_query_string;
16691671 else
16701672 p = debug_query_string;
16711673
@@ -2329,7 +2331,7 @@ pg_hint_plan_ProcessUtility(Node *parsetree, const char *queryString,
23292331 * Use standard planner if pg_hint_plan is disabled or current nesting
23302332 * depth is nesting depth of SPI calls.
23312333 */
2332- if (!pg_hint_plan_enable_hint || nested_level > 0)
2334+ if (!pg_hint_plan_enable_hint || hint_inhibit_level > 0)
23332335 {
23342336 if (debug_level > 1)
23352337 ereport(pg_hint_plan_message_level,
@@ -2497,13 +2499,13 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
24972499 * depth is nesting depth of SPI calls. Other hook functions try to change
24982500 * plan with current_hint if any, so set it to NULL.
24992501 */
2500- if (!pg_hint_plan_enable_hint || nested_level > 0)
2502+ if (!pg_hint_plan_enable_hint || hint_inhibit_level > 0)
25012503 {
25022504 if (debug_level > 1)
25032505 elog(pg_hint_plan_message_level,
25042506 "pg_hint_plan%s: planner: enable_hint=%d,"
2505- " nested_level=%d",
2506- qnostr, pg_hint_plan_enable_hint, nested_level);
2507+ " hint_inhibit_level=%d",
2508+ qnostr, pg_hint_plan_enable_hint, hint_inhibit_level);
25072509 hidestmt = true;
25082510
25092511 goto standard_planner_proc;
@@ -3105,17 +3107,17 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
31053107 * Do nothing if we don't have a valid hint in this context or current
31063108 * nesting depth is at SPI calls.
31073109 */
3108- if (!current_hint || nested_level > 0)
3110+ if (!current_hint || hint_inhibit_level > 0)
31093111 {
31103112 if (debug_level > 1)
31113113 ereport(pg_hint_plan_message_level,
31123114 (errhidestmt(true),
31133115 errmsg ("pg_hint_plan%s: get_relation_info"
31143116 " no hint to apply: relation=%u(%s), inhparent=%d,"
3115- " current_hint=%p, nested_level=%d",
3117+ " current_hint=%p, hint_inhibit_level=%d",
31163118 qnostr, relationObjectId,
31173119 get_rel_name(relationObjectId),
3118- inhparent, current_hint, nested_level)));
3120+ inhparent, current_hint, hint_inhibit_level)));
31193121 return;
31203122 }
31213123
@@ -3133,10 +3135,10 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
31333135 (errhidestmt(true),
31343136 errmsg ("pg_hint_plan%s: get_relation_info"
31353137 " skipping inh parent: relation=%u(%s), inhparent=%d,"
3136- " current_hint=%p, nested_level=%d",
3138+ " current_hint=%p, hint_inhibit_level=%d",
31373139 qnostr, relationObjectId,
31383140 get_rel_name(relationObjectId),
3139- inhparent, current_hint, nested_level)));
3141+ inhparent, current_hint, hint_inhibit_level)));
31403142 return;
31413143 }
31423144
@@ -3235,10 +3237,10 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
32353237 errmsg("pg_hint_plan%s: get_relation_info:"
32363238 " index deletion by parent hint: "
32373239 "relation=%u(%s), inhparent=%d, current_hint=%p,"
3238- " nested_level=%d",
3240+ " hint_inhibit_level=%d",
32393241 qnostr, relationObjectId,
32403242 get_rel_name(relationObjectId),
3241- inhparent, current_hint, nested_level)));
3243+ inhparent, current_hint, hint_inhibit_level)));
32423244 return;
32433245 }
32443246
@@ -3256,10 +3258,10 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
32563258 errmsg ("pg_hint_plan%s: get_relation_info"
32573259 " index deletion:"
32583260 " relation=%u(%s), inhparent=%d, current_hint=%p,"
3259- " nested_level=%d, scanmask=0x%x",
3261+ " hint_inhibit_level=%d, scanmask=0x%x",
32603262 qnostr, relationObjectId,
32613263 get_rel_name(relationObjectId),
3262- inhparent, current_hint, nested_level,
3264+ inhparent, current_hint, hint_inhibit_level,
32633265 hint->enforce_mask)));
32643266 }
32653267 else
@@ -3270,10 +3272,10 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
32703272 errmsg ("pg_hint_plan%s: get_relation_info"
32713273 " no hint applied:"
32723274 " relation=%u(%s), inhparent=%d, current_hint=%p,"
3273- " nested_level=%d, scanmask=0x%x",
3275+ " hint_inhibit_level=%d, scanmask=0x%x",
32743276 qnostr, relationObjectId,
32753277 get_rel_name(relationObjectId),
3276- inhparent, current_hint, nested_level,
3278+ inhparent, current_hint, hint_inhibit_level,
32773279 current_hint->init_scan_mask)));
32783280 set_scan_config_options(current_hint->init_scan_mask,
32793281 current_hint->context);
@@ -3943,7 +3945,7 @@ pg_hint_plan_join_search(PlannerInfo *root, int levels_needed,
39433945 * valid hint is supplied or current nesting depth is nesting depth of SPI
39443946 * calls.
39453947 */
3946- if (!current_hint || nested_level > 0)
3948+ if (!current_hint || hint_inhibit_level > 0)
39473949 {
39483950 if (prev_join_search)
39493951 return (*prev_join_search) (root, levels_needed, initial_rels);
@@ -4039,48 +4041,7 @@ set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
40394041 static void
40404042 pg_hint_plan_plpgsql_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
40414043 {
4042- PLpgSQL_expr *expr = NULL;
4043-
4044- switch ((enum PLpgSQL_stmt_types) stmt->cmd_type)
4045- {
4046- case PLPGSQL_STMT_FORS:
4047- expr = ((PLpgSQL_stmt_fors *) stmt)->query;
4048- break;
4049- case PLPGSQL_STMT_FORC:
4050- expr = ((PLpgSQL_var *) (estate->datums[((PLpgSQL_stmt_forc *)stmt)->curvar]))->cursor_explicit_expr;
4051- break;
4052- case PLPGSQL_STMT_RETURN_QUERY:
4053- if (((PLpgSQL_stmt_return_query *) stmt)->query != NULL)
4054- expr = ((PLpgSQL_stmt_return_query *) stmt)->query;
4055- else
4056- expr = ((PLpgSQL_stmt_return_query *) stmt)->dynquery;
4057- break;
4058- case PLPGSQL_STMT_EXECSQL:
4059- expr = ((PLpgSQL_stmt_execsql *) stmt)->sqlstmt;
4060- break;
4061- case PLPGSQL_STMT_DYNEXECUTE:
4062- expr = ((PLpgSQL_stmt_dynexecute *) stmt)->query;
4063- break;
4064- case PLPGSQL_STMT_DYNFORS:
4065- expr = ((PLpgSQL_stmt_dynfors *) stmt)->query;
4066- break;
4067- case PLPGSQL_STMT_OPEN:
4068- if (((PLpgSQL_stmt_open *) stmt)->query != NULL)
4069- expr = ((PLpgSQL_stmt_open *) stmt)->query;
4070- else if (((PLpgSQL_stmt_open *) stmt)->dynquery != NULL)
4071- expr = ((PLpgSQL_stmt_open *) stmt)->dynquery;
4072- else
4073- expr = ((PLpgSQL_var *) (estate->datums[((PLpgSQL_stmt_open *)stmt)->curvar]))->cursor_explicit_expr;
4074- break;
4075- default:
4076- break;
4077- }
4078-
4079- if (expr)
4080- {
4081- plpgsql_query_string = expr->query;
4082- plpgsql_query_string_src = (enum PLpgSQL_stmt_types) stmt->cmd_type;
4083- }
4044+ plpgsql_recurse_level++;
40844045 }
40854046
40864047 /*
@@ -4091,9 +4052,7 @@ pg_hint_plan_plpgsql_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
40914052 static void
40924053 pg_hint_plan_plpgsql_stmt_end(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
40934054 {
4094- if (plpgsql_query_string &&
4095- plpgsql_query_string_src == stmt->cmd_type)
4096- plpgsql_query_string = NULL;
4055+ plpgsql_recurse_level--;
40974056 }
40984057
40994058 void plpgsql_query_erase_callback(ResourceReleasePhase phase,
@@ -4103,8 +4062,8 @@ void plpgsql_query_erase_callback(ResourceReleasePhase phase,
41034062 {
41044063 if (phase != RESOURCE_RELEASE_AFTER_LOCKS)
41054064 return;
4106- /* Force erase stored plpgsql query string */
4107- plpgsql_query_string = NULL;
4065+ /* Cancel plpgsql nest level*/
4066+ plpgsql_recurse_level = 0;
41084067 }
41094068
41104069 #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 --
Show on old repository browser