[pgpool-general: 7443] Re: Supported python async drivers

[Tatsuo Ishii]

> So I was able to reproduce with just plain pgpool2 on my laptop. I have a
> WSL2 Ubuntu with postgresql 13 installed from the official Postgresql PPA,
> and pgpool2 4.1.4 installed from the same.
> 
> The postgresql 13 is on port 5433, the pgpool2 on 5434.
> 
> I kept all default configurations, except for the host0 datadir. If I use
> psql, I can perform the same query with no problem:
> 
> (broadcaster) anton at dwin:~/dev/tc/transcrobes$ PGPASSWORD=transpass psql -U
> postgres -h 127.0.0.1 -p 5433 -c 'select username from auth_user where id =
> 10' transcrobes
> Pager usage is off.
>  username
> ----------
>  user5
> (1 row)
> 
> (broadcaster) anton at dwin:~/dev/tc/transcrobes$ PGPASSWORD=transpass psql -U
> postgres -h 127.0.0.1 -p 5434 -c 'select username from auth_user where id =
> 10' transcrobes
> Pager usage is off.
>  username
> ----------
>  user5
> (1 row)
> 
> But with the following python+asyncpg, it only works when pointing directly
> at the postgresql
> ````
> import sys
> import asyncio
> import asyncpg
> 
> async def run(host, port):
>     conn = await asyncpg.connect(user='postgres', password='transpass',
>                                  database='transcrobes', host=host,
> port=port)
>     values = await conn.fetch(
>         'SELECT username FROM auth_user WHERE id = $1',
>         10,
>     )
>     await conn.close()
>     print(values)
> 
> loop = asyncio.get_event_loop()
> print(sys.argv)
> loop.run_until_complete(run(sys.argv[1], sys.argv[2]))
> ````
> 
> (broadcaster) anton at dwin:~/dev/tc/transcrobes$ python tmp/asyncpgtest2.py
> 127.0.0.1 5433
> ['tmp/asyncpgtest2.py', '127.0.0.1', '5433']
> [<Record username='user5'>]
> (broadcaster) anton at dwin:~/dev/tc/transcrobes$ python tmp/asyncpgtest2.py
> 127.0.0.1 5434
> ['tmp/asyncpgtest2.py', '127.0.0.1', '5434']
> !!! Here hangs and doesn't return !!!

So I have tested your script here and run for 1000 times but was not
able to reproduce the hang. The modified script attached
(test-anton.py).

> It looks like your attached script was removed by the list software, could
> you send the text in the body so I can try out on my end? Others also
> mentioned fetching/select queries in the ticket I referred to above.

I just forgot to attach the script. Sorry. The script attached (test.py).

> Thanks!
> 
> On Wed, 17 Mar 2021 at 22:27, Anton Melser <anton at transcrob.es> wrote:
> 
>> Thanks very much for investigating that!. I experienced something very
>> similar to https://github.com/MagicStack/asyncpg/issues/573, which was
>> also experienced by a few others. I will try and set up a basic
>> reproduction use case. I will note that it does appear that *all* of us are
>> using the Bitnami postgresql-ha images/helm chart... So it may actually be
>> a special config they use, or possibly related to having it in a docker
>> image... I'll investigate further and report back.
>>
>> Thanks again for your quick reply on this!
>>
>> On Wed, 17 Mar 2021 at 21:20, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>>
>>> >> Hi,
>>> >>
>>> >> I am using pgpool2 for failover (and maybe later load balancing, it's a
>>> >> long story...) and am starting to migrate some of my code to use async.
>>> >> Many async projects that support postgres I have seen do so via
>>> asyncpg.
>>> >> asyncpg doesn't seem to work at all with pgpool2 (at least with my
>>> current
>>> >> pgpool config, which works perfectly synchronously with psycopg2).
>>> Basic
>>> >> initial testing suggests aiopg does support pgpool2, at least for what
>>> I
>>> >> tested it with. This is probably because it is based on
>>> psycopg2-binary.
>>> >>
>>> >> Unfortunately almost all of the projects I have been looking at using
>>> that
>>> >> require async postgres only support asyncpg (Broadcaster,
>>> TortoiseORM,...),
>>> >> not aiopg.
>>> >>
>>> >> Is there an officially supported async driver for python? Is one
>>> planned?
>>> >
>>> > I am not familiar with "async" nor "asyncpg". To understand what they
>>> > are doing, it would be helpful to config Pgpool-II with
>>> > log_client_messages = on and share the pgpool log.
>>>
>>> I have tried with asyncpg myself. (see attached python
>>> program. Forgive me if there's something wrong. This is my first
>>> python script:-) With log_client_messages = on, and log_connections =
>>> on it seems Pgpool-II works fine.
>>>
>>> 2021-03-17 22:15:33: child pid 28311: LOG:  new connection received
>>> 2021-03-17 22:15:33: child pid 28311: DETAIL:  connecting host=127.0.0.1
>>> port=33452
>>> 2021-03-17 22:15:33: [unknown] pid 28311: LOG:  Parse message from
>>> frontend.
>>> 2021-03-17 22:15:33: [unknown] pid 28311: DETAIL:  statement:
>>> "__asyncpg_stmt_1__", query: "SELECT 1;"
>>> 2021-03-17 22:15:33: [unknown] pid 28311: LOG:  DB node id: 0 backend
>>> pid: 28670 statement: SELECT version()
>>> 2021-03-17 22:15:33: [unknown] pid 28311: LOG:  DB node id: 2 backend
>>> pid: 28669 statement: Parse: SELECT 1;
>>> 2021-03-17 22:15:33: [unknown] pid 28311: LOG:  Describe message from
>>> frontend.
>>> 2021-03-17 22:15:33: [unknown] pid 28311: DETAIL:  statement:
>>> "__asyncpg_stmt_1__"
>>> 2021-03-17 22:15:33: [unknown] pid 28311: LOG:  DB node id: 2 backend
>>> pid: 28669 statement: D message
>>> 2021-03-17 22:15:33: [unknown] pid 28311: LOG:  Sync message from
>>> frontend.
>>> 2021-03-17 22:15:33: [unknown] pid 28311: LOG:  Bind message from
>>> frontend.
>>> 2021-03-17 22:15:33: [unknown] pid 28311: DETAIL:  portal: "", statement:
>>> "__asyncpg_stmt_1__"
>>> 2021-03-17 22:15:33: [unknown] pid 28311: LOG:  DB node id: 2 backend
>>> pid: 28669 statement: Bind: SELECT 1;
>>> 2021-03-17 22:15:33: [unknown] pid 28311: LOG:  Execute message from
>>> frontend.
>>> 2021-03-17 22:15:33: [unknown] pid 28311: DETAIL:  portal: ""
>>> 2021-03-17 22:15:33: [unknown] pid 28311: LOG:  DB node id: 2 backend
>>> pid: 28669 statement: Execute: SELECT 1;
>>> 2021-03-17 22:15:33: [unknown] pid 28311: LOG:  Sync message from
>>> frontend.
>>> 2021-03-17 22:15:33: [unknown] pid 28311: LOG:  Terminate message from
>>> frontend.
>>> 2021-03-17 22:15:33: [unknown] pid 28311: LOG:  DB node id: 0 backend
>>> pid: 28670 statement:  DISCARD ALL
>>> 2021-03-17 22:15:33: [unknown] pid 28311: LOG:  DB node id: 2 backend
>>> pid: 28669 statement:  DISCARD ALL
>>>
>>> Best regards,
>>> --
>>> Tatsuo Ishii
>>> SRA OSS, Inc. Japan
>>> English: http://www.sraoss.co.jp/index_en.php
>>> Japanese:http://www.sraoss.co.jp
>>>
>>
-------------- next part --------------
import sys
import asyncio
import asyncpg

async def run(host, port):
    conn = await asyncpg.connect(user='t-ishii', password='transpass',
                                 database='test', host=host, port=port)
    values = await conn.fetch(
        'SELECT username FROM auth_user WHERE id = $1',
        10,
    )
    await conn.close()
    print(values)

loop = asyncio.get_event_loop()
print(sys.argv)
loop.run_until_complete(run(sys.argv[1], sys.argv[2]))
-------------- next part --------------
import asyncpg
import asyncio

async def main():
    dsn = 'postgresql://t-ishii:pass@localhost:11000/test'
    conn = await asyncpg.connect(dsn)
    answer = await conn.fetch('''SELECT 1;''')
    await conn.close()

asyncio.get_event_loop().run_until_complete(main())