Hi Everyone,
We would like to propose the below 2 new plpgsql diagnostic items,
related to parsing. Because, the current diag items are not providing
the useful diagnostics about the dynamic SQL statements.
1. PG_PARSE_SQL_STATEMENT (returns parse failed sql statement)
2. PG_PARSE_SQL_STATEMENT_POSITION (returns parse failed sql text cursor
position)
Consider the below example, which is an invalid SQL statement.
postgres=# SELECT 1 JOIN SELECT 2;
ERROR: syntax error at or near "JOIN"
LINE 1: SELECT 1 JOIN SELECT 2;
^
Here, there is a syntax error at JOIN clause,
and also we are getting the syntax error position(^ symbol, the position of
JOIN clause).
This will be helpful, while dealing with long queries.
Now, if we run the same statement as a dynamic SQL(by using EXECUTE <sql
statement>),
then it seems we are not getting the text cursor position,
and the SQL statement which is failing at parse level.
Please find the below example.
postgres=# SELECT exec_me('SELECT 1 JOIN SELECT 2');
NOTICE: RETURNED_SQLSTATE 42601
NOTICE: COLUMN_NAME
NOTICE: CONSTRAINT_NAME
NOTICE: PG_DATATYPE_NAME
NOTICE: MESSAGE_TEXT syntax error at or near "JOIN"
NOTICE: TABLE_NAME
NOTICE: SCHEMA_NAME
NOTICE: PG_EXCEPTION_DETAIL
NOTICE: PG_EXCEPTION_HINT
NOTICE: PG_EXCEPTION_CONTEXT PL/pgSQL function exec_me(text) line 18 at
EXECUTE
NOTICE: PG_CONTEXT PL/pgSQL function exec_me(text) line 21 at GET STACKED
DIAGNOSTICS
exec_me
---------
(1 row)
>From the above results, by using all the existing diag items, we are unable
to get the position of "JOIN" in the submitted SQL statement.
By using these proposed diag items, we will be getting the required
information,
which will be helpful while running long SQL statements as dynamic SQL
statements.
Please find the below example.
postgres=# SELECT exec_me('SELECT 1 JOIN SELECT 2');
NOTICE: PG_PARSE_SQL_STATEMENT SELECT 1 JOIN SELECT 2
NOTICE: PG_PARSE_SQL_STATEMENT_POSITION 10
exec_me
---------
(1 row)
>From the above results, by using these diag items,
we are able to get what is failing and it's position as well.
This information will be much helpful to debug the issue,
while a long running SQL statement is running as a dynamic SQL statement.
We are attaching the patch for this proposal, and will be looking for your
inputs.
Regards,
Dinesh Kumar
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 5c546f630f..aee4bc1461 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -2869,6 +2869,16 @@ GET STACKED DIAGNOSTICS <replaceable>variable</replaceable> { = | := } <replacea
<entry><type>text</type></entry>
<entry>the name of the schema related to exception</entry>
</row>
+ <row>
+ <entry><literal>PG_PARSE_SQL_STATEMENT</literal></entry>
+ <entry><type>text</type></entry>
+ <entry>the parse failed sql statement, if any</entry>
+ </row>
+ <row>
+ <entry><literal>PG_PARSE_SQL_STATEMENT_POSITION</literal></entry>
+ <entry><type>text</type></entry>
+ <entry>the parse failed sql statement's text cursor position, if any</entry>
+ </row>
<row>
<entry><literal>PG_EXCEPTION_DETAIL</literal></entry>
<entry><type>text</type></entry>
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 2f473c14c4..b8bbfd314a 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -2443,6 +2443,14 @@ exec_stmt_getdiag(PLpgSQL_execstate *estate, PLpgSQL_stmt_getdiag *stmt)
estate->cur_error->hint);
break;
+ case PLPGSQL_GETDIAG_PARSE_SQL_STATEMENT:
+ exec_assign_c_string(estate, var, estate->cur_error->internalquery);
+ break;
+
+ case PLPGSQL_GETDIAG_PARSE_SQL_STATEMENT_POSITION:
+ exec_assign_value(estate, var, UInt64GetDatum(estate->cur_error->internalpos), false, INT8OID, -1);
+ break;
+
case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
exec_assign_c_string(estate, var,
unpack_sql_state(estate->cur_error->sqlerrcode));
diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c
index ee60ced583..9086ebd20d 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -313,6 +313,10 @@ plpgsql_getdiag_kindname(PLpgSQL_getdiag_kind kind)
return "PG_EXCEPTION_DETAIL";
case PLPGSQL_GETDIAG_ERROR_HINT:
return "PG_EXCEPTION_HINT";
+ case PLPGSQL_GETDIAG_PARSE_SQL_STATEMENT:
+ return "PG_PARSE_SQL_STATEMENT";
+ case PLPGSQL_GETDIAG_PARSE_SQL_STATEMENT_POSITION:
+ return "PG_PARSE_SQL_STATEMENT_POSITION";
case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
return "RETURNED_SQLSTATE";
case PLPGSQL_GETDIAG_COLUMN_NAME:
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 3e84162487..05c6e1c8a0 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -323,6 +323,8 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt);
%token <keyword> K_PG_CONTEXT
%token <keyword> K_PG_DATATYPE_NAME
%token <keyword> K_PG_EXCEPTION_CONTEXT
+%token <keyword> K_PG_PARSE_SQL_STATEMENT
+%token <keyword> K_PG_PARSE_SQL_STATEMENT_POSITION
%token <keyword> K_PG_EXCEPTION_DETAIL
%token <keyword> K_PG_EXCEPTION_HINT
%token <keyword> K_PRINT_STRICT_PARAMS
@@ -998,6 +1000,8 @@ stmt_getdiag : K_GET getdiag_area_opt K_DIAGNOSTICS getdiag_list ';'
case PLPGSQL_GETDIAG_ERROR_CONTEXT:
case PLPGSQL_GETDIAG_ERROR_DETAIL:
case PLPGSQL_GETDIAG_ERROR_HINT:
+ case PLPGSQL_GETDIAG_PARSE_SQL_STATEMENT:
+ case PLPGSQL_GETDIAG_PARSE_SQL_STATEMENT_POSITION:
case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
case PLPGSQL_GETDIAG_COLUMN_NAME:
case PLPGSQL_GETDIAG_CONSTRAINT_NAME:
@@ -1081,6 +1085,10 @@ getdiag_item :
else if (tok_is_keyword(tok, &yylval,
K_PG_EXCEPTION_CONTEXT, "pg_exception_context"))
$$ = PLPGSQL_GETDIAG_ERROR_CONTEXT;
+ else if (tok_is_keyword(tok, &yylval, K_PG_PARSE_SQL_STATEMENT, "pg_parse_sql_statement"))
+ $$ = PLPGSQL_GETDIAG_PARSE_SQL_STATEMENT;
+ else if (tok_is_keyword(tok, &yylval, K_PG_PARSE_SQL_STATEMENT_POSITION, "pg_parse_sql_statement_position"))
+ $$ = PLPGSQL_GETDIAG_PARSE_SQL_STATEMENT_POSITION;
else if (tok_is_keyword(tok, &yylval,
K_COLUMN_NAME, "column_name"))
$$ = PLPGSQL_GETDIAG_COLUMN_NAME;
diff --git a/src/pl/plpgsql/src/pl_unreserved_kwlist.h b/src/pl/plpgsql/src/pl_unreserved_kwlist.h
index 99b3cf7d8a..ae1ef657d4 100644
--- a/src/pl/plpgsql/src/pl_unreserved_kwlist.h
+++ b/src/pl/plpgsql/src/pl_unreserved_kwlist.h
@@ -84,6 +84,8 @@ PG_KEYWORD("pg_datatype_name", K_PG_DATATYPE_NAME)
PG_KEYWORD("pg_exception_context", K_PG_EXCEPTION_CONTEXT)
PG_KEYWORD("pg_exception_detail", K_PG_EXCEPTION_DETAIL)
PG_KEYWORD("pg_exception_hint", K_PG_EXCEPTION_HINT)
+PG_KEYWORD("pg_parse_sql_statement", K_PG_PARSE_SQL_STATEMENT)
+PG_KEYWORD("pg_parse_sql_statement_position", K_PG_PARSE_SQL_STATEMENT_POSITION)
PG_KEYWORD("print_strict_params", K_PRINT_STRICT_PARAMS)
PG_KEYWORD("prior", K_PRIOR)
PG_KEYWORD("query", K_QUERY)
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index 0c3d30fb13..259c53c310 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -153,6 +153,8 @@ typedef enum PLpgSQL_getdiag_kind
PLPGSQL_GETDIAG_ERROR_CONTEXT,
PLPGSQL_GETDIAG_ERROR_DETAIL,
PLPGSQL_GETDIAG_ERROR_HINT,
+ PLPGSQL_GETDIAG_PARSE_SQL_STATEMENT,
+ PLPGSQL_GETDIAG_PARSE_SQL_STATEMENT_POSITION,
PLPGSQL_GETDIAG_RETURNED_SQLSTATE,
PLPGSQL_GETDIAG_COLUMN_NAME,
PLPGSQL_GETDIAG_CONSTRAINT_NAME,
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index d0a6b630b8..e76789857d 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -5703,3 +5703,46 @@ END; $$ LANGUAGE plpgsql;
ERROR: "x" is not a scalar variable
LINE 3: GET DIAGNOSTICS x = ROW_COUNT;
^
+--
+-- PG_PARSE_SQL_STATEMENT, PG_PARSE_SQL_STATEMENT_POSITION
+-- should return parse exception details, if sql's parse operation fail
+--
+DO
+$$
+DECLARE
+v_sql TEXT:='SELECT 1 JOIN SELECT 2';
+v_err_sql_stmt TEXT;
+v_err_sql_pos INT;
+BEGIN
+EXECUTE v_sql;
+EXCEPTION
+WHEN OTHERS THEN
+GET STACKED DIAGNOSTICS v_err_sql_stmt = PG_PARSE_SQL_STATEMENT,
+v_err_sql_pos = PG_PARSE_SQL_STATEMENT_POSITION;
+RAISE NOTICE 'exception sql %', v_err_sql_stmt;
+RAISE NOTICE 'exception sql position %', v_err_sql_pos;
+END;
+$$;
+NOTICE: exception sql SELECT 1 JOIN SELECT 2
+NOTICE: exception sql position 10
+--
+-- PG_PARSE_SQL_STATEMENT, PG_PARSE_SQL_STATEMENT_POSITION
+-- should return empty results, if sql's parse operation success
+--
+DO
+$$
+DECLARE
+v_err_sql_pos INT;
+v_err_sql_stmt TEXT;
+BEGIN
+PERFORM 1/0;
+EXCEPTION
+WHEN OTHERS THEN
+GET STACKED DIAGNOSTICS v_err_sql_stmt = PG_PARSE_SQL_STATEMENT,
+v_err_sql_pos = PG_PARSE_SQL_STATEMENT_POSITION;
+RAISE NOTICE 'exception sql %', v_err_sql_stmt;
+RAISE NOTICE 'exception sql position %', v_err_sql_pos;
+END;
+$$;
+NOTICE: exception sql
+NOTICE: exception sql position 0
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 07c60c80e4..092befa79f 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -4647,3 +4647,44 @@ BEGIN
GET DIAGNOSTICS x = ROW_COUNT;
RETURN;
END; $$ LANGUAGE plpgsql;
+
+--
+-- PG_PARSE_SQL_STATEMENT, PG_PARSE_SQL_STATEMENT_POSITION
+-- should return parse exception details, if sql's parse operation fail
+--
+DO
+$$
+DECLARE
+v_sql TEXT:='SELECT 1 JOIN SELECT 2';
+v_err_sql_stmt TEXT;
+v_err_sql_pos INT;
+BEGIN
+EXECUTE v_sql;
+EXCEPTION
+WHEN OTHERS THEN
+GET STACKED DIAGNOSTICS v_err_sql_stmt = PG_PARSE_SQL_STATEMENT,
+v_err_sql_pos = PG_PARSE_SQL_STATEMENT_POSITION;
+RAISE NOTICE 'exception sql %', v_err_sql_stmt;
+RAISE NOTICE 'exception sql position %', v_err_sql_pos;
+END;
+$$;
+
+--
+-- PG_PARSE_SQL_STATEMENT, PG_PARSE_SQL_STATEMENT_POSITION
+-- should return empty results, if sql's parse operation success
+--
+DO
+$$
+DECLARE
+v_err_sql_pos INT;
+v_err_sql_stmt TEXT;
+BEGIN
+PERFORM 1/0;
+EXCEPTION
+WHEN OTHERS THEN
+GET STACKED DIAGNOSTICS v_err_sql_stmt = PG_PARSE_SQL_STATEMENT,
+v_err_sql_pos = PG_PARSE_SQL_STATEMENT_POSITION;
+RAISE NOTICE 'exception sql %', v_err_sql_stmt;
+RAISE NOTICE 'exception sql position %', v_err_sql_pos;
+END;
+$$;
