From: | "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "WireSpot *EXTERN*" <wirespot(at)gmail(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Prepared statement already exists |
Date: | 2008-11-20 08:56:54 |
Message-ID: | D960CB61B694CF459DCFB4B0128514C202C68DED@exadv11.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | hendra kusuma | 2008-11-20 09:00:37 | Serial - last value |
Previous Message | Andy Greensted | 2008-11-20 08:44:49 | Re: Connecting to old 7.1 Database |