Thread: JDBC question for PG 8.3.9

Hi foilks

I am using PG 8.3 from Java. I am considering a performance tweak which will involve holding about 150 java.sql.PreparedStatment objects open against a single PGSQL connection. Is this safe?

I know that MySQL does not support prepared statements per se, and so their implementation of PreparedStatement is nothing more than some client-side convenience code that knows how to escape and format constants for you. Is this the case for PG, or does the PG JDBC driver do the real thing? I'm assuming if it's just a client side constant escaper that there won't be an issue.

Cheers
Dave

On 15/04/10 04:49, Dave Crooke wrote:
> Hi foilks
>
> I am using PG 8.3 from Java. I am considering a performance tweak which
> will involve holding about 150 java.sql.PreparedStatment objects open
> against a single PGSQL connection. Is this safe?
>
> I know that MySQL does not support prepared statements /per se/, and so
> their implementation of PreparedStatement is nothing more than some
> client-side convenience code that knows how to escape and format
> constants for you. Is this the case for PG, or does the PG JDBC driver
> do the real thing?

Pg supports real server-side prepared statements, as does the JDBC driver.

IIRC (and I can't say this with 100% certainty without checking the
sources or a good look at TFM) the PostgreSQL JDBC driver initially does
only a client-side prepare. However, if the PreparedStatement is re-used
more than a certain number of times (five by default?) it switches to
server-side prepared statements.

This has actually caused a bunch of performance complaints on the jdbc
list, because the query plan may change at that switch-over point, since
with a server-side prepared statement Pg no longer has a specific value
for each parameter and may pick a more generic plan.

Again only IIRC there's a configurable threshold for prepared statement
switch-over. I thought all this was in the PgJDBC documentation and/or
javadoc - if it's not, it needs to be.

--
Craig Ringer

Mine is a single record INSERT, so no issues with plans :-) Little Java ETL job.

Is there any setting I'd need to tweak assuming I'm using 150-200 of these at once?

Cheers
Dave

On Wed, Apr 14, 2010 at 7:10 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 15/04/10 04:49, Dave Crooke wrote:
>>
>> Hi foilks
>>
>> I am using PG 8.3 from Java. I am considering a performance tweak which
>> will involve holding about 150 java.sql.PreparedStatment objects open
>> against a single PGSQL connection. Is this safe?
>>
>> I know that MySQL does not support prepared statements /per se/, and so
>> their implementation of PreparedStatement is nothing more than some
>> client-side convenience code that knows how to escape and format
>> constants for you. Is this the case for PG, or does the PG JDBC driver
>> do the real thing?
>
> Pg supports real server-side prepared statements, as does the JDBC driver.
>
> IIRC (and I can't say this with 100% certainty without checking the sources
> or a good look at TFM) the PostgreSQL JDBC driver initially does only a
> client-side prepare. However, if the PreparedStatement is re-used more than
> a certain number of times (five by default?) it switches to server-side
> prepared statements.
>
This is partially true. The driver uses an unnamed prepared statement
on the server.

> This has actually caused a bunch of performance complaints on the jdbc list,
> because the query plan may change at that switch-over point, since with a
> server-side prepared statement Pg no longer has a specific value for each
> parameter and may pick a more generic plan.

This is a limitation of the server, not the driver