Re: Request for resolution || Support

> On 24 Jul 2019, at 10:08, jay chauhan <jaychauhan(dot)src(at)gmail(dot)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 resolved if 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 features are 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 Hat 4.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 an extension 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 cannot do anything else. You’re effectively blocking it for other transactions and keeping that particular transaction ‘waiting in transaction’ until, in the worst case, a time-out. That means that no maintenance can be done on records touched by 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 over to 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 such cases 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 compatibility layer 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.