Thread: Prepared statement already exists
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.
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
On Thu, Nov 20, 2008 at 10:56, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > Do you still need the old prepared statement? > > If not, you can simple DEALLOCATE it and then try the PREPARE again. Yes, I'd like to keep the old statements, that's part of the perks -- if a query will be repeated it will (possibly) benefit from the statement being already prepared.
On Wed, Nov 19, 2008 at 2:42 PM, WireSpot <wirespot@gmail.com> 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. pg_prepared_statements (on recent versions of postgresql) also, watch out for race conditions. merlin
Please, send your replies to the list as well. WireSpot wrote: > > Do you still need the old prepared statement? > > > > If not, you can simple DEALLOCATE it and then try the PREPARE again. > > Yes, I'd like to keep the old statements, that's part of the perks -- > if a query will be repeated it will (possibly) benefit from the > statement being already prepared. I see. Then you'll have a way to remember the names of prepared statements, because otherwise you cannot reuse them. You'll have to find a way to pick or generate unique names for the prepared statements. You could check for name collisions and disambiguate with a suffix or something. Yours, Laurenz Albe
Merlin Moncure escribió: > On Wed, Nov 19, 2008 at 2:42 PM, WireSpot <wirespot@gmail.com> wrote: > > 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. > > pg_prepared_statements (on recent versions of postgresql) > > also, watch out for race conditions. What race conditions? Prepared statements are per-connection. Perhaps the application could keep a hash of statements prepared so far, to avoid having to query pg_prepared_statements all the time. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> Merlin Moncure escribió: >> pg_prepared_statements (on recent versions of postgresql) Thank you, that's one of the things I wanted to know. On Thu, Nov 20, 2008 at 15:30, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Merlin Moncure escribió: >> also, watch out for race conditions. > > What race conditions? Prepared statements are per-connection. I guess he means if connections are persistent, or if the same connection is being used at the same time from different parts of the application. Which brings us to the next point: > Perhaps the application could keep a hash of statements prepared so far, > to avoid having to query pg_prepared_statements all the time. That's how I dealt with it so far, purely in the code. But see the above scenarios. If the connection is reused but the code is fresh (being PHP and living in a webpage lifecycle) the hash in the code will be empty but the statements are in fact still there. Or if parts of the application issue their own PREPAREs without going through the central mechanism with the hash (granted, this is an internal issue of code discipline). Still, I don't wanna have to query pg_prepared_statements for every query I make. I feed that a hybrid approach might be best. Early in the lifecycle of every script I could load pg_prepared_statements in the code hashtable, then the mechanism could proceed normally, checking only against the hashtable.
On Wed, Nov 19, 2008 at 09:42:33PM +0200, WireSpot wrote: > 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. Have you thought about using stored procedures instead of prepared statements? No need to register them or keep track of that state. Sam
On Thu, Nov 20, 2008 at 15:45, Sam Mason <sam@samason.me.uk> wrote: > On Wed, Nov 19, 2008 at 09:42:33PM +0200, WireSpot wrote: >> 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. > > Have you thought about using stored procedures instead of prepared > statements? No need to register them or keep track of that state. I'm not sure if it would work. What I'm trying to do is have an application layer which takes all the client queries and makes prepared statements out of them. Do you mean to say I should make stored procedures out of them instead? Granted, CREATE FUNCTION has OR REPLACE, but other than that it's only complicating matters. A function needs to have the argument types defined, for example, I can't get away with simply listing them like I do when executing a statement.
WireSpot escribió: > I guess he means if connections are persistent, or if the same > connection is being used at the same time from different parts of the > application. I guess if connections are persistent, you could clear them before each usage with DISCARD (8.3 only) -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Thu, Nov 20, 2008 at 04:03:08PM +0200, WireSpot wrote: > On Thu, Nov 20, 2008 at 15:45, Sam Mason <sam@samason.me.uk> wrote: > > Have you thought about using stored procedures instead of prepared > > statements? No need to register them or keep track of that state. > > I'm not sure if it would work. What I'm trying to do is have an > application layer which takes all the client queries and makes > prepared statements out of them. I think it depends on what level this abstraction wants to live. If its purpose is just to make queries run a bit quicker then, no, stored procedures aren't going to help at all. If this abstraction can be given knowledge of the things that you actually want to do against the database then stored procedures could be a win. The best structure will be determined by your problem, I just thought that mentioning another solution may push your mental model around a bit to help clarify the "best" solution. Sam
On Thu, Nov 20, 2008 at 16:07, Alvaro Herrera <alvherre@commandprompt.com> wrote: > I guess if connections are persistent, you could clear them before each > usage with DISCARD (8.3 only) Again, I'd be losing the advantage of the already prepared statements. Basically, what it comes down it is I want to benefit as much as possible from previously prepared statements, while at the same time avoiding name clashes.
Albe Laurenz wrote: > You'll have to find a way to pick or generate unique names for the > prepared statements. > You could check for name collisions and disambiguate with a suffix > or something. By the way, why do the prepared statements require to be named at all? With other DBMS such as oracle or mysql, one can prepare statements without providing any name for them: the prepare() step returns a "statement handle" that is to be passed to subsequent exec() calls, no unique name is involved. I know that you can pass an empty string to PQPrepare(), but only one such statement can be used at a time, so it's not the same thing. Currently with pg, using prepared statements more or less implies implementing an application-wide policy about naming them, otherwise there is always the risk that some code upper in the stack has a live statement with the same name. And what about contributed code or libraries? That would be easier if this global namespace for prepared statements didn't exist in the first place. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
On Thu, Nov 20, 2008 at 19:19, Daniel Verite <daniel@manitou-mail.org> wrote: > By the way, why do the prepared statements require to be named at all? > With other DBMS such as oracle or mysql, one can prepare statements without > providing any name for them: the prepare() step returns a "statement handle" > that is to be passed to subsequent exec() calls, no unique name is involved. Isn't this basically the same thing? Except instead of having to take care yourself about the uniqueness aspect, it gets taken care of automatically by the language, since each handle becomes a separate variable. > Currently with pg, using prepared statements more or less implies > implementing an application-wide policy about naming them, otherwise there > is always the risk that some code upper in the stack has a live statement > with the same name. And what about contributed code or libraries? That would > be easier if this global namespace for prepared statements didn't exist in > the first place. Yeah, but if you wanted to reuse a statement you'd still have to implement a mechanism in the code. Like in my case, I'd still have to have a method of recognizing the same query, so I'd still resort to a hashtable with MD5's as keys, only instead of a boolean I'd put statement handles in there. So it would eliminate the possibility of clashes, but do nothing for statement reuse. What would make it all the way better was if the database would do that last step for you as well: automatically recognize statements that do the same thing and return the already existing handle. Only then I'd be truly worry-free as far as the code goes; I could prepare statements left and right knowing that both clashing and reuse are taken care of by Postgres.
On Fri, Nov 21, 2008 at 09:55:11AM +0200, WireSpot wrote: > What would make it all the way better was if the database would do > that last step for you as well: automatically recognize statements > that do the same thing and return the already existing handle. This is somewhat difficult; things to consider are different data distributions (i.e. the optimal plan changes depending on if you're searching for common vs. rare values) changes to schema (table foo and bar are now called the opposite) and various other issues. It would of course be possible for PG to do this, it's just getting it all correct and it being a win in the common case is difficult. Sam
WireSpot wrote: > So it would eliminate the possibility of clashes, but do nothing for > statement reuse. Agreed. > What would make it all the way better was if the database would do > that last step for you as well: automatically recognize statements > that do the same thing and return the already existing handle. Sure. What I understand from the thread is that you're trying to emulate in client code what would essentially be a server-side plan-caching-and-reuse feature. Since it's refered to in the TODO list (under the entry "Consider automatic caching of statements at various levels"), I guess this feature doesn't exist in current versions. Also contrary to prepared statements, maybe that cache would be shared between connections, and that would be excellent, since it fits the typical usage pattern of websites: a high-throughput of a small set of low-latency queries, fired from pooled connections. Not having the server reparsing and replanning over and over the same queries can lead to very significant wins in latency, and it doesn't have to involve any client-side specific code. What the client has to do however is to use parameterized queries, otherwise the cache gets polluted with non-reusable statements. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
Daniel Verite wrote: > Also contrary to prepared statements, maybe that cache would be shared > between connections, and that would be excellent, since it fits the > typical usage pattern of websites: a high-throughput of a small set of > low-latency queries, fired from pooled connections. In this case, why not just prepare all the needed statements at the first use of the session by the pool software? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera wrote: > In this case, why not just prepare all the needed statements at the > first use of the session by the pool software? In theory yes, but I can't imagine how it could be done in practice. The pool software is typically a middleware and the application isn't even aware of its existence. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
On 2008-11-20 12:56, WireSpot wrote:
> On Thu, Nov 20, 2008 at 10:56, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
>> Do you still need the old prepared statement?
>>
>> If not, you can simple DEALLOCATE it and then try the PREPARE again.
>
> Yes, I'd like to keep the old statements, that's part of the perks --
> if a query will be repeated it will (possibly) benefit from the
> statement being already prepared.
>
So:
sql_md5 = md5(sql);
try {
PREPARE sql_md5 AS sql;
} catch (SQLException e) {
if (! e.getSQLState().equals("42P05")) {
throw e;
}
}
EXECUTE sql_md5;
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
On Mon, Dec 8, 2008 at 09:17, Tomasz Ostrowski <tometzky@batory.org.pl> wrote:
> So:
>
> sql_md5 = md5(sql);
> try {
> PREPARE sql_md5 AS sql;
> } catch (SQLException e) {
> if (! e.getSQLState().equals("42P05")) {
> throw e;
> }
> }
> EXECUTE sql_md5;
Yeah, well, like I said, I have to write it in PHP and apparently
"old" or should I say "classic" extensions like Postgres don't trigger
specific but generic exceptions (ie. the catch block above, while
possible, is useless). There are even cases where PHP doesn't throw
catchable exceptions but an "older" kind; which can be handled
globally with a custom exception handler, but doing this for an entire
application just for the sake of one part of it is more trouble than
it's worth (in this case).
In case anybody is interested, here is the way I went. I created a
singleton class which contains a static hashtable. Every time a new
instance is requested it queries the pg_prepared_statements table and
adds values from the column "name" to the hashtable. This way the main
method can avoid clashes fairly well.
This mechanism is still not perfect. Technically it is still possible
for race conditions to appear. Apparently (in PHP at least) pg_connect
does persistent connections by default. If this is overlooked it is
possible for two web pages to use the same connection and one of them
to define a statement a short while before another, which would cause
a clash. Because while connections may be shared, the code-side
hashtable is not. Solution: either make sure connections are NOT
shared, or implement a way to properly share the hashtable across
pages. Or implement the whole thing in Postgres, transparently.
WireSpot wrote: > This mechanism is still not perfect. Technically it is still possible > for race conditions to appear. Apparently (in PHP at least) pg_connect > does persistent connections by default. Nope - pg_pconnect() does that. Multiple calls to pg_connect() within the same script will give the same connection though. -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > WireSpot wrote: >> This mechanism is still not perfect. Technically it is still possible >> for race conditions to appear. Apparently (in PHP at least) pg_connect >> does persistent connections by default. > > Nope - pg_pconnect() does that. Multiple calls to pg_connect() within > the same script will give the same connection though. IFF you supply exactly the same details (db/host/port, user, pass) - if any are different you'll get a new connection. -- Postgresql & php tutorials http://www.designmagick.com/
On Tue, Dec 9, 2008 at 8:59 PM, Chris <dmagick@gmail.com> wrote: > Richard Huxton wrote: >> >> WireSpot wrote: >>> >>> This mechanism is still not perfect. Technically it is still possible >>> for race conditions to appear. Apparently (in PHP at least) pg_connect >>> does persistent connections by default. >> >> Nope - pg_pconnect() does that. Multiple calls to pg_connect() within >> the same script will give the same connection though. > > IFF you supply exactly the same details (db/host/port, user, pass) - if any > are different you'll get a new connection. There's a bool arg you can throw at the connection to stop that. From the pg_connect page: If a second call is made to pg_connect() with the same connection_string as an existing connection, the existing connection will be returned unless you pass PGSQL_CONNECT_FORCE_NEW as connect_type . It's not like the behaviour is hidden or something.