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 > <//e.mail.ru/compose/?mailto=mailto%3as.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 > >
