Re: Add PL/pgSQL extra check no_data_found

Hi

po 12. 12. 2022 v 13:37 odesílatel Мельников Игорь <melnikov_ii(at)mail(dot)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(dot)stehule(at)gmail(dot)com>:
>
>
>
> čt 8. 12. 2022 v 12:29 odesílatel Sergey Shinderuk <
> s(dot)shinderuk(at)postgrespro(dot)ru
> <//e.mail.ru/compose/?mailto=mailto%3as(dot)shinderuk(at)postgrespro(dot)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_ii(at)mail(dot)ru
>
>