Repository navigation
[Feature Request] Allow to manage reset query for connection #780
Description
Every connection on release calls reset, which results in evaluation of this statements (for postgres caps):
SELECT pg_advisory_unlock_all();
CLOSE ALL;
UNLISTEN *;
RESET ALL;
So if you use postgres your caps predefined. But not every project uses this capabilities, so this calls looks like overhead.
Maybe add interface to specify server caps explicitly, this will reduce reset statements to only useful ones or even noop at all.
Something like pass server caps in init of connection or/and connection pool.
Reacted by Leonid
Activity
elprans commentedon Jul 8, 2021
There's already a way to override connection behavior: make a subclass and pass it in as
connection_classargument toconnect().alexeynavarkin commentedon Jul 8, 2021
Thanks for reply!
But is there something, that stopping implementation of this feature in default one?
As I see, almost in every project you would need to implement custom, almost one line class, if you want to optimize performance. In case of many short queries asyncpg will almost double qps to postgresql. I think many connection poolers has interface for tuning connection reset algorithm, in example pg_bouncer server_reset_query param.
I suppose, that most projects do not use all the caps.
We can keep default behavior, but add caps arg to init, that will override autodetection, or allow directly set reset query.
elprans commentedon Jul 8, 2021
Server capabilities aren't meant to be an API and overriding the detection is a bad idea, because new releases might add more caps for newer Postgres versions or alternative implementations and you overload will become outdated, which might lead to unexpected behavior. Basically, this is a dangerous way to achieve better performance.
Setting the reset query directly is a more reasonable approach. Feel free to send a PR.
mdespriee commentedon May 25, 2022
I stumbled upon this thread, because we noticed a lot of
pg_advisory_unlock_all, sometimes taking a significant amount of time to complete. But I don't see any use of advisory_locks in asyncpg codebase. But I may be mistaken.Is there any doc for these server capabilities ? And why there are these unused ones ?
LMalikov commentedon Jan 12, 2023
Just wondering if it's possible to add this reset query to the last executed query before connection is released instead of making a separate request that ends up in extra round trip to DB?
alexeynavarkin commentedon Jan 16, 2023
@LMalikov i think - it can not be done in that way.
At least when asyncpg executing query it can't understand that it is the last one query in a connection context.
alexeynavarkin commentedon Jan 16, 2023
@mdespriee i can't see any documentation about this.
But you can understand how that works from here:
https://github.com/MagicStack/asyncpg/blob/v0.27.0/asyncpg/connection.py#L93 - how caps determined.
https://github.com/MagicStack/asyncpg/blob/v0.27.0/asyncpg/connection.py#L1525 - how thay used to generate reset query that executed before connection release.
sandr8 commentedon Jan 18, 2023
The pgbouncer admin console complains with asyncpg clients:
is related, as it seems?
alexeynavarkin commentedon Jan 19, 2023
@sandr8
Looks like so, except the last one statement
use show help- that part not related to asyncpg.sandr8 commentedon Jan 20, 2023
That's part of the notice message from pgbouncer of course... which is quoting the command sent by pgbouncer. What I would like to understand, when I find the time, is what the deal is with the «capabilities» cited above and if, as it sounds, the server is supposed somehow to let the client know what the client can do, and the client should act accordingly.
alexeynavarkin commentedon Jan 20, 2023
@sandr8 i don't think you can do anything with it from pgbouncer/postgresql side.
This can be configured in asyncpg as @elprans suggested earlier or i need some time to create PR to make it more easy to config.
sandr8 commentedon Jan 21, 2023
I took a look at the code. I think the current approach is destined to become more complicated. For example, CockroachDB is soon going to support PL/pgSQL. At that point the code will have to branch on the CockroachDB version. Why not just dynamically
try...excepteach little bit of the reset query at the beginning of the (first) connection (within a pool) and see and remember which parts are accepted?Allow to specify custom connection reset query builder
Allow customizing connection state reset
Allow customizing connection state reset
Allow customizing connection state reset (MagicStack#1191)