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

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

> Regards,
> Dinesh Kumar
>