[pgpool-general: 7444] Re: Supported python async drivers
Tatsuo Ishii
ishii at sraoss.co.jp
Thu Mar 18 09:34:06 JST 2021
> Ok, so I was able to get it to work... I thought - why don't I activate
> logging to see what is happening? And of course it then promptly started to
> work :-).
>
> If I set `log_client_messages = on` and `client_min_messages = log` (where
> it has to be *at least* log - notice level doesn't work, all the debugX
> do). then the asyncpg works fine.
>
> Interesting! Any ideas what might be going on?
No idea but I suspect the problem might be triggered by timing: with
logging, pgpool is somewhat slow down and may not trigger the problem.
It would be helpful if you attach gdb to hang pgpool process and
examine the process.
> Thanks again
>
> On Wed, 17 Mar 2021 at 23:09, Anton Melser <anton at transcrob.es> wrote:
>
>> 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 !!!
>>
>> 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.
>>
>> 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
>>>>
>>>
More information about the pgpool-general
mailing list