oracleQueryPlan internal error: statement handle is not NULL

[vishalnatekar]

Hello, My function has a bunch of SQL which is inserting and updating several tables. It reads from Oracle table using oracle_fdw. In one of the enviroment it is getting stuck on one of the SQL, when i pull out the SQL just run the SQL on its own, it finishes in a microsecond, but for some reason it gets stuck there when running as part of the function.

When i wanted to check the plan and I get the error - oracleQueryPlan internal error: statement handle is not NULL.

I dont see the error if i just run the EXPLAIN, i get it only when i run EXPLAIN ANALYZE.

When I set client_min_messages = debug1;

And i see the below output -

oracle_fdw: plan foreign table scan oracle_fdw: set NLS_LANG=AMERICAN_AMERICA.AL32UTF8 oracle_fdw: plan foreign table scan oracle_fdw: set NLS_LANG=AMERICAN_AMERICA.AL32UTF8 oracle_fdw: plan foreign table scan oracle_fdw: set NLS_LANG=AMERICAN_AMERICA.AL32UTF8 oracle_fdw: plan foreign table scan oracle_fdw: set NLS_LANG=AMERICAN_AMERICA.AL32UTF8 oracle_fdw: plan foreign table scan oracle_fdw: set NLS_LANG=AMERICAN_AMERICA.AL32UTF8 oracle_fdw: remote query is: SELECT /9fb5557c9f5d9406650381fdaf7f19eb/ r4."M_BRW_NOM1", r4."M_TRN_STATUS", r4."M_PL_INSCUR", r4."M_CONTRACT", r6."M_UDF_REF" FROM ("SWA_FINANCIAL"."TRN_HDR_DBF" r4 INNER JOIN "SWA_FINANCIAL"."TRN_EXT_DBF" r6 ON (r4."M_LEXTREF" = r6."M_REFERENCE")) oracle_fdw: remote query is: SELECT /91a5017d48009812980f9bc65368dada/ r1."M_REF_DATA", r1."M_CNT_ORG", r1."M_CNT_VS2", r1."M_COLLAGCAT", r1."M_CONTRACT", r1."M_COUPON_DT", r1."M_H_COLL_FX", r1."M_MKT_VALUE", r1."M_PORTFOLIO", r1."M_STP_STATUS", r1."M_TP_RTSD0", r1."M_TP_RTSD1", r1."M_TRM_VALUE", r1."M_TRN_TYPE", r1."M_VRBL_NTNL" FROM "SWA_DATAMART"."SWA_NPV_REP" r1 WHERE (r1."M_REF_DATA" = 370593) oracle_fdw: remote query is: SELECT /ec1df85d735f86e216a292fe194a1ed8/ r1."M_NB", r1."M_NOV_STATUS", r1."M_OTHER_PORT" FROM "SWA_FINANCIAL"."TABLE#DATA#DEALIRD_DBF" r1 oracle_fdw: remote query is: SELECT /8ba20444169bbcd0f15845445639cf40/ r1."M_NB", r1."M_NOV_STATUS", r1."M_OTHER_PORT" FROM "SWA_FINANCIAL"."TABLE#DATA#DEALSCF_DBF" r1 oracle_fdw: begin foreign join oracle_fdw: begin foreign table scan on 962581 oracle_fdw: begin foreign table scan on 962611 oracle_fdw: begin foreign table scan on 962614 oracle_fdw: execute query in foreign table scan [2023-09-08 14:29:24] [HV000] ERROR: oracleQueryPlan internal error: statement handle is not NULL oracle_fdw: execute query in foreign table scan oracle_fdw: explain foreign table scan.

I am using Amazon Aurora version 13.8.

From changelog i could see the above issue was fixed in version 2.6.

Not sure which version of oracle_fdw i am using, but i updated it using

ALTER EXTENSION oracle_fdw UPDATE; But still no luck. Any idea why i am facing this?

Thanks

[laurenz]

I just released 2.6.0.

Could you try with this code and see if that makes the problem go away?

[laurenz]

Duplicate of #359

[laurenz]

Does the latest version work for you?

[vishalnatekar]

Hey laurenz, thanks for the response. We use AWS aurora postgres. I don’t think I can use that package.

I tried destroying and recreating oracle_fdw and it seem to have resolved this issue. This doesn’t happen every-time there are no rows to fetch . We have quite a few environments and most of them return 0 rows for this SQL, but this issue was seen only in one of the environment.

[vishalnatekar]

If it happens again i am thinking if I should try - setting isolation level to read read_committed on the oracle server and see if it works. We only read from Oracle.

[laurenz]

Do you have a way to determine which oracle_fdw version you are running? If in doubt, ask Amazon.

Yes, this is not entirely deterministic; it depends on the order of the API calls. I could easily reproduce it by enabling auto_explain.

[vishalnatekar]

From definition i could see - its version 1.2

When i try to update it using - ALTER EXTENSION oracle_fdw UPDATE. It says i'm using the latest version 1.2 :(

[laurenz]

That's just the extension version. You get the oracle_fdw version with

SELECT oracle_diag();

[vishalnatekar]

Ah, ok ,.

SELECT oracle_diag();

oracle_fdw 2.5.0, PostgreSQL 14.7, Oracle client 19.10.0.0.0

[laurenz]

That is too old. You need more recent software to have that bug fixed; at least containing commit b82f507dc741b6115e56bb98796defa1fda88fe5 from 2023-04-11.

[vishalnatekar]

Do you know how I can update on Amazon ? I can raise a portal case with them too..

[laurenz]

You are dependent on Amazon for that. Lobby them to make release 2.6.0 available.

[laurenz]

I'll close this issue, since I don't think there is anything I can do to make Amazon upgrade.