PostgreSQL服务器端准备语句的寿命是多长？

问题描述

根据，准备好的语句被绑定到数据库会话/ connection：

，：

哪一个是真的？

只要数据库连接已打开，准备好的语句就会存在，因此在使用连接池时，只要池未明确关闭物理

解决方案

，则会清除服务器端预处理语句。 >所以，你的问题终于归结为如何 java.sql.PreparedStatement 播放与PostgreSQL。

这里的答案取决于你使用的JDBC驱动程序。

TL; DR ：在现代驱动程序服务器准备语句中，直到连接消失或直到语句被另一个驱逐（常规LRU逐出）。 >

注意：PostgreSQL服务器不能跨数据库连接共享预准备语句，因此最好的JDBC驱动程序可以保持计划在每个连接中缓存。

注意：JDBC规范强制使用？，？用于绑定占位符，而服务器需要 $ 1，$ 2 因此JDBC驱动程序也缓存所谓的解析的SQL文本。

有两个众所周知的JDBC驱动程序：pgjdbc和pgjdbc-ng

pgjdbc

由于当使用 PreparedStatement 时，它会自动缓存服务器端计划。
注意：即使您 close（） PreparedStatement ，语句也会被缓存。
为了得到服务器端的准备，你需要执行查询5次（可以通过 prepareThreshold 配置）。

目前，缓存是每个连接实现的。默认情况下，pgjdbc缓存256（ preparedStatementCacheQueries ）查询和 preparedStatementCacheSizeMiB 查询。这是一个保守的设置，因此您可能需要调整它。有关属性的说明，请参见。
缓存包含解析的和服务器准备的语句。​​

github问题：

pgjdbc-ng

我没有进入pgjdbc-ng，但它看起来像是解析（默认缓存大小是查询）和服务器准备（默认缓存大小为查询）。服务器端预处理语句的支持已于2014年2月24日上线，因此如果您使用某些最新版本，可以获取语句缓存。

注意：如果您不小心使用非常因为pgjdbc-ng不能根据保留的字节数逐出条目。

由于长整型查询，您可以按 OutOfMemory 缓存是每个连接，因此即使你关闭语句，它也是透明的。

我不能说很多关于pgjdbc-ng的性能，虽然从上次我试图抛出

github问题：

服务器准备的计划

PostgreSQL有 PREPARE 和 DEALLOCATE 命令， code> EXEC 。它优化了两件事：

1. 使用 PREPARE d语句时（换句话说，服务器准备的），客户端不必一次又一次发送查询文本。它只是发送一个简短的查询名称和绑定变量的值。


2. 自从9.2以来，数据库仍然尝试重新安装查询的前几个执行。它这样做以尝试如果查询需要多个计划或如果通用计划足够好。最终（如果查询没有参数，则立即），数据库。

换句话说， PreparedStatement

更多信息：

在PL / pgSQL中准备的语句

按照文档，PostgreSQL 计划。这发生在几次执行之后（3或5，我不记得确切的阈值），所以在创建存储过程后，它可能有点慢，但是它会切换到缓存计划（如果数据库同意使用通用计划对于特定的查询）。

换句话说，为了实现缓存执行计划，您需要使用最新的JDBC驱动程序，你对存储过程的查询。
在每次执行时将调用过程，但调用本身通常比构成过程的查询短得多。

According to the PostgreSQL documentation, a prepared statement is bound to a database session/connection:

But then, Markus Winand (author of SQL Performance Explained) says that:

Which one is true?

Does the prepared statement live as long as the database connection is open, so when using a connection pool this can live as long as the pool doesn't explicitly closes the physical connection or does the server-side prepared statement is wiped out once the JDBC PreparedStatement is closed.

解决方案

So, your question finally boils down to "how java.sql.PreparedStatement plays with PostgreSQL". See answer on "how this plays with server-prepared plans" in the end.

Here's the answer: that depends on the JDBC driver you use.

TL;DR: in modern drivers server-prepared statement lives until connection dies or until the statement is evicted by another one (regular LRU eviction).

Note: PostgreSQL server cannot share prepared statement across database connections, thus the best JDBC driver can do is to keep plan cached in each connection.

Note: JDBC spec mandates usage of ?, ? for bind placeholders, while server wants $1, $2 thus JDBC drivers cache so-called parsed SQL texts as well.

There are two well-known JDBC drivers: pgjdbc and pgjdbc-ng

pgjdbc

https://github.com/pgjdbc/pgjdbc

Since pgjdbc 9.4-1202 it automatically caches server-side plans when using PreparedStatement.Note: the statements are cached even if you close() the PreparedStatement.In order to get to server-side prepare, you need to execute the query 5 times (that can be configured via prepareThreshold).

Currently, the cache is implemented per-connection. By default pgjdbc caches 256 (preparedStatementCacheQueries) queries and up to preparedStatementCacheSizeMiB of queries. This is a conservative setting, so you might want adjusting it. See documentation for the description of properties.The cache includes both parsed and server-prepared statements.

github issue: https://github.com/pgjdbc/pgjdbc/pull/319

pgjdbc-ng

https://github.com/impossibl/pgjdbc-ng

I'm not into pgjdbc-ng, however it looks like it does both parsing (default cache size is 250 queries) and server-preparing (default cache size is 50 queries). The support of server-side prepared statements landed on 24 Feb 2014, so if you use somewhat recent version, you can get statement caching.

Note: if you accidentally use very long queries, you can hit OutOfMemory since pgjdbc-ng cannot evict entries based on the number of retained bytes.

The cache is per-connection, thus it is transparently used even if you close statements.

I cannot say much about pgjdbc-ng performance though since last time I tried to throw jmh at it it failed with random exceptions.

github issue: https://github.com/impossibl/pgjdbc-ng/pull/69

Server-prepared plans

PostgreSQL has PREPARE and DEALLOCATE commands to reference the statement when sending EXEC over the wire. It optimizes two things:

1. When using PREPAREd statement (in other words, server-prepared one), client does not have to send query text again and again. It just sends a short query name and the values for bind variables.
2. Since 9.2, database still tries to replan first few executions of a query. It does so to try if the query needs multiple plans or if generic plan is good enough. Eventually (immediately if the query has no parameters), the database might switch to a generic plan.

In other words, PreparedStatement optimizes both query parsing at JDBC side and query planning at database side.

More info here: http://blog.endpoint.com/2014/04/custom-plans-prepared-statements-in.html

Prepared statements in PL/pgSQL

As per documentation, PostgreSQL caches plans for queries used in PL/pgSQL. This happens after a few executions (3 or 5, I do not remember the exact threshold), so after you create stored procedure it might be a bit slow, however then it will switch to cached plans (provided the database agrees to use generic plan for a particular query).

In other words in order to achieve "cached execution plans", you either need to use up to date JDBC driver, or you can wrap all your queries into stored procedures.The call to procedure will replan at each execute, however the call itself is typically much shorter than queries that compose the procedure.

这篇关于PostgreSQL服务器端准备语句的寿命是多长？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！