Custom pooling with SqlHydra

[Swoorup]

I am currently investigating an issue which appears to be a leak of connections. We have a custom pooling mechanism we use on top of npgsql and we are also using sqlHydra. Since SqlHydra expects a DbConnection we have wrapped the connection like the following:

type WrappedDatabase(pooledEntry: PooledEntry<IDbConnection>)=
  inherit DbConnection()

  let _inner = pooledEntry.Entry :?> NpgsqlConnection

  override _.ConnectionString = _inner.ConnectionString
  override _.ConnectionString with set value = _inner.ConnectionString <- value
  override _.ConnectionTimeout = _inner.ConnectionTimeout
  override _.Database = _inner.Database
  override _.DataSource = _inner.DataSource
  override _.ServerVersion = _inner.ServerVersion
  override _.State = _inner.State
  override _.BeginDbTransaction(il) = _inner.BeginTransaction(il) 
  override _.ChangeDatabase(dbName) = _inner.ChangeDatabase(dbName)
  override _.CreateDbCommand() = _inner.CreateCommand()
  override _.Open() = _inner.Open()
  override _.Close() = _inner.Close()

  override _.DisposeAsync() = (pooledEntry :> IAsyncDisposable).DisposeAsync()

  interface IAsyncDisposable with
    override _.DisposeAsync() = (pooledEntry :> IAsyncDisposable).DisposeAsync()

  interface IDisposable with
    override _.Dispose() = (pooledEntry :>IDisposable).Dispose()

let useContext (connBuilder: DbConnPool) =
  let compiler = PostgresCompiler()

  let createConnection () =
    task {
      let! loaned = connBuilder.Loan()
      let wrappedDb = new WrappedDatabase(loaned)
      return new QueryContext(wrappedDb, compiler)
    } |> Task.runSynchronously

  Create(createConnection)

For the looks of it, afaik doesn't SQLHydra clean up after the connections? Or am I missing something here. Just wanted to double check if anyone has run through the same issue.

[JordanMarr]

Can you show an example of what it looks like when you actually run a query?

[Swoorup]

@JordanMarr Thank you. It worked fine until today. And currently getting this. It might not even be related to SqlHydra (I am about 80% sure) but I am trying to eliminate potential places of issues, so just thought I'll run through it for other alternative ways to do the above or any tips.

It's a generic message at the moment, which doesn't tell much about where actually the connection is not being released.

DBMS: PostgreSQL (ver. 15.2)
Case sensitivity: plain=lower, delimited=exact
Driver: PostgreSQL JDBC Driver (ver. 42.6.0, JDBC4.2)
[53300] FATAL: remaining connection slots are reserved for non-replication superuser connections.

[JordanMarr]

Are you using the use keyword to dispose after your queries?

[Swoorup]

Yep, this is an example of the query.

 task {
      let ctx = Util.useContext connBuilder

      return!
        selectTask HydraReader.Read ctx {
          for a in table<approver> do
            select a.user_id
        }
    }

[JordanMarr]

selectTask in conjunction with ContextType.Create creates a task, executes your query and then disposes your connection for you. So, it seems fine from that aspect.

[Swoorup]

@JordanMarr thanks for running this through.

Fair enough, I'll close this issue for now. It would be nice to use IDbTransaction and a variant of CreateAsync instead of what I have 😄 .

[JordanMarr]

Yes, I think it would benefit from having a third DU case for async.

[JordanMarr]

v2.4.0-beta.6 is released.

You should now be able to write this:

module Utils = 
  let select' (pool: DbConnPool) = 
    let createConnection () = 
      task {
        let! loaded = pool.Loan()
        let wrappedDb = new WrappedDatabase(loaned)
        let compiler = PostgresCompiler()
        return new QueryContext(wrappedDb, compiler)
      }
      selectTask<'Selected, 'Mapped, 'Reader> HydraReader.Read (CreateTask createConnection)

  let selectShared (ctx: QueryContext) = 
    selectTask<'Selected, 'Mapped, 'Reader> HydraReader.Read (Shared ctx)

open Utils

let getApprovers (pool: DbConnPool) =
  select' pool {
    for a in table<approver> do
    select a.user_id
  }