Pool management questions

[JohnAD]

In part, this follows up on issue #2

I'm thinking of using your library for a few web sites; especially because of the pooling support. I especially like that your library pauses the query if the pool is full. That is a great response when the server get's a spike in traffic.

For a web site, it is key that the pool handle the connections long term and under threaded web server conditions.

The docs imply this library is thread-safe, but the docs show launching query threads. It does not explicitly say if it is safe to launch a query from within another thread.

So, if I where to use pg with the jester library, I'd like to call the library like this:

import htmlgen
import jester
import pg
import strutils

let pool = newAsyncPool("remote.host.domian", "user", "password", "dbname", 10)

routes:
  get "/":
    let rows = await pool.rows(sql"SELECT 1")
    resp h1("rows found: $1".format(rows.len))

So, the pg pool is created when the webserver starts and sits idle until web traffic arrives. Each query to "/" runs in it's own thread pulling from the shared pool.

Such a server can run for months or longer between resets.

So the questions:

1. Are the queries using global pg safe from contention issues from competing threads. (I suspect the answer is yes, given the test I see in tests, but I wanted to specifically ask.)
2. If one or more of the pool sessions has died, which will happen sometimes given the time frame, does the software automatically find a living thread? If not, what happens?

If you think it useful, I'm happy to write some documentation for use under jester (or asynchttpserver).

[ThomasTJdev]

I'm also interested in the answer to these questions!

[ranedk]

import htmlgen
import jester
import pg
import strutils

var pool {.threadvar.}: AsyncPool 
pool = newAsyncPool("remote.host.domian", "user", "password", "dbname", 10)

routes:
  get "/":
    let rows = await pool.rows(sql"SELECT 1")
    resp h1("rows found: $1".format(rows.len))

This should work. The pool is local to the thread and should work fine.

However, if there is any error in the SQL (e.g. wrong column name), it gives an "unhandled exception" and dies. This happens at libpq, so I don't know if this is fixable.

[treeform]

Sorry I never tested this library with real os threads. I only 1 single threader servers per core. Nim's threads+async never worked well together I recommending running multiple instances instead.