ne 25. 7. 2021 v 12:52 odesílatel Dinesh Chemuduru <dinesh.ku...@migops.com> napsal:
> 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. > 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 >>> >>