Thread: 8.1.11 PREPARE problem?
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: selectaggregationvalue.value as Dec 18 10:59:15 devel postgres[6889]: [2-2] aggregationvalue$value,aggregationvalue.aggregatetype as aggregationvalue$aggregatetype,aggregationvalue.countas Dec 18 10:59:15 devel postgres[6889]: [2-3] aggregationvalue$count,aggregationvalue.stoptime as aggregationvalue$stoptime,aggregationvalue.starttimeas 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: selectaggregationvalue.value as Dec 18 10:59:25 devel postgres[6889]: [4-2] aggregationvalue$value,aggregationvalue.aggregatetype as aggregationvalue$aggregatetype,aggregationvalue.countas Dec 18 10:59:25 devel postgres[6889]: [4-3] aggregationvalue$count,aggregationvalue.stoptime as aggregationvalue$stoptime,aggregationvalue.starttimeas 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.valueas Dec 18 10:59:25 devel postgres[6889]: [6-2] aggregationvalue$value,aggregationvalue.aggregatetype as aggregationvalue$aggregatetype,aggregationvalue.countas Dec 18 10:59:25 devel postgres[6889]: [6-3] aggregationvalue$count,aggregationvalue.stoptime as aggregationvalue$stoptime,aggregationvalue.starttimeas 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 ]
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:
selectaggregationvalue.value as
> Dec 18 10:59:15 devel postgres[6889]: [2-2] aggregationvalue$value,aggregationvalue.aggregatetype as
aggregationvalue$aggregatetype,aggregationvalue.countas
> Dec 18 10:59:15 devel postgres[6889]: [2-3] aggregationvalue$count,aggregationvalue.stoptime as
aggregationvalue$stoptime,aggregationvalue.starttimeas
> 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:
selectaggregationvalue.value as
> Dec 18 10:59:25 devel postgres[6889]: [4-2] aggregationvalue$value,aggregationvalue.aggregatetype as
aggregationvalue$aggregatetype,aggregationvalue.countas
> Dec 18 10:59:25 devel postgres[6889]: [4-3] aggregationvalue$count,aggregationvalue.stoptime as
aggregationvalue$stoptime,aggregationvalue.starttimeas
> 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:
selectaggregationvalue.value as
> Dec 18 10:59:25 devel postgres[6889]: [6-2] aggregationvalue$value,aggregationvalue.aggregatetype as
aggregationvalue$aggregatetype,aggregationvalue.countas
> Dec 18 10:59:25 devel postgres[6889]: [6-3] aggregationvalue$count,aggregationvalue.stoptime as
aggregationvalue$stoptime,aggregationvalue.starttimeas
> 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 ]
>
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.
On Thu, 2008-12-18 at 14:52 -0500, Tom Lane wrote:
> 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
>
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:
selectaggregationvalue.value as
> > Dec 18 10:59:15 devel postgres[6889]: [2-2] aggregationvalue$value,aggregationvalue.aggregatetype as
aggregationvalue$aggregatetype,aggregationvalue.countas
> > Dec 18 10:59:15 devel postgres[6889]: [2-3] aggregationvalue$count,aggregationvalue.stoptime as
aggregationvalue$stoptime,aggregationvalue.starttimeas
> > 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:
selectaggregationvalue.value as
> > Dec 18 10:59:25 devel postgres[6889]: [4-2] aggregationvalue$value,aggregationvalue.aggregatetype as
aggregationvalue$aggregatetype,aggregationvalue.countas
> > Dec 18 10:59:25 devel postgres[6889]: [4-3] aggregationvalue$count,aggregationvalue.stoptime as
aggregationvalue$stoptime,aggregationvalue.starttimeas
> > 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:
selectaggregationvalue.value as
> > Dec 18 10:59:25 devel postgres[6889]: [6-2] aggregationvalue$value,aggregationvalue.aggregatetype as
aggregationvalue$aggregatetype,aggregationvalue.countas
> > Dec 18 10:59:25 devel postgres[6889]: [6-3] aggregationvalue$count,aggregationvalue.stoptime as
aggregationvalue$stoptime,aggregationvalue.starttimeas
> > 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