Patroni based solutions support

[mixa2130]

Hello! Has anybody tried this lib with Patroni? Does the connection restore work correctly when switching nodes?

[mosquito]

@mixa2130 there is no easy way to check this, you need a patroni-cluster and run this experiment on it, but if you have patroni I will be glad to know about your results.

[mixa2130]

Traceback (most recent call last):
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/site-packages/hasql/base.py", line 452, in _check_pool_task
    await self._periodic_pool_check(pool, dsn, sys_connection)
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/site-packages/hasql/base.py", line 509, in _periodic_pool_check
    await asyncio.wait_for(
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/asyncio/tasks.py", line 494, in wait_for
    return fut.result()
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/site-packages/hasql/base.py", line 569, in _refresh_pool_role
    is_master = await self._is_master(sys_connection)
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/site-packages/hasql/asyncpg.py", line 20, in _is_master
    read_only = await connection.fetchrow("SHOW transaction_read_only")
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/site-packages/asyncpg/connection.py", line 678, in fetchrow
    data = await self._execute(
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/site-packages/asyncpg/connection.py", line 1658, in _execute
    result, _ = await self.__execute(
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/site-packages/asyncpg/connection.py", line 1683, in __execute
    return await self._do_execute(
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/site-packages/asyncpg/connection.py", line 1730, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg/protocol/protocol.pyx", line 201, in bind_execute
asyncpg.exceptions.InvalidSQLStatementNameError: prepared statement "__asyncpg_stmt_1__" does not exist
HINT:  
NOTE: pgbouncer with pool_mode set to "transaction" or
"statement" does not support prepared statements properly.
You have two options:
* if you are using pgbouncer for connection pooling to a
  single server, switch to the connection pool functionality
  provided by asyncpg, it is a much better option for this
  purpose;
* if you have no option of avoiding the use of pgbouncer,
  then you can set statement_cache_size to 0 when creating
  the asyncpg connection object.

I've changed pool_factory in asyncpg.py to:

    async def _pool_factory(self, dsn: Dsn):
        return await asyncpg.create_pool(str(dsn), statement_cache_size=0,  **self.pool_factory_kwargs)

This disables prepared statements which doesn't work with pgbouncer in transaction mode. But such an action does not help - getting time out(((

Cancelled error for dsn='postgresql://****************/ml360'
Traceback (most recent call last):
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/asyncio/runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/asyncio/base_events.py", line 616, in run_until_complete
    return future.result()
  File "/Users/19459459/PycharmProjects/bitbucket/griffon/common-internal/src/main/resources/export_to_postgres/check_hasql.py", line 21, in create_pool
    await pool.ready(masters_count=1, replicas_count=1)
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/site-packages/hasql/base.py", line 354, in ready
    await asyncio.wait_for(
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/asyncio/tasks.py", line 501, in wait_for
    raise exceptions.TimeoutError()
asyncio.exceptions.TimeoutError

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/site-packages/hasql/base.py", line 452, in _check_pool_task
    await self._periodic_pool_check(pool, dsn, sys_connection)
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/site-packages/hasql/base.py", line 523, in _periodic_pool_check
    await asyncio.sleep(self._refresh_delay)
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/asyncio/tasks.py", line 659, in sleep
    return await future
asyncio.exceptions.CancelledError
Cancelled error for dsn='postgresql://*********************/ml360'
Traceback (most recent call last):
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/asyncio/runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/asyncio/base_events.py", line 616, in run_until_complete
    return future.result()
  File "/Users/19459459/PycharmProjects/bitbucket/griffon/common-internal/src/main/resources/export_to_postgres/check_hasql.py", line 21, in create_pool
    await pool.ready(masters_count=1, replicas_count=1)
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/site-packages/hasql/base.py", line 354, in ready
    await asyncio.wait_for(
  File "/Users/a19459459/opt/anaconda3/lib/python3.8/asyncio/tasks.py", line 501, in wait_for
    raise exceptions.TimeoutError()
asyncio.exceptions.TimeoutError

[mixa2130]

@mosquito It's a pity but I think in my Patroni configuration this awesome lib won't work, like in most others cluster configurations. HAProxy forwards the connection to whichever node is currently the primary. It does this using a REST endpoint that Patroni provides. Patroni ensures that, at any given time, only the primary Postgres node will appear as online, forcing HAProxy to connect to the correct node. So, as I've found, HAProxy with Patroni ensures master connection.