Thread: Re: Request for resolution || Support

> On 24 Jul 2019, at 10:08, jay chauhan <jaychauhan.src@gmail.com> wrote:
>
> Hi Thomas, David/Team,
>
> Thanks you for your response. However we need your confirmation whether my Error/issue as mentioned below will be
resolvedif we upgrade our PostgreSQL Version. 

It won’t, you are talking about Oracle-specific features. You need to change the code. PostgreSQL is not Oracle, some
featuresare quite RDBMS-specific. 

> <<My PostgreSQL version : "PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red
Hat4.8.5-4), 64-bit">> 
> David response: Use a newer version
> Tomas response: Yeah, you should use release 11 for a new project.
>
> My Issue while migrating procedure/function from Oracle to PostgreSQL:
> Error-1)
> ERROR:  cannot begin/end transactions in PL/pgSQL
> HINT:  Use a BEGIN block with an EXCEPTION clause instead.
> CONTEXT:  PL/pgSQL function icmsuatnew.eload_commission_payout_active(text,text,text,text,text,text) line 486 at SQL
statement
> SQL state: 0A000
> David Response on it : Rewrite your code as instructed

How to handle these depends on your use of sub-transactions, but the HINT gives a pretty good general approach.

> Error-2)
> ERROR:  schema "utl_http" does not exist
> LINE 38:     L_HTTP_REQUEST UTL_HTTP.REQ;
>                             ^
> SQL state: 3F000
> Character: 1785
> Thomas response: That's an Oracle thing for doing HTTP requests from PL/SQL.  To do that from plpgsql you could try
anextension like this one: 
> https://github.com/pramsey/pgsql-http
> Or you could write your own function in Python or <insert your
> favourite PL>.  That's what I'd probably do.
> https://www.postgresql.org/docs/11/plpython-funcs.html

Initiating TCP/IP from the database means that a database process needs to wait for a response. In the meantime, it
cannotdo anything else. You’re effectively blocking it for other transactions and keeping that particular transaction
‘waitingin transaction’ until, in the worst case, a time-out. That means that no maintenance can be done on records
touchedby this transaction, which can lead to bloat. 

This is generally considered a bad idea, at least in this community. You’re usually better off handing the connection
overto an external process that reports back to the database when appropriate. 
The exception to that is if your transaction cannot be allowed to commit without a response from the other peer. In
suchcases it is appropriate to use plpython, plperl, etc 

All that said, I am talking about PostgreSQL here. If you’re instead using EnterpriseDB, which does have an Oracle
compatibilitylayer that could perhaps support these features (I don’t know), you should talk to the EnterpriseDB guys. 

Alban Hertroys
--
There is always an exception to always.

> On 24 Jul 2019, at 10:08, jay chauhan <jaychauhan.src@gmail.com> wrote:
>
> Hi Thomas, David/Team,
>
> Thanks you for your response. However we need your confirmation whether my Error/issue as mentioned below will be
resolvedif we upgrade our PostgreSQL Version. 

It won’t, you are talking about Oracle-specific features. You need to change the code. PostgreSQL is not Oracle, some
featuresare quite RDBMS-specific. 

> <<My PostgreSQL version : "PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red
Hat4.8.5-4), 64-bit">> 
> David response: Use a newer version
> Tomas response: Yeah, you should use release 11 for a new project.
>
> My Issue while migrating procedure/function from Oracle to PostgreSQL:
> Error-1)
> ERROR:  cannot begin/end transactions in PL/pgSQL
> HINT:  Use a BEGIN block with an EXCEPTION clause instead.
> CONTEXT:  PL/pgSQL function icmsuatnew.eload_commission_payout_active(text,text,text,text,text,text) line 486 at SQL
statement
> SQL state: 0A000
> David Response on it : Rewrite your code as instructed

How to handle these depends on your use of sub-transactions, but the HINT gives a pretty good general approach.

> Error-2)
> ERROR:  schema "utl_http" does not exist
> LINE 38:     L_HTTP_REQUEST UTL_HTTP.REQ;
>                             ^
> SQL state: 3F000
> Character: 1785
> Thomas response: That's an Oracle thing for doing HTTP requests from PL/SQL.  To do that from plpgsql you could try
anextension like this one: 
> https://github.com/pramsey/pgsql-http
> Or you could write your own function in Python or <insert your
> favourite PL>.  That's what I'd probably do.
> https://www.postgresql.org/docs/11/plpython-funcs.html

Initiating TCP/IP from the database means that a database process needs to wait for a response. In the meantime, it
cannotdo anything else. You’re effectively blocking it for other transactions and keeping that particular transaction
‘waitingin transaction’ until, in the worst case, a time-out. That means that no maintenance can be done on records
touchedby this transaction, which can lead to bloat. 

This is generally considered a bad idea, at least in this community. You’re usually better off handing the connection
overto an external process that reports back to the database when appropriate. 
The exception to that is if your transaction cannot be allowed to commit without a response from the other peer. In
suchcases it is appropriate to use plpython, plperl, etc 

All that said, I am talking about PostgreSQL here. If you’re instead using EnterpriseDB, which does have an Oracle
compatibilitylayer that could perhaps support these features (I don’t know), you should talk to the EnterpriseDB guys. 

Alban Hertroys
--
There is always an exception to always.