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

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));
EXECUTEJDBC_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
>