Re: [PROPOSAL] new diagnostic items for the dynamic sql - Mailing list pgsql-hackers

Hi

pá 16. 7. 2021 v 21:47 odesílatel Dinesh Chemuduru <dinesh.kumar@migops.com> napsal:

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.

+1 It is good idea.  I am not sure if the used names are good. I propose

PG_SQL_TEXT and PG_ERROR_LOCATION

Regards

Pavel

Regards,

Dinesh Kumar