Prepared Statements Already Exists

Postgres - ERROR: prepared statement S_1 already exists

This turned out to be a pgBouncer issue that occurs when using anything other than session pooling. We were using transaction pooling, which apparently can't support prepared statements. By switching to session pooling, we got around the issue.

Unfortunately, this isn't a good fix for our use case. We have two separate uses for pgBouncer: one part of our system does bulk updates which are most efficient as prepared statements, and another part needs many connections in very rapid succession. Since pgBouncer doesn't allow switching back and forth between session pooling and transaction pooling, we're forced to either run two separate instances on different ports just to support our needs, or to implement this patch. Preliminary testing shows it to work well, but time will tell if it proves to be safe and effective.

prepared statement already exists

Using pg_query($link, "DEALLOCATE ALL"); before the prepare fixed the problem.

However the troubling issue is that the only place prepare statement was used for that name query name was at that location. After turning off my machine and coming back to puzzle I tried the process that ran the code and it said that the statement already existed even though that was the first time the code was run.

Php postgresql error, prepared statement already exists

Prepare execute does not permit duplicate naming, so that is your error.
A query should only be prepared once, for example, in a cycle for the preparation state must be set out of the for and its execution in the for.

 $result=$pg_prepare($connection,"my_query7",$query);
    for($id=1;$id<3;$id++){
      $result=pg_execute($connection,"my_query7",array($l_country,$user_id));
       ...
    }

In your case using a functio that use the prepare and execute multiple times it's a problem.

What are you trying to accomplish with this function dispatches more code like where you are calling the function. This way I might be able to help you.

If you want to use functions I would use this method

Exemple from https://secure.php.net

<?php
function requestToDB($connection,$request){
    if(!$result=pg_query($connection,$request)){
        return False;
    }
    $combined=array();
    while ($row = pg_fetch_assoc($result)) {
        $combined[]=$row;
    }
    return $combined;
}
?>

<?php
$conn = pg_pconnect("dbname=mydatabase");

$results=requestToDB($connect,"select * from mytable");

//You can now access a "cell" of your table like this:
$rownumber=0;
$columname="mycolumn";

$mycell=$results[$rownumber][$columname];
var_dump($mycell); 

If you whant to use preaper and execute functions try to create a function that creates the preparations only once in a session. Do not forget to give different names so that the same error does not occur. I tried to find something of the genre and did not find. If you find a form presented here for others to learn. If in the meantime I find a way I present it.

PHP/PostgreSQL: check if a prepared statement already exists

One way (I hope someone will point out a simpler one):

<?
$prepared_statement_name = 'activity1';
$mydbname = '...';

$conn = pg_connect("host=... port=... dbname=... user=... password=...");

$result = pg_query_params($conn, 'SELECT name FROM pg_prepared_statements WHERE name = $1', array($prepared_statement_name));

if (pg_num_rows($result) == 0) {
    $result = pg_prepare($conn, $prepared_statement_name, 'SELECT * FROM pg_stat_activity WHERE datname =  $1');
}

$result = pg_execute($conn, $prepared_statement_name, array($mydbname));
while($row = pg_fetch_row($result)) {
    var_dump($row);
}

Ruby `prepare': ERROR: prepared statement should_insert already exists (PG::DuplicatePstatement)

I was able to get this working by deallocating the prepared statement. I inserted this line right after the exec_prepared statement.

db_connection.exec("DEALLOCATE should_insert")

---

Related Topics