stack overflow for queries that return large results

[ghennequin]

This fails (stack overflow):

module S = Sqlexpr_sqlite.Make (Sqlexpr_concurrency.Id)
let db = S.open_db "db.sqlite"

let () =  (* create simple but large table *)
  S.execute db [%sql "CREATE TABLE test (x FLOAT)"];
  let insert = S.insert db [%sqlc "INSERT INTO test(x) VALUES(%f)"] in
  S.execute db [%sql "BEGIN TRANSACTION"];
  for i=1 to 100000 do
    insert (Random.float 1.) |> ignore
  done;
  S.execute db [%sql "COMMIT TRANSACTION"];

(* simple query - get the entire table; this fails with stack overflow *)
let result = S.select db [%sqlc "SELECT @f{x} FROM test"]

let () = S.close_db db

On my computer, it won't fail if I limit the query to return < 65202 rows. Any idea how to fix this? Thank you!

[tachukao]

I have the same problem but fails for ~24000 rows. It would be great to have a way to fix this! Thank you!

[ghennequin]

Also, note that this issue does not occur when using Sqlite3 directly, so it seems specific to sqlexpr.

[mfp]

On Sun, Mar 04, 2018 at 04:40:42PM +0000, Guillaume Hennequin wrote:

Also, note that this issue does not occur when using Sqlite3 directly, so it seems specific to sqlexpr.

I have been able to reproduce the issue. It seems the loop that iterates over the rows becomes non-tail-recursive when the Sqlexpr_sqlite.Make functor is applied to the identity monad Sqlexpr_concurrency.Id in a way that is not syntactically obvious. I suspect that this is caused by a non-tail-recursive (in the Id case) function call like backtrace_bind being inserted by the lwt ppx extension, but I haven't established this for sure yet.

In the meantime, you could try to use the non-blocking flavor Sqlexpr_sqlite_lwt or Sqlexpr_sqlite.Make(Sqlexpr_concurrency.Lwt), which do not exhibit the issue. The following works:

$ cat sqlfaillwt.ml
module S = Sqlexpr_sqlite.Make(Sqlexpr_concurrency.Lwt)
module Sqlexpr = S

let run () =
  let db = S.open_db "db.sqlite" in

  (* create simple but large table *)
  let%lwt () = S.execute db [%sql "CREATE TABLE test (x FLOAT)"] in

  let%lwt () =
    S.transaction db
      (fun db ->
         for%lwt i=1 to 100000 do
           let%lwt _ =
             S.insert db [%sqlc "INSERT INTO test(x) VALUES(%f)"] @@ Random.float 1.
           in
             Lwt.return_unit
         done) in

  let%lwt result = S.select db [%sqlc "SELECT @f{x} FROM test"] in
    Printf.printf "Read %d rows\n" @@ List.length result;
    Lwt.return @@ S.close_db db

let () = Lwt_main.run @@ run ()

$ ocamlfind ocamlopt -package sqlexpr,sqlexpr.ppx sqlfaillwt.ml -o sqlfaillwt -linkpkg -thread

$ ./sqlfaillwt 
Read 100000 rows

-- Mauricio Fernández

[ghennequin]

Thanks @mfp ! Looking forward to a fix for the Sqlexpr_concurrency.Id case.

[mfp]

Fixed in 1d08315b5e0. The let%lwt syntax was wrapping each iteration with multiple backtrace_bind calls, which broke tail-recursion with Id concurrency. The testcase runs OK now (tested on 4.02.3 and 4.05).

[ghennequin]

Thanks @mfp !