db_postgres: automatic reconnect

[FedericoCeratto]

db_postgres should detect connection error and reconnect automatically using pqreset() Ideally with:

• exponential backoff
• configurable start interval
• configurable number of retries or timeout (set to 0 to disable reconnect)
• enabled by default

[juancarlospaco]

Having a general purpose retry with exponential backoff on std lib would be nice. You can use it for other stuff like httpclient downloads, etc.

[FedericoCeratto]

Also a simple thread-safe connection pool.

[francescop]

How would you implement this? On my end I wrote a simple function that uses libpq's PQreset function and it does what is supposed to do - the tryExec somehow always returns false, but i am investigating that.

it works means: it reconnects correctly

Something like this:

let db* = open("localhost", "postgres", "", "nim_prologue_quickstart")

proc keepAlive*() {.thread.} =
  {.gcsafe.}:
    while true:
      if not db.tryExec(sql"SELECT 1"):
        echo "db error, reconnecting..."
        pqreset(db) # this could be added to the stdlib
      sleep(1000)

On the stlib something like this could be added:

proc reset*(db: DbConn) {.tags: [DbEffect].} =
  # resets the database connection.
  pqreset(db) # pqreset is already defined in lib/wrappers/postgres.nim

Now, I don't know if something like this is acceptable, if you can create threads on the stdlib, if using channels to pass the db connection status is an option, etc