De-allocating prepared queries

Question

EDIT: 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.

NOTE: To say I am a newbie to postgresql is to insult the newbies!

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.

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. 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.

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';")

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. 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)

My question is then two-fold"

1. How (barring repeated queries to the server regarding the deallocated query) can I determine if the deallocation was successful, and
2. Is there an easy way to determine which part of the query failed ( in the event of a failure) the deallocation, or the sending of the query itself?

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

Answer 1

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";
 }

This displays:

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

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)

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

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.

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).

Answer 2

It shouldn't be necessary to deallocate at all. PostgreSQL deallocates prepared statements as soon as the session ends: http://www.postgresql.org/docs/9.3/static/sql-deallocate.html

Edit: Despite your claim that it is, I cannot see why that would be different for PHP scripts. If you use pooled connections, then you want the prepared statements to be kept as well, instead of them being compiled once again on each run of the script.