<div dir="ltr">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 :-).<div><br></div><div>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.</div><div><br></div><div>Interesting! Any ideas what might be going on?</div><div><br></div><div>Thanks again</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, 17 Mar 2021 at 23:09, Anton Melser <<a href="mailto:anton@transcrob.es">anton@transcrob.es</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">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. <div><br></div><div>The postgresql 13 is on port 5433, the pgpool2 on 5434.<br><div><br></div><div>I kept all default configurations, except for the host0 datadir. If I use psql, I can perform the same query with no problem:<div><br></div><div>(broadcaster) anton@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<br>Pager usage is off.<br> username<br>----------<br> user5<br>(1 row)<br><br>(broadcaster) anton@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<br>Pager usage is off.<br> username<br>----------<br> user5<br>(1 row)<br><br>But with the following python+asyncpg, it only works when pointing directly at the postgresql<br>````<br>import sys<br>import asyncio<br>import asyncpg<br><br>async def run(host, port):<br> conn = await asyncpg.connect(user='postgres', password='transpass',<br> database='transcrobes', host=host, port=port)<br> values = await conn.fetch(<br> 'SELECT username FROM auth_user WHERE id = $1',<br> 10,<br> )<br> await conn.close()<br> print(values)<br><br>loop = asyncio.get_event_loop()<br>print(sys.argv)<br>loop.run_until_complete(run(sys.argv[1], sys.argv[2]))<br>````<br><br>(broadcaster) anton@dwin:~/dev/tc/transcrobes$ python tmp/asyncpgtest2.py 127.0.0.1 5433<br>['tmp/asyncpgtest2.py', '127.0.0.1', '5433']<br>[<Record username='user5'>]<br>(broadcaster) anton@dwin:~/dev/tc/transcrobes$ python tmp/asyncpgtest2.py 127.0.0.1 5434<br>['tmp/asyncpgtest2.py', '127.0.0.1', '5434']<br>!!! Here hangs and doesn't return !!!<br></div></div></div><div><br></div><div>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.</div><div><br></div><div>Thanks!</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, 17 Mar 2021 at 22:27, Anton Melser <<a href="mailto:anton@transcrob.es" target="_blank">anton@transcrob.es</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Thanks very much for investigating that!. I experienced something very similar to <a href="https://github.com/MagicStack/asyncpg/issues/573" target="_blank">https://github.com/MagicStack/asyncpg/issues/573</a>, 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. <div><br></div><div>Thanks again for your quick reply on this!</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, 17 Mar 2021 at 21:20, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">>> Hi,<br>
>> <br>
>> I am using pgpool2 for failover (and maybe later load balancing, it's a<br>
>> long story...) and am starting to migrate some of my code to use async.<br>
>> Many async projects that support postgres I have seen do so via asyncpg.<br>
>> asyncpg doesn't seem to work at all with pgpool2 (at least with my current<br>
>> pgpool config, which works perfectly synchronously with psycopg2). Basic<br>
>> initial testing suggests aiopg does support pgpool2, at least for what I<br>
>> tested it with. This is probably because it is based on psycopg2-binary.<br>
>> <br>
>> Unfortunately almost all of the projects I have been looking at using that<br>
>> require async postgres only support asyncpg (Broadcaster, TortoiseORM,...),<br>
>> not aiopg.<br>
>> <br>
>> Is there an officially supported async driver for python? Is one planned?<br>
> <br>
> I am not familiar with "async" nor "asyncpg". To understand what they<br>
> are doing, it would be helpful to config Pgpool-II with<br>
> log_client_messages = on and share the pgpool log.<br>
<br>
I have tried with asyncpg myself. (see attached python<br>
program. Forgive me if there's something wrong. This is my first<br>
python script:-) With log_client_messages = on, and log_connections =<br>
on it seems Pgpool-II works fine.<br>
<br>
2021-03-17 22:15:33: child pid 28311: LOG: new connection received<br>
2021-03-17 22:15:33: child pid 28311: DETAIL: connecting host=127.0.0.1 port=33452<br>
2021-03-17 22:15:33: [unknown] pid 28311: LOG: Parse message from frontend.<br>
2021-03-17 22:15:33: [unknown] pid 28311: DETAIL: statement: "__asyncpg_stmt_1__", query: "SELECT 1;"<br>
2021-03-17 22:15:33: [unknown] pid 28311: LOG: DB node id: 0 backend pid: 28670 statement: SELECT version()<br>
2021-03-17 22:15:33: [unknown] pid 28311: LOG: DB node id: 2 backend pid: 28669 statement: Parse: SELECT 1;<br>
2021-03-17 22:15:33: [unknown] pid 28311: LOG: Describe message from frontend.<br>
2021-03-17 22:15:33: [unknown] pid 28311: DETAIL: statement: "__asyncpg_stmt_1__"<br>
2021-03-17 22:15:33: [unknown] pid 28311: LOG: DB node id: 2 backend pid: 28669 statement: D message<br>
2021-03-17 22:15:33: [unknown] pid 28311: LOG: Sync message from frontend.<br>
2021-03-17 22:15:33: [unknown] pid 28311: LOG: Bind message from frontend.<br>
2021-03-17 22:15:33: [unknown] pid 28311: DETAIL: portal: "", statement: "__asyncpg_stmt_1__"<br>
2021-03-17 22:15:33: [unknown] pid 28311: LOG: DB node id: 2 backend pid: 28669 statement: Bind: SELECT 1;<br>
2021-03-17 22:15:33: [unknown] pid 28311: LOG: Execute message from frontend.<br>
2021-03-17 22:15:33: [unknown] pid 28311: DETAIL: portal: ""<br>
2021-03-17 22:15:33: [unknown] pid 28311: LOG: DB node id: 2 backend pid: 28669 statement: Execute: SELECT 1;<br>
2021-03-17 22:15:33: [unknown] pid 28311: LOG: Sync message from frontend.<br>
2021-03-17 22:15:33: [unknown] pid 28311: LOG: Terminate message from frontend.<br>
2021-03-17 22:15:33: [unknown] pid 28311: LOG: DB node id: 0 backend pid: 28670 statement: DISCARD ALL<br>
2021-03-17 22:15:33: [unknown] pid 28311: LOG: DB node id: 2 backend pid: 28669 statement: DISCARD ALL<br>
<br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
</blockquote></div>
</blockquote></div>
</blockquote></div>