取消分配准备好的查询 [英] De-allocating prepared queries

问题描述

编辑：非常感谢Daniel和Dennis。问题现在已经解决，并且正如他们巧妙地指出的那样，在这种情况下的问题是程序员（特别是一直没有思考），希望我能接受这两个答案。

My thanks to both Daniel and Dennis. The problem is resolved now, and as they tactfully pointed out, the problem in this case was the programmer (specifically not thinking it all the way through) I wish I could accept both as answers.

注意：要说我是Postgresql的新手，就是侮辱新手！

我正在编写一个Web应用程序，其中将利用PostgreSQL数据库进行数据存储。到目前为止，我已经很好地掌握了创建查询以及从查询中检索结果的语法，无论是查找，删除，插入还是更新。但是，我遇到了一个难题。

I am writing a web app which will utilize a PostgreSQL database for it's data storage. In what I have done so far, I have managed a good grasp of the syntax for creating queries, and retrieving results from them, whether it be for a lookup, a deletion, an insertion, or an update. I have run in to one quandary however.

为避免SQL注入问题，请使用 pg_prepare（）/ pg_execute（）或 pg_query_params 。我使用的 pg_prepare（）/ pg_execute（）比其他的更多。但是每个查询都是一个四步过程，

To avoid SQL injection issues, the use of pg_prepare()/pg_execute() or pg_query_params is recommended. I am using more of the pg_prepare()/pg_execute() than I am of the other. But each query is then a 4 step process,

1. 准备查询字符串本身，


2. 准备在数据库上查询（使用pg_prepare）


3. 执行查询（pg_execute）


4. 处理/处理返回的数据。

1. prepare the query string itself,
2. prepare the query on the db (use pg_prepare)
3. execute the query (pg_execute)
4. handle/manipulate the returned data.

由于这是一个PHP脚本，因此准备好的查询不会在脚本终止时自动释放，因此需要通过以下调用手动完成：

Because this is a PHP script, the prepared query is not automatically deallocated upon script termination, so it needs to be done manually via call like:

pg_query（$ dbconn， DEALLOCATE'query_name';）

但是， DEALLOCATE SQL命令不会返回有关成功或失败的有用信息，因此在尝试确定 DEALLOCATE 指令，试图确定是否会变得一团糟：

However, the DEALLOCATE SQL command returns no useful information regarding success or failure, so when attempting to determine the results of the DEALLOCATE instruction, it becomes a mess trying to determine if:

1. 查询成功，因此释放b


2. 查询成功且取消分配失败


3. 查询失败，因此取消分配


4. 查询fa iled，并且解除分配成功（我什至认为这不可能发生）

1. The query succeeded and so did the deallocation
2. The query succeeded and the deallocation failed
3. the query failed and so did the deallocation
4. The query failed and the deallocation succeeded (I don't believe this can even happen)

我的问题是双重的

1. 如何（除非对服务器发出有关已释放查询的重复查询），我才能确定释放是否成功，并且


2. 是否有一种简单的方法来确定查询的哪一部分失败（在失败的情况下）是取消分配还是发送查询本身？

这个问题给出了部分解决方案，但是对查找错误的来源没有帮助。 PHP / PostgreSQL：检查是否已存在准备好的语句

This question gives a partial solution, but is no help in finding the SOURCE of the error. PHP/PostgreSQL: check if a prepared statement already exists

推荐答案

在取消分配语句时， pg_query 的返回值表示成功与否，就像任何实用程序语句一样。失败时，它应该返回false。例如：

When deallocating the statement, the return value of pg_query indicates success or not, like for any "utility statement". On failure it should return false. For example:

 if (!pg_query($cnx, "deallocate foobar")) {
   echo "Error deallocate: " . pg_last_error($cnx);
 }
 else {
  echo "deallocate successful";
 }

此显示：

错误解除分配：错误：预备语句 foobar不存在

Error deallocate: ERROR: prepared statement "foobar" does not exist

请注意，语句名称取消分配必须不要用单引号引起来，因为它是一个标识符，而不是字符串文字。如果由于字符问题需要将其封闭，可以使用 pg_escape_identifier （php> = 5.4.4）

Note that the statement name to deallocate must not be surrounded by single quotes, because it's an identifier, not a string literal. Should it need to be enclosed because of problematic characters, it can be done with pg_escape_identifier (php >=5.4.4)

要清理会话，甚至不需要遍历准备好的语句并逐个释放它们，您可以调用 DEALLOCATE ALL 仍然使用 pg_query 。

To clean up a session, it's not even necessary to iterate over the prepared statements and deallocate them one by one, you may call DEALLOCATE ALL instead, still with pg_query.

还有另一条语句在一个查询中进行更多清理：
全部丢弃

There's also another statement that does more cleanup in one query: DISCARD ALL

此外，如果脚本确实与postgres断开连接，这甚至都不是必需的，因为准备好的语句在其父会话中是本地的，并且死于

Also, none of this is even necessary if the script really disconnects from postgres, since prepared statements are local to their parent session and die with it.

在脚本之间使用连接重用时，显式清理是必要的，可以持久化通过PHP（ pg_pconnect ）或诸如 pgBouncer （尽管池管理程序本身可能会调用 DISCARD ALL ，具体取决于其配置）。

The explicit cleanup is necessary when using connection reuse between scripts, either with persistent connections by PHP (pg_pconnect), or a connection pooler like pgBouncer (although the pooler itself may call DISCARD ALL depending on its configuration).

这篇关于取消分配准备好的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！