Обсуждение: postgresql-8.1-413.jdbc3.jar ignoring setPrepareThreshold();
I have slight problem on a postgresql-server-8.1.11-1PGDG.rhel4 db. The first statement below runs fine. But the second two statements return no results. When these statement are not run as prepared statements they work fine. I need to run them as a prepared statement because of the parameterization, but I don't want the server to cache the prepared nature of the statements. These statements worked fine on an old 7.3 driver. The only differendce I could see was that the driver didn't prepare them. Dec 18 10:10:56 devel postgres[5404]: [2-1] LOG: duration: 362.004 ms statement: EXECUTE <unnamed> [PREPARE: selectaggregationvalue.value as Dec 18 10:10:56 devel postgres[5404]: [2-2] aggregationvalue$value,aggregationvalue.aggregatetype as aggregationvalue$aggregatetype,aggregationvalue.countas Dec 18 10:10:56 devel postgres[5404]: [2-3] aggregationvalue$count,aggregationvalue.stoptime as aggregationvalue$stoptime,aggregationvalue.starttimeas Dec 18 10:10:56 devel postgres[5404]: [2-4] aggregationvalue$starttime from aggregationvalue where date_trunc('month',aggregationvalue.stoptime) between $1 and $2 and Dec 18 10:10:56 devel postgres[5404]: [2-5] aggregationvalue.aggregatetype = $3 and split_part(aggregationvalue.value,':',1)= $4 and Dec 18 10:10:56 devel postgres[5404]: [2-6] split_part(aggregationvalue.value,':',2) like $5 and split_part(aggregationvalue.value,':',3)like $6 ] Dec 18 10:11:05 devel postgres[5404]: [4-1] LOG: duration: 31.000 ms statement: EXECUTE <unnamed> [PREPARE: selectaggregationvalue.value as Dec 18 10:11:05 devel postgres[5404]: [4-2] aggregationvalue$value,aggregationvalue.aggregatetype as aggregationvalue$aggregatetype,aggregationvalue.countas Dec 18 10:11:05 devel postgres[5404]: [4-3] aggregationvalue$count,aggregationvalue.stoptime as aggregationvalue$stoptime,aggregationvalue.starttimeas Dec 18 10:11:05 devel postgres[5404]: [4-4] aggregationvalue$starttime from aggregationvalue where date_trunc('month',aggregationvalue.stoptime) between $1 and $2 and Dec 18 10:11:05 devel postgres[5404]: [4-5] aggregationvalue.aggregatetype = $3 and split_part(aggregationvalue.value,':',1)= $4 and Dec 18 10:11:05 devel postgres[5404]: [4-6] split_part(aggregationvalue.value,':',2) like $5 and split_part(aggregationvalue.value,':',3)like $6 ] Dec 18 10:11:05 devel postgres[5404]: [6-1] LOG: duration: 2.000 ms statement: EXECUTE <unnamed> [PREPARE: select aggregationvalue.valueas Dec 18 10:11:05 devel postgres[5404]: [6-2] aggregationvalue$value,aggregationvalue.aggregatetype as aggregationvalue$aggregatetype,aggregationvalue.countas Dec 18 10:11:05 devel postgres[5404]: [6-3] aggregationvalue$count,aggregationvalue.stoptime as aggregationvalue$stoptime,aggregationvalue.starttimeas Dec 18 10:11:05 devel postgres[5404]: [6-4] aggregationvalue$starttime from aggregationvalue where date_trunc('day', aggregationvalue.stoptime)between $1 and $2 and Dec 18 10:11:05 devel postgres[5404]: [6-5] aggregationvalue.aggregatetype = $3 and split_part(aggregationvalue.value,':',1)= $4 and Dec 18 10:11:05 devel postgres[5404]: [6-6] split_part(aggregationvalue.value,':',2) like $5 and split_part(aggregationvalue.value,':',3)like $6 ] The sum of the Universe is zero.
Jeremiah Jahn wrote: > I have slight problem on a postgresql-server-8.1.11-1PGDG.rhel4 db. > The first statement below runs fine. But the second two statements > return no results. When these statement are not run as prepared > statements they work fine. I need to run them as a prepared statement > because of the parameterization, but I don't want the server to cache > the prepared nature of the statements. These statements worked fine on > an old 7.3 driver. The only differendce I could see was that the driver > didn't prepare them. It's not at all clear what you are trying to do here or what exactly the problem you see is. For example, from the server logs, the driver is not using named statements for *any* of those queries, which seems to contradict what you're describing. And the queries are identical. And you haven't given any parameter values or any of your code to look at. So it's impossible to diagnose anything from that. Do you have a selfcontained testcase that shows the problem? -O
On Thu, Dec 18, 2008 at 6:56 PM, Oliver Jowett <oliver@opencloud.com> wrote:
Jeremiah Jahn wrote:It's not at all clear what you are trying to do here or what exactly the problem you see is.I have slight problem on a postgresql-server-8.1.11-1PGDG.rhel4 db.
The first statement below runs fine. But the second two statements
return no results. When these statement are not run as prepared
statements they work fine. I need to run them as a prepared statement
because of the parameterization, but I don't want the server to cache
the prepared nature of the statements. These statements worked fine on
an old 7.3 driver. The only differendce I could see was that the driver
didn't prepare them.
For example, from the server logs, the driver is not using named statements for *any* of those queries, which seems to contradict what you're describing. And the queries are identical. And you haven't given any parameter values or any of your code to look at. So it's impossible to diagnose anything from that.
If I understand you, you don't want the driver to prepare them at all, but you want to use JDBC prepared statements.
The driver is using unnamed statements here, the prepareThreshold determines when it will start using a named statement. You can get around this behaviour by using protocolVersion=2 as a connection parameter.
Dave
The driver is using unnamed statements here, the prepareThreshold determines when it will start using a named statement. You can get around this behaviour by using protocolVersion=2 as a connection parameter.
Dave