Does oracle_fdw support test query to check cached sesssion valid about oracle_fdw HOT 4 CLOSED

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?

from oracle_fdw.

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

from oracle_fdw.

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 

from oracle_fdw.

Thanks for the feedback!

from oracle_fdw.