• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revision90830faef0282f475b74f8f841a1415622336bb8 (tree)
Time2014-12-22 20:51:43
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.

Regression tests for PL/pgSQL added.

Tests for defined PL/pgSQL functions are omitted from regression
tests so added it.

Change Summary

Incremental Difference

--- a/expected/pg_hint_plan.out
+++ b/expected/pg_hint_plan.out
@@ -8010,14 +8010,16 @@ EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = 1;
80108010 Index Cond: (id = 1)
80118011 (2 rows)
80128012
8013-DO LANGUAGE plpgsql $$
8013+-- static function
8014+CREATE FUNCTION testfunc() RETURNS RECORD AS $$
80148015 DECLARE
8015- id integer;
8016+ ret record;
80168017 BEGIN
8017- SELECT /*+SeqScan(t1)*/ t1.id INTO id FROM t1 WHERE t1.id = 1;
8018- RETURN;
8018+ SELECT /*+ SeqScan(t1) */ * INTO ret FROM t1 LIMIT 1;
8019+ RETURN ret;
80198020 END;
8020-$$;
8021+$$ LANGUAGE plpgsql;
8022+SELECT testfunc();
80218023 LOG: pg_hint_plan:
80228024 used hint:
80238025 SeqScan(t1)
@@ -8025,8 +8027,209 @@ not used hint:
80258027 duplication hint:
80268028 error hint:
80278029
8028-CONTEXT: SQL statement "SELECT /*+SeqScan(t1)*/ t1.id FROM t1 WHERE t1.id = 1"
8029-PL/pgSQL function inline_code_block line 5 at SQL statement
8030+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 LIMIT 1"
8031+PL/pgSQL function testfunc() line 5 at SQL statement
8032+ testfunc
8033+----------
8034+ (1,1)
8035+(1 row)
8036+
8037+-- dynamic function
8038+DROP FUNCTION testfunc();
8039+CREATE FUNCTION testfunc() RETURNS void AS $$
8040+BEGIN
8041+ EXECUTE format('/*+ SeqScan(t1) */ SELECT * FROM t1');
8042+END;
8043+$$ LANGUAGE plpgsql;
8044+SELECT testfunc();
8045+LOG: pg_hint_plan:
8046+used hint:
8047+SeqScan(t1)
8048+not used hint:
8049+duplication hint:
8050+error hint:
8051+
8052+CONTEXT: SQL statement "/*+ SeqScan(t1) */ SELECT * FROM t1"
8053+PL/pgSQL function testfunc() line 3 at EXECUTE statement
8054+ testfunc
8055+----------
8056+
8057+(1 row)
8058+
8059+-- This should not use SeqScan(t1)
8060+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
8061+LOG: pg_hint_plan:
8062+used hint:
8063+IndexScan(t1)
8064+not used hint:
8065+duplication hint:
8066+error hint:
8067+
8068+ id | val
8069+----+-----
8070+ 1 | 1
8071+(1 row)
8072+
8073+-- Perform
8074+DROP FUNCTION testfunc();
8075+CREATE FUNCTION testfunc() RETURNS void AS $$
8076+BEGIN
8077+ PERFORM 1, /*+ SeqScan(t1) */ * from t1;
8078+END;
8079+$$ LANGUAGE plpgsql;
8080+SELECT testfunc();
8081+LOG: pg_hint_plan:
8082+used hint:
8083+SeqScan(t1)
8084+not used hint:
8085+duplication hint:
8086+error hint:
8087+
8088+CONTEXT: SQL statement "SELECT 1, /*+ SeqScan(t1) */ * from t1"
8089+PL/pgSQL function testfunc() line 3 at PERFORM
8090+ testfunc
8091+----------
8092+
8093+(1 row)
8094+
8095+-- FOR loop
8096+DROP FUNCTION testfunc();
8097+CREATE FUNCTION testfunc() RETURNS int AS $$
8098+DECLARE
8099+ sum int;
8100+ v int;
8101+BEGIN
8102+ sum := 0;
8103+ FOR v IN SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id LOOP
8104+ sum := sum + v;
8105+ END LOOP;
8106+ RETURN v;
8107+END;
8108+$$ LANGUAGE plpgsql;
8109+SELECT testfunc();
8110+LOG: pg_hint_plan:
8111+used hint:
8112+SeqScan(t1)
8113+not used hint:
8114+duplication hint:
8115+error hint:
8116+
8117+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id"
8118+PL/pgSQL function testfunc() line 7 at FOR over SELECT rows
8119+ testfunc
8120+----------
8121+
8122+(1 row)
8123+
8124+-- Dynamic FOR loop
8125+DROP FUNCTION testfunc();
8126+CREATE FUNCTION testfunc() RETURNS int AS $$
8127+DECLARE
8128+ sum int;
8129+ v int;
8130+ i int;
8131+BEGIN
8132+ sum := 0;
8133+ FOR v IN EXECUTE 'SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id' LOOP
8134+ sum := sum + v;
8135+ END LOOP;
8136+ RETURN v;
8137+END;
8138+$$ LANGUAGE plpgsql;
8139+SELECT testfunc();
8140+LOG: pg_hint_plan:
8141+used hint:
8142+SeqScan(t1)
8143+not used hint:
8144+duplication hint:
8145+error hint:
8146+
8147+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id"
8148+PL/pgSQL function testfunc() line 8 at FOR over EXECUTE statement
8149+ testfunc
8150+----------
8151+ 0
8152+(1 row)
8153+
8154+-- Cursor FOR loop
8155+DROP FUNCTION testfunc();
8156+CREATE FUNCTION testfunc() RETURNS int AS $$
8157+DECLARE
8158+ ref CURSOR FOR SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
8159+ rec record;
8160+ sum int := 0;
8161+BEGIN
8162+ FOR rec IN ref LOOP
8163+ sum := sum + rec.val;
8164+ END LOOP;
8165+ RETURN sum;
8166+END;
8167+$$ LANGUAGE plpgsql;
8168+SELECT testfunc();
8169+LOG: pg_hint_plan:
8170+used hint:
8171+SeqScan(t1)
8172+not used hint:
8173+duplication hint:
8174+error hint:
8175+
8176+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id"
8177+PL/pgSQL function testfunc() line 7 at FOR over cursor
8178+ testfunc
8179+----------
8180+ 495000
8181+(1 row)
8182+
8183+-- RETURN QUERY
8184+DROP FUNCTION testfunc();
8185+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
8186+BEGIN
8187+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
8188+END;
8189+$$ LANGUAGE plpgsql;
8190+SELECT * FROM testfunc() LIMIT 1;
8191+LOG: pg_hint_plan:
8192+used hint:
8193+SeqScan(t1)
8194+not used hint:
8195+duplication hint:
8196+error hint:
8197+
8198+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id"
8199+PL/pgSQL function testfunc() line 3 at RETURN QUERY
8200+ id | val
8201+----+-----
8202+ 1 | 1
8203+(1 row)
8204+
8205+-- Test for error exit from inner SQL statement.
8206+DROP FUNCTION testfunc();
8207+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
8208+BEGIN
8209+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id;
8210+END;
8211+$$ LANGUAGE plpgsql;
8212+SELECT * FROM testfunc() LIMIT 1;
8213+ERROR: relation "ttx" does not exist
8214+LINE 1: SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id
8215+ ^
8216+QUERY: SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id
8217+CONTEXT: PL/pgSQL function testfunc() line 3 at RETURN QUERY
8218+-- this should not use SeqScan(t1) hint.
8219+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
8220+LOG: pg_hint_plan:
8221+used hint:
8222+IndexScan(t1)
8223+not used hint:
8224+duplication hint:
8225+error hint:
8226+
8227+ id | val
8228+----+-----
8229+ 1 | 1
8230+(1 row)
8231+
8232+DROP FUNCTION testfunc();
80308233 DROP EXTENSION pg_hint_plan;
80318234 --
80328235 -- 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
@@ -439,7 +439,13 @@ static int debug_level = 0;
439439 static int pg_hint_plan_message_level = INFO;
440440 /* Default is off, to keep backward compatibility. */
441441 static bool pg_hint_plan_enable_hint_table = false;
442-static bool hidestmt = false;
442+
443+/* Internal static variables. */
444+static bool hidestmt = false; /* Allow or inhibit STATEMENT: output */
445+
446+static int plpgsql_recurse_level = 0; /* PLpgSQL recursion level */
447+static int hint_inhibit_level = 0; /* Inhibit hinting if this is above 0 */
448+ /* (This could not be above 1) */
443449
444450 static const struct config_enum_entry parse_messages_level_options[] = {
445451 {"debug", DEBUG2, true},
@@ -525,13 +531,6 @@ static const HintParser parsers[] = {
525531 {NULL, NULL, HINT_KEYWORD_UNRECOGNIZED}
526532 };
527533
528-/*
529- * PL/pgSQL plugin for retrieving string representation of each query during
530- * function execution.
531- */
532-static const char *plpgsql_query_string = NULL;
533-static enum PLpgSQL_stmt_types plpgsql_query_string_src;
534-
535534 PLpgSQL_plugin plugin_funcs = {
536535 NULL,
537536 NULL,
@@ -542,9 +541,6 @@ PLpgSQL_plugin plugin_funcs = {
542541 NULL,
543542 };
544543
545-/* Current nesting depth of SPI calls, used to prevent recursive calls */
546-static int nested_level = 0;
547-
548544 /*
549545 * Module load callbacks
550546 */
@@ -1597,7 +1593,7 @@ get_hints_from_table(const char *client_query, const char *client_application)
15971593
15981594 PG_TRY();
15991595 {
1600- ++nested_level;
1596+ hint_inhibit_level++;
16011597
16021598 SPI_connect();
16031599
@@ -1635,11 +1631,11 @@ get_hints_from_table(const char *client_query, const char *client_application)
16351631
16361632 SPI_finish();
16371633
1638- --nested_level;
1634+ hint_inhibit_level--;
16391635 }
16401636 PG_CATCH();
16411637 {
1642- --nested_level;
1638+ hint_inhibit_level--;
16431639 PG_RE_THROW();
16441640 }
16451641 PG_END_TRY();
@@ -1655,15 +1651,21 @@ get_query_string(void)
16551651 {
16561652 const char *p;
16571653
1658- if (stmt_name)
1654+ if (plpgsql_recurse_level > 0)
1655+ {
1656+ /*
1657+ * This is quite ugly but this is the only point I could find where
1658+ * we can get the query string.
1659+ */
1660+ p = (char*)error_context_stack->arg;
1661+ }
1662+ else if (stmt_name)
16591663 {
16601664 PreparedStatement *entry;
16611665
16621666 entry = FetchPreparedStatement(stmt_name, true);
16631667 p = entry->plansource->query_string;
16641668 }
1665- else if (plpgsql_query_string)
1666- p = plpgsql_query_string;
16671669 else
16681670 p = debug_query_string;
16691671
@@ -2326,7 +2328,7 @@ pg_hint_plan_ProcessUtility(Node *parsetree, const char *queryString,
23262328 * Use standard planner if pg_hint_plan is disabled or current nesting
23272329 * depth is nesting depth of SPI calls.
23282330 */
2329- if (!pg_hint_plan_enable_hint || nested_level > 0)
2331+ if (!pg_hint_plan_enable_hint || hint_inhibit_level > 0)
23302332 {
23312333 if (debug_level > 1)
23322334 ereport(pg_hint_plan_message_level,
@@ -2488,13 +2490,13 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
24882490 * depth is nesting depth of SPI calls. Other hook functions try to change
24892491 * plan with current_hint if any, so set it to NULL.
24902492 */
2491- if (!pg_hint_plan_enable_hint || nested_level > 0)
2493+ if (!pg_hint_plan_enable_hint || hint_inhibit_level > 0)
24922494 {
24932495 if (debug_level > 1)
24942496 elog(pg_hint_plan_message_level,
24952497 "pg_hint_plan%s: planner: enable_hint=%d,"
2496- " nested_level=%d",
2497- qnostr, pg_hint_plan_enable_hint, nested_level);
2498+ " hint_inhibit_level=%d",
2499+ qnostr, pg_hint_plan_enable_hint, hint_inhibit_level);
24982500 hidestmt = true;
24992501
25002502 goto standard_planner_proc;
@@ -3095,18 +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-
3099- if (!current_hint || nested_level > 0)
3100+ if (!current_hint || hint_inhibit_level > 0)
31003101 {
31013102 if (debug_level > 1)
31023103 ereport(pg_hint_plan_message_level,
31033104 (errhidestmt(true),
31043105 errmsg ("pg_hint_plan%s: get_relation_info"
31053106 " no hint to apply: relation=%u(%s), inhparent=%d,"
3106- " current_hint=%p, nested_level=%d",
3107+ " current_hint=%p, hint_inhibit_level=%d",
31073108 qnostr, relationObjectId,
31083109 get_rel_name(relationObjectId),
3109- inhparent, current_hint, nested_level)));
3110+ inhparent, current_hint, hint_inhibit_level)));
31103111 return;
31113112 }
31123113
@@ -3124,10 +3125,10 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
31243125 (errhidestmt(true),
31253126 errmsg ("pg_hint_plan%s: get_relation_info"
31263127 " skipping inh parent: relation=%u(%s), inhparent=%d,"
3127- " current_hint=%p, nested_level=%d",
3128+ " current_hint=%p, hint_inhibit_level=%d",
31283129 qnostr, relationObjectId,
31293130 get_rel_name(relationObjectId),
3130- inhparent, current_hint, nested_level)));
3131+ inhparent, current_hint, hint_inhibit_level)));
31313132 return;
31323133 }
31333134
@@ -3226,10 +3227,10 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
32263227 errmsg("pg_hint_plan%s: get_relation_info:"
32273228 " index deletion by parent hint: "
32283229 "relation=%u(%s), inhparent=%d, current_hint=%p,"
3229- " nested_level=%d",
3230+ " hint_inhibit_level=%d",
32303231 qnostr, relationObjectId,
32313232 get_rel_name(relationObjectId),
3232- inhparent, current_hint, nested_level)));
3233+ inhparent, current_hint, hint_inhibit_level)));
32333234 return;
32343235 }
32353236
@@ -3247,10 +3248,10 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
32473248 errmsg ("pg_hint_plan%s: get_relation_info"
32483249 " index deletion:"
32493250 " relation=%u(%s), inhparent=%d, current_hint=%p,"
3250- " nested_level=%d, scanmask=0x%x",
3251+ " hint_inhibit_level=%d, scanmask=0x%x",
32513252 qnostr, relationObjectId,
32523253 get_rel_name(relationObjectId),
3253- inhparent, current_hint, nested_level,
3254+ inhparent, current_hint, hint_inhibit_level,
32543255 hint->enforce_mask)));
32553256 }
32563257 else
@@ -3261,10 +3262,10 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
32613262 errmsg ("pg_hint_plan%s: get_relation_info"
32623263 " no hint applied:"
32633264 " relation=%u(%s), inhparent=%d, current_hint=%p,"
3264- " nested_level=%d, scanmask=0x%x",
3265+ " hint_inhibit_level=%d, scanmask=0x%x",
32653266 qnostr, relationObjectId,
32663267 get_rel_name(relationObjectId),
3267- inhparent, current_hint, nested_level,
3268+ inhparent, current_hint, hint_inhibit_level,
32683269 current_hint->init_scan_mask)));
32693270 set_scan_config_options(current_hint->init_scan_mask,
32703271 current_hint->context);
@@ -3934,7 +3935,7 @@ pg_hint_plan_join_search(PlannerInfo *root, int levels_needed,
39343935 * valid hint is supplied or current nesting depth is nesting depth of SPI
39353936 * calls.
39363937 */
3937- if (!current_hint || nested_level > 0)
3938+ if (!current_hint || hint_inhibit_level > 0)
39383939 {
39393940 if (prev_join_search)
39403941 return (*prev_join_search) (root, levels_needed, initial_rels);
@@ -4030,48 +4031,7 @@ set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
40304031 static void
40314032 pg_hint_plan_plpgsql_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
40324033 {
4033- PLpgSQL_expr *expr = NULL;
4034-
4035- switch ((enum PLpgSQL_stmt_types) stmt->cmd_type)
4036- {
4037- case PLPGSQL_STMT_FORS:
4038- expr = ((PLpgSQL_stmt_fors *) stmt)->query;
4039- break;
4040- case PLPGSQL_STMT_FORC:
4041- expr = ((PLpgSQL_var *) (estate->datums[((PLpgSQL_stmt_forc *)stmt)->curvar]))->cursor_explicit_expr;
4042- break;
4043- case PLPGSQL_STMT_RETURN_QUERY:
4044- if (((PLpgSQL_stmt_return_query *) stmt)->query != NULL)
4045- expr = ((PLpgSQL_stmt_return_query *) stmt)->query;
4046- else
4047- expr = ((PLpgSQL_stmt_return_query *) stmt)->dynquery;
4048- break;
4049- case PLPGSQL_STMT_EXECSQL:
4050- expr = ((PLpgSQL_stmt_execsql *) stmt)->sqlstmt;
4051- break;
4052- case PLPGSQL_STMT_DYNEXECUTE:
4053- expr = ((PLpgSQL_stmt_dynexecute *) stmt)->query;
4054- break;
4055- case PLPGSQL_STMT_DYNFORS:
4056- expr = ((PLpgSQL_stmt_dynfors *) stmt)->query;
4057- break;
4058- case PLPGSQL_STMT_OPEN:
4059- if (((PLpgSQL_stmt_open *) stmt)->query != NULL)
4060- expr = ((PLpgSQL_stmt_open *) stmt)->query;
4061- else if (((PLpgSQL_stmt_open *) stmt)->dynquery != NULL)
4062- expr = ((PLpgSQL_stmt_open *) stmt)->dynquery;
4063- else
4064- expr = ((PLpgSQL_var *) (estate->datums[((PLpgSQL_stmt_open *)stmt)->curvar]))->cursor_explicit_expr;
4065- break;
4066- default:
4067- break;
4068- }
4069-
4070- if (expr)
4071- {
4072- plpgsql_query_string = expr->query;
4073- plpgsql_query_string_src = (enum PLpgSQL_stmt_types) stmt->cmd_type;
4074- }
4034+ plpgsql_recurse_level++;
40754035 }
40764036
40774037 /*
@@ -4082,9 +4042,7 @@ pg_hint_plan_plpgsql_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
40824042 static void
40834043 pg_hint_plan_plpgsql_stmt_end(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
40844044 {
4085- if (plpgsql_query_string &&
4086- plpgsql_query_string_src == stmt->cmd_type)
4087- plpgsql_query_string = NULL;
4045+ plpgsql_recurse_level--;
40884046 }
40894047
40904048 void plpgsql_query_erase_callback(ResourceReleasePhase phase,
@@ -4094,8 +4052,8 @@ void plpgsql_query_erase_callback(ResourceReleasePhase phase,
40944052 {
40954053 if (phase != RESOURCE_RELEASE_AFTER_LOCKS)
40964054 return;
4097- /* Force erase stored plpgsql query string */
4098- plpgsql_query_string = NULL;
4055+ /* Cancel plpgsql nest level*/
4056+ plpgsql_recurse_level = 0;
40994057 }
41004058
41014059 #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