oracle_fdw wouldn't rebuild a session to oracle automatically? about oracle_fdw HOT 5 CLOSED

When an Oracle connection is requested, oracle_fdw first checks if the server connection is still active and reconnects if not.

The problem here is that right before the OCITransStart call, everything is ok, so you get the error once.

Can you verify that after you get this error, the next SQL query against the foreign table will work again?

It might be worth catching this specific error at transaction start. It would be cool to have some criterion when an Oracle error means "try again" (there should be some "transparent application failover" errors with the same meaning).

from oracle_fdw.

The error could happen elsewhere too, e.g. if you start a transaction, do something in Oracle, then wait for the idle timeout and then do something else.
But in that case an error needs to be thrown anyway, because the remote transaction is gone.

But as I said, the next attempt to use the connection should work again, so maybe it would be a good thing to catch this error at transaction start...

from oracle_fdw.

the next SQL query against the foreign table still doesn't work, for the detail:

mydb=# \! date +'%F %T'              
2015-01-29 12:24:17
mydb=#  select 1 from foreign_table limit 1;
 ?column? 
----------
        1
(1 row)

mydb=#  select 1 from foreign_table limit 1;
ERROR:  error connecting to Oracle: OCITransStart failed to start a transaction
DETAIL:  ORA-02396: exceeded maximum idle time, please connect again
mydb=# \! date +'%F %T'              
2015-01-29 14:11:03
mydb=#  select 1 from foreign_table limit 1;
ERROR:  error connecting to Oracle: OCITransStart failed to start a transaction
DETAIL:  ORA-01012: not logged on
mydb=# \! date +'%F %T'              
2015-01-29 14:11:09
mydb=#  select 1 from foreign_table limit 1;
ERROR:  error connecting to Oracle: OCITransStart failed to start a transaction
DETAIL:  ORA-01012: not logged on

all this executed in the postgres that login through the termintal, and I didn't start a transaction.

from oracle_fdw.

Ok, I have just committed c269639 which should improve the situation.

Could you give it a try and see if it works for you?

from oracle_fdw.

Thank you very much! It works!

mydb=# \! date +'%F %T'
2015-02-02 15:18:30
mydb=# select 1 from  foreign_table limit 1;
 ?column? 
----------
        1
(1 row)

mydb=# \! date +'%F %T'                                                                                                       
2015-02-02 16:36:04
mydb=# select 1 from  foreign_table limit 1;
 ?column? 
----------
        1
(1 row)

from oracle_fdw.