Re: Add PL/pgSQL extra check no_data_found

po 12. 12. 2022 v 14:16 odesílatel Мельников Игорь <melnikov_ii(at)mail(dot)ru>
napsal:

> 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?
>

The extra checks are not designed for compatibility with Oracle. It is
designed to implement some common checks that are harder or slower to
implement in plpgsql.

no_data_found issue can be simply checked by variable FOUND. On the second
hand, too many rows is more complex (a little bit). You need to use the GET
DIAGNOSTICS command and IF.

Extra checks were designed to check some less frequent but nasty errors to
write safer code. It is not designed for better portability from Oracle.

Regards

Pavel

> Thanx
>
> Best Regards
> Igor Melnikov
>
>
>
> Понедельник, 12 декабря 2022, 16:01 +03:00 от Pavel Stehule <
> pavel(dot)stehule(at)gmail(dot)com <http:///compose?To=pavel(dot)stehule(at)gmail(dot)com>>:
>
> Hi
>
> po 12. 12. 2022 v 13:37 odesílatel Мельников Игорь <melnikov_ii(at)mail(dot)ru
> <http://e.mail.ru/compose/?mailto=mailto%3amelnikov_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
> <http://e.mail.ru/compose/?mailto=mailto%3apavel(dot)stehule(at)gmail(dot)com>>:
>
>
>
> čt 8. 12. 2022 v 12:29 odesílatel Sergey Shinderuk <
> s(dot)shinderuk(at)postgrespro(dot)ru
> <http://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
> <http://e.mail.ru/compose/?mailto=mailto%3amelnikov_ii(at)mail(dot)ru>
>
>
>
>
> С уважением,
> Мельников Игорь
> melnikov_ii(at)mail(dot)ru <http:///compose?To=melnikov_ii(at)mail(dot)ru>
>
>