8.1.11 PREPARE problem?

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 '%' ;

doh! my second prepared statement is getting prepared as all text, when
the second and third parameters should be timestamps.

argh! Is there some sort of logging, that says how prepared statements
are getting prepared?

On Thu, 2008-12-18 at 13:13 -0600, Jeremiah Jahn wrote:

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 ]

printk("%s: confused, missing data\n", drive->name);
linux-2.6.6/drivers/ide/ide-cd.c

Jeremiah Jahn <jeremiah.jahn@gmail.com> writes:

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.

It seems quite unlikely that there's a generic bug in prepared
statements that no one has encountered till now. If you've got a real
bug here I'm sure it's a lot narrower case than that. Can you put
together a self-contained test case? Also, can you reproduce the
problem on something newer than 8.1.11?

regards, tom lane

thanks for the help.

between CAST(? as timestamp) and CAST(? as timestamp) fixes my problem.

because my second query uses a between the same day parameter it wasn't
being found when it was treated as a string, as a timestamp it works
fine. Apparently a simple query will convert the string to a timestamp
correctly, while they way I was doing things in java was forcing it to
be a string, thus killing my query.

I'm using a rather dense index:
CREATE INDEX stat_speed_big ON aggregationvalue (aggregatetype, value,
date_trunc('hour', stoptime), date_trunc('day', stoptime),
date_trunc('month', stoptime), date_trunc('year', stoptime),
split_part(aggregationvalue.value,':',1),
split_part(aggregationvalue.value,':',2),
split_part(aggregationvalue.value,':',3),
split_part(aggregationvalue.value,':',4),
split_part(aggregationvalue.value,':',5));

and messing with my dynamic SQL enough to make sure it hits the index
every time, that I got headed down the wrong track when it didn't work
consistently between applications, different driver versions, and did
work correctly on the old version, which doesn't server prepare
statements.

thanks again, if only for the moral support.