Re: 8.1.11 PREPARE problem?

From: Jeremiah Jahn <jeremiah(dot)jahn(at)gmail(dot)com>
To: postgres list <pgsql-general(at)postgresql(dot)org>
Subject: Re: 8.1.11 PREPARE problem?
Date: 2008-12-18 19:13:31
Message-ID: 1229627612.1933.40.camel@bluejay.goodinassociates.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

moving on:
After digging through the JDBC source. It seems that the server is
returning a 'C': // Command Status (end of Execute) before returning
any data rows. So it doesn't look like JDBC is causing any problems.

I've tried not running the first, and only running the second, and I get
still no results. But as usual when running by hand, I do get results.

the SQL that i'm running ends up looking like these when run by hand:

1) works as a prepared statement
select aggregationvalue.value as aggregationvalue
$value,aggregationvalue.aggregatetype as aggregationvalue
$aggregatetype,aggregationvalue.count as aggregationvalue
$count,aggregationvalue.stoptime as aggregationvalue
$stoptime,aggregationvalue.starttime as aggregationvalue$starttime from
aggregationvalue where date_trunc('month', aggregationvalue.stoptime)
between '2007-11-01' and '2008-12-01' and
aggregationvalue.aggregatetype = 'MONTHLY' and
split_part(aggregationvalue.value,':',1) = 'JUDICI_PAGE' and
split_part(aggregationvalue.value,':',2) like '%' and
split_part(aggregationvalue.value,':',3) like '%' ;

2) does not work as prepared statement
select aggregationvalue.value as aggregationvalue
$value,aggregationvalue.aggregatetype as aggregationvalue
$aggregatetype,aggregationvalue.count as aggregationvalue
$count,aggregationvalue.stoptime as aggregationvalue
$stoptime,aggregationvalue.starttime as aggregationvalue$starttime from
aggregationvalue where date_trunc('day', aggregationvalue.stoptime)
between '2008-12-18' and '2008-12-18' and
aggregationvalue.aggregatetype = 'HOURLY' and
split_part(aggregationvalue.value,':',1) = 'JUDICI_PAGE' and
split_part(aggregationvalue.value,':',2) like '%' and
split_part(aggregationvalue.value,':',3) like '%' ;

On Thu, 2008-12-18 at 11:21 -0600, Jeremiah Jahn wrote:
> This is with jdbc3-415. None of the (jdbc, or pg) change logs since then
> have mentioned this problem. When run as a prepared statement the first
> statement will execute and return results, while the next two seem to
> execute, but return no results. When run by hand, not prepared , each
> statement runs just fine. when using an older version of the jdbc driver
> (7.3?), that does not call PREPARE, things work just fine. Looking at
> the logs below, it sure looks like the statements are running, given the
> different durations, which are correct for the differences between the
> statements. help please.
>
>
>
>
> Dec 18 10:59:15 devel postgres[6889]: [2-1] LOG: duration: 358.005 ms statement: EXECUTE <unnamed> [PREPARE: select aggregationvalue.value as
> Dec 18 10:59:15 devel postgres[6889]: [2-2] aggregationvalue$value,aggregationvalue.aggregatetype as aggregationvalue$aggregatetype,aggregationvalue.count as
> Dec 18 10:59:15 devel postgres[6889]: [2-3] aggregationvalue$count,aggregationvalue.stoptime as aggregationvalue$stoptime,aggregationvalue.starttime as
> Dec 18 10:59:15 devel postgres[6889]: [2-4] aggregationvalue$starttime from aggregationvalue where date_trunc($1, aggregationvalue.stoptime) between $2 and $3 and
> Dec 18 10:59:15 devel postgres[6889]: [2-5] aggregationvalue.aggregatetype = $4 and split_part(aggregationvalue.value,':',1) = $5 and
> Dec 18 10:59:15 devel postgres[6889]: [2-6] split_part(aggregationvalue.value,':',2) like $6 and split_part(aggregationvalue.value,':',3) like $7 ]
>
> Dec 18 10:59:25 devel postgres[6889]: [4-1] LOG: duration: 25.001 ms statement: EXECUTE <unnamed> [PREPARE: select aggregationvalue.value as
> Dec 18 10:59:25 devel postgres[6889]: [4-2] aggregationvalue$value,aggregationvalue.aggregatetype as aggregationvalue$aggregatetype,aggregationvalue.count as
> Dec 18 10:59:25 devel postgres[6889]: [4-3] aggregationvalue$count,aggregationvalue.stoptime as aggregationvalue$stoptime,aggregationvalue.starttime as
> Dec 18 10:59:25 devel postgres[6889]: [4-4] aggregationvalue$starttime from aggregationvalue where date_trunc($1, aggregationvalue.stoptime) between $2 and $3 and
> Dec 18 10:59:25 devel postgres[6889]: [4-5] aggregationvalue.aggregatetype = $4 and split_part(aggregationvalue.value,':',1) = $5 and
> Dec 18 10:59:25 devel postgres[6889]: [4-6] split_part(aggregationvalue.value,':',2) like $6 and split_part(aggregationvalue.value,':',3) like $7 ]
>
> Dec 18 10:59:25 devel postgres[6889]: [6-1] LOG: duration: 2.000 ms statement: EXECUTE <unnamed> [PREPARE: select aggregationvalue.value as
> Dec 18 10:59:25 devel postgres[6889]: [6-2] aggregationvalue$value,aggregationvalue.aggregatetype as aggregationvalue$aggregatetype,aggregationvalue.count as
> Dec 18 10:59:25 devel postgres[6889]: [6-3] aggregationvalue$count,aggregationvalue.stoptime as aggregationvalue$stoptime,aggregationvalue.starttime as
> Dec 18 10:59:25 devel postgres[6889]: [6-4] aggregationvalue$starttime from aggregationvalue where date_trunc($1, aggregationvalue.stoptime) between $2 and $3 and
> Dec 18 10:59:25 devel postgres[6889]: [6-5] aggregationvalue.aggregatetype = $4 and split_part(aggregationvalue.value,':',1) = $5 and
> Dec 18 10:59:25 devel postgres[6889]: [6-6] split_part(aggregationvalue.value,':',2) like $6 and split_part(aggregationvalue.value,':',3) like $7 ]
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message novnov 2008-12-18 19:24:20 Re: Error: Operator does not exist: "char"=integer
Previous Message George Weaver 2008-12-18 18:50:39 Re: Server side large object functions