Does oracle_fdw support test query to check cached sesssion valid

[qinghuiguo]

When I attempt to query in Oracle database using the Oracle Foreign Data Wrapper (Oracle FDW), I often encounter an issue. This problem is caused by the security policies of the network firewall, which results in connections timing out and being closed. However, the Oracle FDW's connection cache does not receive timely notifications of the closed connections. As a consequence, Oracle FDW continues to use expired connections during queries, leading to query timeouts.
Does the session connecting to Oracle through oracle_fdw have a regular health check mechanism like a connection pool?

[laurenz]

No, oracle_fdw has no support for an "are you still there?" query. I don't see the benefit of such a query: if the test query hangs and runs into a timeout, are you any better off than if your actual query hangs?

Issue #603 is somewhat similar. Perhaps the remedies that Phil suggested there can help.

Another thing you can do is to change the TCP keepalive settings on the machine where PostgreSQL is running. Very often, that is enough to combat a broken network setup like yours. On what operating system is the PostgreSQL server running?

[philflorent]

Hi,
Yes, just configure sqlnet.expire_time to 5 (=> in fact a value strictly inferior than that of your firewall timeout) in $ORACLE_HOME/network/admin/sqlnet.ora server side and that's all. It is an Oracle recommendation. Problem is that the default value of this parameter is not the recommended one for historical reasons. I solved many "strange" network issues simply with that parameter.
You can have some issues since Oracle switched from an internal feature in 11g to the OS TCP keepalive feature in 12c+ and it was bugged. But if your system is patched with latest release updates it should be OK.
Best regards,
Phil

[qinghuiguo]

Many thanks !

I have a solution. You should set kernel parameters on both the PG Server and the Oracle Server. If I only set the parameters on PG server, this problem happens again.

Example:

vi /etc/sysctl.con
net.ipv4.tcp_keepalive_time = 60  ###lower than firewall timeout 
net.ipv4.tcp_keepalive_intvl = 10 
net.ipv4.tcp_keepalive_probes = 6 

[laurenz]

Thanks for the feedback!