Re: Add PL/pgSQL extra check no_data_found - Mailing list pgsql-hackers

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.stehule@gmail.com>:
 

Hi

 

po 12. 12. 2022 v 13:37 odesílatel Мельников Игорь <melnikov_ii@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.stehule@gmail.com>:
 

 

 

čt 8. 12. 2022 v 12:29 odesílatel Sergey Shinderuk <s.shinderuk@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_ii@mail.ru

 

 

 

С уважением,
Мельников Игорь
melnikov_ii@mail.ru