On Sat, 17 Jul 2021 at 01:29, Pavel Stehule <pavel.steh...@gmail.com> wrote:
> Hi > > pá 16. 7. 2021 v 21:47 odesílatel Dinesh Chemuduru < > dinesh.ku...@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. Regards, Dinesh Kumar > > >> Regards, >> Dinesh Kumar >> >