I know, know. But ora2pg NOT convert source code in application tier anonymouse block and dynamic SQL in server side pl/sql. This part of application need to be rewrite manually. "no_data_found" for the plpgsql.extra_errors and plpgsql.extra_warnings will be reduce this part of work. Also, in my opinion, it looks strange that there too_many_rows is in plpgsql.extra_errors and plpgsql.extra_warnings, but no_data_found NOT. Why? Thanx Best Regards Igor Melnikov
>Понедельник, 12 декабря 2022, 16:01 +03:00 от Pavel Stehule < >pavel.steh...@gmail.com >: > >Hi >po 12. 12. 2022 v 13:37 odesílatel Мельников Игорь < melnikov...@mail.ru > >napsal: >>Hi! >> >>This new feature will be in demand for customers who migrate their >>largeapplications (having millions of lines of PL/SQL code) from Oracle to >>PostreSQL. >>It will reduce the amount of work on rewriting the code will provide an >>opportunity to reduce budgets for the migration project. >> >>Yes, in case the part of the code that handles no_data_found is executed very >>often, this will cause performance loss. >>During the testing phase, this will be discovered and the customer will >>rewrite these problem areas of the code - add the phrase STRICT. >>He will not need to change all the code at the very beginning, as it happens >>now, without this feature. > >ora2pg does this work by default. It is great tool and reduces lot of work > >https://ora2pg.darold.net/ > >Regards > >Pavel > > >> >>I am convinced that this functionality will attract even more customers to >>PostgreSQL - it will increase the popularity of the PostgeSQL DBMS. >> >>Thank you! >> >>Best Regards >>Igor Melnikov >> >> >>>Понедельник, 12 декабря 2022, 15:23 +03:00 от Pavel Stehule < >>>pavel.steh...@gmail.com >: >>> >>> >>>čt 8. 12. 2022 v 12:29 odesílatel Sergey Shinderuk < >>>s.shinde...@postgrespro.ru > napsal: >>>>Hello, >>>> >>>>I propose to add a new value "no_data_found" for the >>>>plpgsql.extra_errors and plpgsql.extra_warnings parameters [1]. >>>> >>>>With plpgsql.extra_errors=no_data_found SELECT INTO raises no_data_found >>>>exception when the result set is empty. With >>>>plpgsql.extra_errors=too_many_rows,no_data_found SELECT INTO behaves >>>>like SELECT INTO STRICT [2]. This could simplify migration from PL/SQL >>>>and may be just more convenient. >>>> >>>>One potential downside is that plpgsql.extra_errors=no_data_found could >>>>break existing functions expecting to get null or checking IF found >>>>explicitly. This is also true for the too_many_rows exception, but >>>>arguably it's a programmer error, while no_data_found switches to a >>>>different convention for handling (or not handling) an empty result with >>>>SELECT INTO. >>>> >>>>Otherwise the patch is straightforward. >>>> >>>>What do you think? >>> >>>I am not against it. It makes sense. >>> >>>I don't like the idea about possible replacement of INTO STRICT by INTO + >>>extra warnings. >>> >>>Handling exceptions is significantly more expensive than in Oracle, and >>>using INTO without STRICT with the next test IF NOT FOUND THEN can save one >>>safepoint and one handling an exception. It should be mentioned in the >>>documentation. Using this very common Oracle's pattern can have a very >>>negative impact on performance in Postgres. If somebody does port from >>>Oracle, and wants compatible behavior then he should use INTO STRICT. I >>>think it is counterproductive to hide syntax differences when there is a >>>significant difference in performance (and will be). >>> >>>Regards >>> >>>Pavel >>> >>> >>> >>>>-- >>>>Sergey Shinderuk https://postgrespro.com/ >>>> >>>> >>>>[1] >>>>https://www.postgresql.org/docs/devel/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS >>>>[2] >>>>https://www.postgresql.org/docs/devel/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW >>>> >> >> >>С уважением, >>Мельников Игорь >>melnikov...@mail.ru >> С уважением, Мельников Игорь melnikov...@mail.ru
