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

On Sun, 25 Jul 2021 at 16:34, Pavel Stehule <pavel.stehule@gmail.com> wrote:

ne 25. 7. 2021 v 12:52 odesílatel Dinesh Chemuduru <dinesh.kumar@migops.com> napsal:

On Sat, 17 Jul 2021 at 01:29, Pavel Stehule <pavel.stehule@gmail.com> wrote:

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

Thanks Pavel,

Sorry for the late reply.

The proposed diag items are `PG_SQL_TEXT`, `PG_ERROR_LOCATION` are much better and generic.

But, as we are only dealing with the parsing failure, I thought of adding that to the diag name.

I understand. But parsing is only one case - and these variables can be used for any case. Sure, ***we don't want*** to have PG_PARSE_SQL_TEXT, PG_ANALYZE_SQL_TEXT, PG_EXECUTION_SQL_TEXT ...

The idea is good, and you found the case, where it has benefits for users. Naming is hard.

Thanks for your time and suggestions Pavel.

I updated the patch as per the suggestions, and attached it here for further inputs.

Regards,

Dinesh Kumar

 

Regards

Pavel

Regards,

Dinesh Kumar

 

Regards,

Dinesh Kumar