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

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