Re: statement caching proof of concept

Dave Cramer wrote:
>
> On 19-Jun-06, at 7:15 PM, Oliver Jowett wrote:
>>
>> The "wrapper" implementation approach suffers from the usual
>> difficulty that the "back links" such as ResultSet.getStatement()
>> point to the wrong object. It's actually quite a bit of work to get
>> this right..
> Since result sets only live as long as the statement, wouldn't they
> point to the statement that is still open ?

What I mean is that this won't work:

PreparedStatement ps = conn.prepareStatement(...);
ResultSet rs = ps.executeQuery();
assert rs.getStatement() == ps;

since rs.getStatement() will return the real underlying statement, not
the wrapper that prepareStatement() creates.

>> The cached statements are vulnerable to buggy apps that mutate the
>> statement after close, since there's no interception of those methods
>> to check whether the wrapper statement has been closed.
>
> No question, and I would certainly not make this the default behaviour.
> The user would have to turn on caching.

Isn't the right solution to intercept methods and complain if the
wrapper is closed? The wrapper statement would never get re-opened like
the underlying statement does. Then the cached version behaves just like
the non-cached version.

>> What exactly is the performance bottleneck you're trying to avoid by
>> having the statement pool? If it's the parse/plan cost, I think Mark's
>> suggestion of putting the cache at the protocol level may be simpler.
>> If it's overall statement cost, you might be better off with a generic
>> wrapper that is not postgresql-specific at all.
>
> How does the generic wrapper solve the problems above ? I would think
> they all suffer from the same problems ?

Well, yes, but my point is that you can solve this once for all JDBC
drivers, you don't need postgres-specific code to do it .. and surely
someone has already done this?

-O