Prepared Statement connection is active and wait for ClientRead.

Hi team,

Right now, asyncpg does prepared statements in two steps.

1. Parse, Describe, Flush.
2. Bind, Execute, Sync.

Two coroutines represent these two steps. In a highly concurrent setup, the wall-clock gap between these two steps can be large. Why is it causing a problem for me? Command Flush does not change the connection state, i.e., active. So I observe a lot of queries show up as

 wait_event_type | wait_event | state
-----------------+------------+--------
      Client    | ClientRead  | active

in pg_stat_activity. This causes a lot of unnecessary confusion for our database monitors/observability tools. The connection is essentially idle between these two steps, so I think it is better to report it as wait_event=ClientRead but state=idle? Meanwhile, the command Sync will change the connection state to idle, so after step 2, the connection becomes idle.

My ask is: could we use Sync instead of Flush in step 1?

Moreover, there could be more than one bind step, so the sequence could be

1. Parse, Describe, Flush.
2. Bind, Execute, Sync.
3. Bind, Execute, Sync.
...

In this case, the connection is active only between step 1 and step 2, but idle for all other step gaps. I kind of feel this behavior is inconsistent.
I might have neglected some basic design about PostgreSQL extended query. Hope to hear from you soon!

Bắt đầu với luồng prepared-statement của asyncpg được mô tả trong issue và tài liệu về giao thức extended-query của PostgreSQL. Xác định liệu việc thay đổi trình tự Flush/Sync có thể duy trì tính đúng đắn khi có nhiều bước Bind/Execute hay không; hoàn thành có nghĩa là pg_stat_activity báo cáo trạng thái kết nối một cách nhất quán mà không làm hỏng các prepared statement.