Asyncpg: Prepared statements with connection pool

[Artimi]

Hi, I have troubles using prepared statements with connection pool. AFAIK you have to setup prepared statement for each connection you are using (https://www.postgresql.org/docs/9.3/static/sql-prepare.html):

Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again. This also means that a single prepared statement cannot be used by multiple simultaneous database clients; however, each client can create their own prepared statement to use.

So what is preferred way to work with prepared statements with connection pool? Should I, after connect, prepare statements for each connection of connection pool (how to iterate them?)? Should I use setup keyword of create_pool? But that is called each time acquire and we do not get references to statements easily. How to handle statement references? Dict of connection: statements?
Is there a way to do it with asyncpg? Could you send me an code example how to do it or even better add it to documentation?

[elprans]

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.

[Artimi]

Thank you for answers. I'll stick just to asyncpg LRU cache.

[Gyllsdorff]

So do I understand this correctly? @elprans

• If a PreparedStatement was made from a Connection that was acquired from a ConnectionPool, 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 after pool.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 the statement_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 on pool.release(con) or when the connection is actually closed to due to max_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?

[elprans]

So do I understand this correctly? @elprans

• If a PreparedStatement was made from a Connection that was acquired from a ConnectionPool, 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 after pool.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 the statement_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 on pool.release(con) or when the connection is actually closed to due to max_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.

[eirnym]

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:

1) validate syntax of SQL queries on the start of my app and it's quite useful during development and testing
2) 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).

[elprans]

@eirnym Prepared statements are a server resource and are per-connection.

[eirnym]

@elprans thank your for an explanation