Message-ID: <42D47407.10301@jobflash.com>
Date: Tue, 12 Jul 2005 18:53:11 -0700
From: Tom Arthurs <tarthurs@jobflash.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: Brent Henry <bh_pgperf@yahoo.com>
Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
	Dennis <dennis@works4me.com>, pgsql-performance@postgresql.org
Subject: Re: General DB Tuning
References: <20050713013630.18625.qmail@web33911.mail.mud.yahoo.com>
In-Reply-To: <20050713013630.18625.qmail@web33911.mail.mud.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Here's the answer for you from the jdbc list:

> Alvin Hung wrote:
> 
> 
>>> Currently, 8.0.2 / JDBC 8.0-310, log_min_duration_statement does not
>>> work with JDBC.  Nothing will get logged.  This makes it very
>>> difficult to tune a java application.  Can you tell me when will this
>>> be fixed?  Thanks.
> 
> 
> This is a server limitation: it does not handle logging of the V3
> extended query protocol very well. There's gradual progress being made
> on it; you might want to search the pgsql-hackers and pgsql-patches
> archives for details.
==========================================================================================


We are using prepared statements, but we are using the 7.4 driver with 
the 8.0.3 server.  I think it comes down to locally (on the client) 
prepared statements vs using server side prepared statments.  I never 
got past this issue (changing the code is in our todo list, but pretty 
far down it) so I never noticed the logging issues.)

I had a problem with prepared statements with the 8.x drivers -- here's 
what I got from the jdbc list when I asked the question:

>>1.  What changed between the driver versions that generate this error?
> 
> 
> The driver started to use server-side prepared statements for
> parameterization of queries (i.e. the driver translates ? to $n in the
> main query string, and sends the actual parameter values out-of-band
> from the query itself). One sideeffect of this is that parameters are
> more strongly typed than in the 7.4.x versions where the driver would do
> literal parameter substitution into the query string before sending it
> to the backend. Also, you can use parameters in fewer places (they must
> fit the backend's idea of where parameterizable expressions are allowed)
> -- e.g. see the recent thread about "ORDER BY ?" changing behaviour with
> the newer driver.
> 
> 
>>> 2.  What is the downside of continuing to use the 7.x version of the
>>> driver -- or are there better alternatives (patch, new version, etc).  I
>>> am using build 311 of the driver.
> 
> 
> Most active development happens on the 8.0 version; 7.4.x is maintained
> for bugfixes but that's about it, you won't get the benefit of any
> performance improvements or added features that go into 8.0. Also, the
> 7.4.x driver won't necessarily work with servers >= 8.0.
> 
> In the longer term, the 7.4.x version will eventually become unmaintained.

So for the short term, you could downgrade your driver.


Brent Henry wrote:
> We are running Postgres 8.0.2 with the 8.0.2 jdbc
> driver.  And yes we are using prepared statements. 
> I've spent hours trying to get the
> 'log_min_duration_statement' and 'log_duration'
> options to work with no luck.  I never get any
> duration from the statement.  I also never see 'begin'
> or 'commit' in the log so I can't tell how long my
> batch commands are taking to commit to the DB.
> 
> Is there a different kind of 'prepared' statements
> that we should be using in the driver to get logging
> to work properly?  What is the 'new' protocol?
> 
> Tom, what version are you using?  Are you using
> prepared statements in JDBC?
> 
> -Brent
> 
> 
> --- Christopher Kings-Lynne
> <chriskl@familyhealth.com.au> wrote:
> 
> 
>>>>we are using jdbc -- the
>>
>>"log_min_duration_statement = 3000 " 
>>
>>>>statement works fine for me.  Looks like there's
>>
>>no other work around 
>>
>>>>for the bug(?).  Not sure since I have no
>>
>>interest in logging a 
>>
>>>>million statements a day, I only want to see the
>>
>>poorly performing hits. 
>>
>>>Doesn't it depend on what jdbc driver you are
>>
>>using?
>>
>>It depends if he's using new-protocol prepared
>>queries which don't get 
>>logged properly.  Wasn't that fixed for 8.1 or
>>something?
>>
>>Chris
>>
>>
>>---------------------------(end of
>>broadcast)---------------------------
>>TIP 3: Have you checked our extensive FAQ?
>>
>>               http://www.postgresql.org/docs/faq
>>
> 
> 
> 
> 
> 		
> ____________________________________________________
> Start your day with Yahoo! - make it your home page
> http://www.yahoo.com/r/hs
>  
> 
> 
>