Re: Prepared statement already exists

WireSpot wrote:
> I'm trying to use prepared statements in an application and I'm
> running into this error: "Query failed: prepared statement already
> exists".
> 
> The reason is obvious. What I want to know is the best way to avoid
> getting this error. The client application sets statement names as MD5
> of the actual query text, which means there's a possibility of a clash
> between different parts of the applications if they attempt to prepare
> the same query in the lifetime of a connection.
> 
> Possible solutions, please advise:
> 
> 1) Something like PREPARE IF NOT EXISTS. I'm guessing no such luck.
> 
> 2) Tweaking the Postgres error reporting to ignore this particular
> error. Is it possible? From a non-priviledged client connection?
> 
> 3) Reading a list of all the currently defined prepared statements to
> see if the one I want is already prepared. I'm hoping some "magic"
> SELECT in pg's internal tables may do the trick. But I also worry
> about introducing overhead this way.
> 
> I also imagined some workarounds in the code (PHP), such as defining a
> global/static hash table and registering statement names with it. But
> I'd like to know if there's a better way.

Do you still need the old prepared statement?

If not, you can simple DEALLOCATE it and then try the PREPARE again.

Something like that

try {
    PREPARE statementnam AS SELECT ....;
} catch (SQLException e) {
    if (e.getSQLState().equals("42P05")) {
        DEALLOCATE statementnam;
        PREPARE statementnam AS SELECT ....;
    } else
        throw e;
}

(that's Java pseudocode, but I hope you'll understand what I mean).

If you still need the old statement, generate a new, different name
and try again.

Yours,
Laurenz Albe