Comments (7)
So do I understand this correctly? @elprans
- If a
PreparedStatement
was made from aConnection
that was acquired from aConnectionPool
, the prepared statement is not really invalid until the connection is closed, for example due to inactivity, but since there is no way for us to know when that happens all statements are considered invalid afterpool.release(con)
.
Correct. Any resources derived from a connection released to the pool become invalid. This includes explicit prepared statements.
PreparedStatement
made from pools should only be used if you have a tight loop where you insert or run the same complicated query many times before releasing the connection.
Yes, but normally you don't need an explicit prepared statement. asyncpg query LRU cache will do that for you.
- All queries sent using
con.fetch/fetchall/execute/executemany
are turned into prepared statements in the background and can be cached in a LRU cache depending on the connection settings. The next time the same query on that connection is used the prepared statement is used automatically if thestatement_cache
is not disabled and it haven't been pushed out of the cache due to size or age.
Correct.
Question:
- Does the
Connection
query LRU cache get invalidated onpool.release(con)
or when the connection is actually closed to due tomax_inactive_connection_lifetime
/max_queries
?
The LRU cache does not get invalidated on pool.release()
.
- If we always use a few identical queries, do we still get the advantage of the pre-planing the queries due to the LRU query cache or will using
conn.prepare()
give us some other performance advantage?
conn.prepare()
is mostly useful for occasions when you don't want to use the cached prepared statement for a specific query and instead prepare and execute it explicitly. You may want to do that if the generic plan created for the query is bad (see #243). In normal situations simply rely on the LRU cache.
from asyncpg.
Prepared statements are connection-specific, yes. If you really need to keep prepared statements around, then a WeakKeyDictionary conn: statements should work. asyncpg keeps it's own LRU cache of prepared statements per-connection, so you don't need to cache them unless you absolutely need to.
from asyncpg.
Thank you for answers. I'll stick just to asyncpg
LRU cache.
from asyncpg.
So do I understand this correctly? @elprans
- If a
PreparedStatement
was made from aConnection
that was acquired from aConnectionPool
, the prepared statement is not really invalid until the connection is closed, for example due to inactivity, but since there is no way for us to know when that happens all statements are considered invalid afterpool.release(con)
. PreparedStatement
made from pools should only be used if you have a tight loop where you insert or run the same complicated query many times before releasing the connection.- All queries sent using
con.fetch/fetchall/execute/executemany
are turned into prepared statements in the background and can be cached in a LRU cache depending on the connection settings. The next time the same query on that connection is used the prepared statement is used automatically if thestatement_cache
is not disabled and it haven't been pushed out of the cache due to size or age.
Question:
Prepared statements and cursors returned by Connection.prepare() and Connection.cursor() become invalid once the connection is released.
-
Does the
Connection
query LRU cache get invalidated onpool.release(con)
or when the connection is actually closed to due tomax_inactive_connection_lifetime
/max_queries
? -
If we always use a few identical queries, do we still get the advantage of the pre-planing the queries due to the LRU query cache or will using
conn.prepare()
give us some other performance advantage?
from asyncpg.
Am I correct to say, that prepared statement data itself in it's nature is the same for one connection or from an another connection from the same connection pool, assuming database is the same?
If yes, I'd prefer to compile SQL requests first and use them second. This would give me an opportunity to:
- validate syntax of SQL queries on the start of my app and it's quite useful during development and testing
- save a lot of time as it's quite usual that I query a huge object from a database about 100 times using ~7 queries per an object. (less typical ~2000 times to request of an object with the same structure).
from asyncpg.
@eirnym Prepared statements are a server resource and are per-connection.
from asyncpg.
@elprans thank your for an explanation
from asyncpg.
Related Issues (20)
- Asyncpg (asyncio/asyncpg.py, line 62, in get_query) get_query throws index error.
- no binary format encoder for type jsonb
- Whenever i create a table with a `real` column and read the value i get something different then what i saved HOT 1
- insufficient data in buffer
- GCP CloudSQL Connector with asyncpg Pool HOT 1
- cursor prefetch with bounded lag
- asyncpg.connect: ValueError: invalid literal for int() with base 10 HOT 5
- Connection secrets displayed in clear text in logs
- RDS Proxy Pinning HOT 1
- Any ETA on releasing the latest version with GSS? HOT 1
- Timeout configuration for multi-host config
- Terminate function does not work when a stream is cancelled prematurely when in an anyio task group HOT 2
- Extremely long delay grabbing type info for string array (and likely other types) on CockroachDB HOT 11
- SELECT query to database returns wrong decimal values. HOT 3
- max_inactive_connection_lifetime param not respected in a conn pool HOT 1
- CockroachDB first query huge 7 seconds delay!! HOT 2
- Connection pool docs inconsistency? HOT 2
- Not accepting str as value for interval/date columns
- Does asyncpg support using `pg_service.conf` for connection parameters?
- No rows returned by fetch() when for DELETE rewritten to UPDATE using rule
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from asyncpg.