Pgcat is not working with asyncpg python client and I am getting this error in logs ERROR `pgcat::client] Unexpected code: H`

[chethankumar4046]

** Pgcat is not working with asyncpg python client, but working psycopg2 python client**
Basically i am going to say pgcat not working with synchronisation with python

To Reproduce
Steps to reproduce the behavior:

1. use this image and deploy either in docker or kubernets ghcr.io/postgresml/pgcat:latest

2. And use bellow config to run deployment
   pgcat_config.txt

3. by running this code you will get issue
   pgcat_test.txt

[levkk]

Hi,

Thank you for reporting the issue. Could you run PgCat with RUST_LOG=debug and provide us with the output? Also curious what errors you get in Python. I think I have a hunch about what could be wrong (async protocol is slightly different than sync - PgCat actually implements the sync protocol I believe). We may need to listen for both server and client messages at the same time, since async sends multiple queries without waiting for server responses.

[chethankumar4046]

@levkk
As you sugguested i have run deployment with RUST_LOG=debug here is the log
[2023-04-10T07:21:56.208307Z DEBUG pgcat::client] Password authentication successful [2023-04-10T07:21:56.208456Z INFO pgcat::client] Client 127.0.0.1:57072 connected (plain) [2023-04-10T07:21:56.498748Z DEBUG pgcat::query_router] Query parser enabled: false [2023-04-10T07:21:56.498775Z DEBUG pgcat::client] Waiting for connection from pool [2023-04-10T07:21:56.498780Z DEBUG pgcat::pool] Address { id: 0, host: "db-cluster-ha.postgresql.svc.cluster.local", port: 5432, shard: 0, database: "test", role: Primary, replica_number: 0, address_index: 0, username: "temp_test4", pool_name: "temp", mirrors: [], stats: AddressStats { total_xact_count: 1, total_query_count: 3, total_received: 666, total_sent: 234, total_xact_time: 0, total_query_time: 2, total_wait_time: 29218, total_errors: 0, avg_query_count: 0, avg_query_time: 0, avg_recv: 41, avg_sent: 14, avg_errors: 0, avg_xact_time: 0, avg_xact_count: 0, avg_wait_time: 1826 } } is ok [2023-04-10T07:21:56.498805Z DEBUG pgcat::client] Got connection from pool [2023-04-10T07:21:56.498810Z DEBUG pgcat::client] Client 127.0.0.1:57072 talking to server Address { id: 0, host: "db-cluster-ha.postgresql.svc.cluster.local", port: 5432, shard: 0, database: "test", role: Primary, replica_number: 0, address_index: 0, username: "temp_test4", pool_name: "temp", mirrors: [], stats: AddressStats { total_xact_count: 1, total_query_count: 3, total_received: 666, total_sent: 234, total_xact_time: 0, total_query_time: 2, total_wait_time: 29221, total_errors: 0, avg_query_count: 0, avg_query_time: 0, avg_recv: 41, avg_sent: 14, avg_errors: 0, avg_xact_time: 0, avg_xact_count: 0, avg_wait_time: 1826 } } [2023-04-10T07:21:56.498823Z ERROR pgcat::client] Unexpected code: H

And output of python script is hungs for ever

[levkk]

I see. Looks like we just need to implement H (Flush) 1. When receiving that message, we need to write out the client buffer to the server.

[martiGIT]

Its still in-motion as pull request failed build in CI #412
any option to fix that?

[levkk]

Yeah, that PR isn't quite right. I haven't had a chance to take a look at it yet, but if you have some time, a PR implementing support for async is welcome.

[decarbonise]

Just wanted to bump this. I'd love to use pgcat but we rely on asyncg so we need this feature before we can do so. Thanks!

[olivierACRI]

Hi,
I am facing the same error using Nodejs Async library (node-postgres) but it looks like the query are working, so I would say that most of the issue is in the flooding errors in the logs.

[ayZagen]

My application with https://github.com/porsager/postgres library is not working also. I tried different pool modes (session and transaction) with prepared_statements_cache_size set to 0 or any number, still the queries fail with this error.

[optroodt]

Adding to this, hoping to give the issue some more attention. 😇

We are running into the same issue (pgcat-1 | 2024-08-02T09:15:29.638103Z ERROR ThreadId(07) pgcat::client: Unexpected code: H) and then hangs.

Our stack:

• FastAPI (async)
• SqlAlchemy
• asyncpg

It works fine using Flask (sync).

We'd love to start using PgCat!