Re: Add PL/pgSQL extra check no_data_found

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(dot)stehule(at)gmail(dot)com >:
> 
>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 > 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
>> 
 
 
С уважением,
Мельников Игорь
melnikov_ii(at)mail(dot)ru