Re: Add PL/pgSQL extra check no_data_found

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.

 

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