search

go-goracle / goracle

Go database/sql driver for connecting to Oracle Database, using the ODPI-C library
273 stars 43 forks source link

Add support for OCI_ATTR_CALL_TIMEOUT #99

Closed rvegas closed 6 years ago

rvegas commented 6 years ago

Hello, the recent release of oracle instant client announced that C clients would get support for call timeout parameters, this would be a great addition.

https://docs.oracle.com/en/database/oracle/oracle-database/18/lnoci/handle-and-descriptor-attributes.html#GUID-D8EE68EB-7E38-4068-B06E-DF5686379E5E

You guys see this getting implemented anytime soon for goracle?

anthony-tuininga commented 6 years ago

This has been wrapped in ODPI-C (which goracle uses) as can be seen here.

tgulacsi commented 6 years ago

It's been implemented in e0af5022b0c4ee6056d0dadc67d20783b3d3286a , 10 days ago.

cjbj commented 6 years ago

The fine print on call timeout behavior can be seen in the Python cx_Oracle 7 release blog https://blogs.oracle.com/opal/python-cx_oracle-7-introduces-soda-document-storage

tgulacsi commented 6 years ago

Thanks for that pointer. Now I see that maybe I don't know how to use this timeout. The blog says the timeout is enforced per round-trip. What shall be a good timeout value? Now I set it to be the time till the context.Deadline, but maybe that's too much.

AFAIK a query will respond when it's first rows are ready, but even that may be a lot of time even it is correctly written - an ORDER BY without index or a simple COUNT() must finish totally before returning the first row.

And the longest is a simple PL/SQL block which wil send nothing back till finished. Am I right? Or does it send some "status report" periodically? Watching the DB side session stats, I doubt...

So is there a better rule-of-thumb deciding a good callTimeout value? I feel that the Context.Deadline may be too much, but I don't know better (yet).

cjbj commented 6 years ago

The rule of thumb is that it needs to be user settable. There are too many variables, from user requirements to network speeds, that make a generic default infeasible.

tgulacsi commented 6 years ago

Thanks, I've added a CallTimeout option to be able to set the timeout for each statement.

rvegas commented 6 years ago

Wow, thanks for the swift action, this is indeed very helpful.

cjbj commented 6 years ago

@rvegas let us know how it works out for you.

tgulacsi commented 6 years ago

It's in v2.8.1