Re: Prepared statement already exists

On Mon, Dec 8, 2008 at 09:17, Tomasz Ostrowski <tometzky(at)batory(dot)org(dot)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.