út 24. 8. 2021 v 8:16 odesílatel Dinesh Chemuduru <dinesh.ku...@migops.com> napsal:
> Hi Pavel, > > On Tue, 24 Aug 2021 at 00:19, Pavel Stehule <pavel.steh...@gmail.com> > wrote: > >> Hi >> >> pá 20. 8. 2021 v 10:24 odesílatel Dinesh Chemuduru < >> dinesh.ku...@migops.com> napsal: >> >>> On Sun, 25 Jul 2021 at 16:34, Pavel Stehule <pavel.steh...@gmail.com> >>> wrote: >>> >> >> please, can you register this patch to commitfest app >> >> https://commitfest.postgresql.org/34/ >> >> This patch is registered > > https://commitfest.postgresql.org/34/3258/ > ok, I looked it over. Regards Pavel > >> Regards >> >> Pavel >> >>> >>>> >>>> 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. >>>> >>>> >>> 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 >>>>>>> >>>>>>
