Re: CVS JDBC driver will try to use server-side-prepare on - Mailing list pgsql-jdbc

Felipe,

Your custom connection pool would provide implementations for
Statement.close() that doesn't really close the statement, but puts it
back into some hashtable/pool where the key to the Statement object is
the text of the sql.  Then prepareStatement(sql) would first look for an
existing Statement object in the hash for that sql and reuse it, else
create a new one if it didn't find it.

--Barry

Felipe Schnack wrote:
>   Hm, and what my connection pool woud do to re-use them?
>
> On Fri, 15 Aug 2003 11:08:59 -0700
> Barry Lind <blind@xythos.com> wrote:
>
>
>>Felipe,
>>
>>You need to write your own connection pool to do this.  That is why I am
>>saying that it takes a lot of coding to get this to all work efficiently.
>>
>>--Barry
>>
>>Felipe Schnack wrote:
>>
>>>  I can't understand. How can I "cache" my Statements? As far as I know after I return my connection to tomcat's
connectionpooling my statement becomes pratically unusable, isn't it? 
>>>
>>>On Fri, 15 Aug 2003 09:55:02 -0700
>>>Barry Lind <blind@xythos.com> wrote:
>>>
>>>
>>>
>>>>Oliver,
>>>>
>>>>See my comments below.
>>>>
>>>>Oliver Jowett wrote:
>>>>
>>>>
>>>>>It looks like the driver is trying to use server-side prepare on SQL that it
>>>>>won't work on:
>>>>>
>>>>>Aug 16 00:05:40 flood postgres[12989]: [12-1] LOG:  query: PREPARE JDBC_STATEMENT_4 AS CREATE TABLE
"testBigDB/persisted_testBigDB/persisted_one"( pk         BYTEA   NOT 
>>>>>Aug 16 00:05:40 flood postgres[12989]: [12-2]  NULL,  generation INT8    NOT NULL,  data       BYTEA   NOT NULL,
CONSTRAINT
>>>>>Aug 16 00:05:40 flood postgres[12989]: [12-3]  "pkey_testBigDB/persisted_testBigDB/persisted_one" PRIMARY KEY
(pk));EXECUTE JDBC_STATEMENT_4 
>>>>>Aug 16 00:05:40 flood postgres[12989]: [13] ERROR:  parser: parse error at or near "CREATE" at character 29
>>>>>
>>>>>This then turns up as a SQLException on the java side.
>>>>>
>>>>>Yes, I know, "don't do that then!", but isn't the plan to default to
>>>>>server-side prepare eventually?
>>>>
>>>>Yes and no.  The plan is to convert fully over to the new V3 protocol
>>>>which will better handle cases like this and a lot of other things.  So
>>>>yes the plan is to move fully to server side prepared statements, but
>>>>via a different mechanism.  And conversly the plan isn't to move the
>>>>current mechanism forward as it has many limitations (as you are finding
>>>>out).  One of the big reasons for the new functionality in the V3
>>>>protocol is to provide better support for these type of opperations
>>>>efficiently.
>>>>
>>>>However a workaround for this specific problem would be to only use
>>>>server side prepared statements in the current implementation for
>>>>executeQuery calls, not for executeUpdate or for plain execute.
>>>>
>>>>
>>>>
>>>>>Should we only be doing PREPARE on queries that are known to be safe (e.g.
>>>>>single-statement SELECTs), or is it better to try to catch the errors and
>>>>>abandon the prepare? (more general, but sounds a bit hairy).
>>>>>
>>>>>The reason that this came up is I'm modifying the driver to allow
>>>>>server-side prepare to be toggled at the connection- and datasource- level.
>>>>>Patches for that to follow once I've sorted this problem out.
>>>>>
>>>>
>>>>I would rather see you invest your time in implementing the V3 protocol
>>>>to do this correctly.  I am reluctant to commit patches along the lines
>>>>of what you are describing (check the archives for previous discussions
>>>>on this).  But in short the reason is, that in general using the current
>>>>prepared implementation will be *slower* than not using it, unless you
>>>>are reusing the statement a number of times.  Therefore unless you have
>>>>some sort of complex application layer that is caching Statement objects
>>>>and reusing them, this feature will nagatively impact performance, and
>>>>IMHO will lead to problems because people will assume that something
>>>>like this should be used and complain when it makes things slower.
>>>>Since in order to be useful you need application logic to cache and
>>>>reuse the Statement objects, it isn't that difficult to have that logic
>>>>also turn on server side prepare using the current methods.
>>>>
>>>>Finally, if you do want to pursue your current course, I would like to
>>>>see some sort of benchmarks that show these changes actually on average
>>>>help.
>>>>
>>>>thanks,
>>>>--Barry
>>>>
>>>>
>>>>
>>>>
>>>>>-O
>>>>>
>>>>>---------------------------(end of broadcast)---------------------------
>>>>>TIP 6: Have you searched our list archives?
>>>>>
>>>>>              http://archives.postgresql.org
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>>---------------------------(end of broadcast)---------------------------
>>>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>>
>>>
>>>
>>>
>>> /~\ The ASCII        Felipe Schnack (felipes@ritterdosreis.br)
>>> \ / Ribbon Campaign  Analista de Sistemas
>>>  X  Against HTML     Cel.: 51-91287530
>>> / \ Email!           Linux Counter #281893
>>>
>>>Centro Universitário Ritter dos Reis
>>>http://www.ritterdosreis.br
>>>ritter@ritterdosreis.br
>>>Fone: 51-32303341
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 6: Have you searched our list archives?
>>>
>>>               http://archives.postgresql.org
>>>
>>
>>
>
>
>
>  /~\ The ASCII        Felipe Schnack (felipes@ritterdosreis.br)
>  \ / Ribbon Campaign  Analista de Sistemas
>   X  Against HTML     Cel.: 51-91287530
>  / \ Email!           Linux Counter #281893
>
> Centro Universitário Ritter dos Reis
> http://www.ritterdosreis.br
> ritter@ritterdosreis.br
> Fone: 51-32303341
>