Re: CVS JDBC driver will try to use server-side-prepare on

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 connection pooling my statement becomes pratically unusable, isn't it?
>
> On Fri, 15 Aug 2003 09:55:02 -0700
> Barry Lind <blind(at)xythos(dot)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(at)postgresql(dot)org
>
>
>
>
> /~\ The ASCII Felipe Schnack (felipes(at)ritterdosreis(dot)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(at)ritterdosreis(dot)br
> Fone: 51-32303341
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>