Re: [PROPOSAL] new diagnostic items for the dynamic sql

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

> On Sat, 17 Jul 2021 at 01:29, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>> Hi
>>
>> pá 16. 7. 2021 v 21:47 odesílatel Dinesh Chemuduru <
>> dinesh(dot)kumar(at)migops(dot)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.

Regards

Pavel

> Regards,
> Dinesh Kumar
>
>
>>
>>
>>> Regards,
>>> Dinesh Kumar
>>>
>>