search

paurkedal / ocaml-caqti

Cooperative-threaded access to relational data
https://paurkedal.github.io/ocaml-caqti/index.html
GNU Lesser General Public License v3.0
299 stars 36 forks source link

Setting Busy timeout for Sqlite3 Connections #93

Closed kiranandcode closed 2 years ago

kiranandcode commented 2 years ago

I'm using Caqti for a project using Sqlite3, but I've been running into BUSY errors when calling the API. After asking on the OCaml discuss, I've been suggested to configure the Sqlite connection using the sqlite3_busy_timeout function (defined here), however, this doesn't seem to be exposed via the Caqti API, and I can't easily get access to the underlying DB connection.

Would it be possible to add this function to the Caqti API somehow?

kiranandcode commented 2 years ago

Might be related to #56 , or could be solved by a bespoke function.

paurkedal commented 2 years ago

Yes, a solution to #56 would be to make the underlying connection object available. This can be done for sqlite3 and postgresql, but not (at least the way I imagined) for mariadb. As a more high-level solution, I am currently looking into #89.

paurkedal commented 2 years ago

I think there is a more immediate solution to this. PRAGMA busy_timeout is an SQL variant of the C function. For the connections derived from the pool, I suggest setting it in the ?post_connect callback of create_pool. Note that this PRAGMA (maybe all) does not work with query parameters and that it returns a row with an integer which seems to be the busy parameter just set; the Caqti type should therefore be unit -->! int.

kiranandcode commented 2 years ago

Ah, yes, perfect! Thanks for the prompt response - you're right, for this, I guess I can just send a PRAGMA busy_timeout for this situation. Thanks again for your hard work on maintaining this library.