Thread: Perl::DBI and interval syntax
Hi again!
I have a perl program running a query on FreeBSD under PostgreSQL 7.4.8
that has worked for some time. Converting the program to Linux under
PostgreSQL 8.0.3 returns a database error. The interval clause is the
issue.
SELECT count(*) from post where post_ts >= current_date - interval ?
execute argument: '21 days'
DBD::Pg::st execute failed: ERROR: syntax error at or near "$1" at
character 74
The value '21 days' is achieved computationally so shouldn't be
hard-coded in the query.
How should this be specified?
Allen
Sample code follows:
#!/usr/bin/perl -w
use strict;
use DBI;
use Data::Dumper;
my $dbName='allen';
my $host='localhost';
my $dbUser=$dbName;
my $dbPassword='';
my $csrnum=0;
my $sql="SELECT count(*) from post where post_ts >= current_date -
interval ?";
my @parms=('21 days');
my $dbh = DBI->connect("DBI:Pg:dbname=$dbName;host=$host", i
$dbUser, $dbPassword,
{ RaiseError => 0, AutoCommit => 0, PrintError => 1 })
or die "Can't connect to db\n";
my $sth = $dbh->prepare($sql);
$sth->execute(@parms) or die "execute err: $DBI::errstr";
while (my $hr = $sth->fetchrow_hashref) {
print Dumper($hr);
}
$sth->finish();
$dbh->commit();
$dbh->disconnect();
exit;
Allen <dba@girders.org> writes:
> SELECT count(*) from post where post_ts >= current_date - interval ?
This is not right, and never has been right, even though it may have
accidentally failed to fail with some client libraries. Try
CAST(? as interval) (SQL standard)
?::interval (Postgres-ism)
The "interval something" syntax is only legal when "something" is
a bare string literal.
regards, tom lane
On Mon, Nov 07, 2005 at 05:58:04PM -0500, Tom Lane wrote: > Allen <dba@girders.org> writes: > > SELECT count(*) from post where post_ts >= current_date - interval ? > > This is not right, and never has been right, even though it may have > accidentally failed to fail with some client libraries. Try > CAST(? as interval) (SQL standard) > ?::interval (Postgres-ism) > > The "interval something" syntax is only legal when "something" is > a bare string literal. This seems to be an example of breakage caused by DBI switching from "substitute params in client" to "use new protocol to substitute params in server" (prepare/execute). AIUI, if you disable use of the new protocol, it should work as before. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Martijn van Oosterhout wrote: > On Mon, Nov 07, 2005 at 05:58:04PM -0500, Tom Lane wrote: >> Allen <dba@girders.org> writes: >> > SELECT count(*) from post where post_ts >= current_date - interval ? >> This is not right, and never has been right, even though it may have >> accidentally failed to fail with some client libraries. Try >> CAST(? as interval) (SQL standard) >> ?::interval (Postgres-ism) [...] > This seems to be an example of breakage caused by DBI switching from > "substitute params in client" to "use new protocol to substitute params > in server" (prepare/execute). [...] Can this be the cause of a huge loss of perf? I have the following query in a Perl script using DBI + DBD::Pg, AutoCommit => 0: SELECT stats_put_sources(?, ?, int4(?), int4(?)) This syntax runs almost 10x faster than: SELECT stats_put_sources(?, ?, ?::int4, ?::int4) I can find where is the real difference, maybe this protocol stuff. In the facts the speed difference was so great that there were no need to use EXPLAIN to know what was the best option. N.B.: The stats_put_source(varchar,varchar,int4,int4) function is a rewrite of the INSERT OR UPDATE function described in the docs. Thanks, -- MaXX
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> This seems to be an example of breakage caused by DBI switching from
> "substitute params in client" to "use new protocol to substitute params
> in server" (prepare/execute).
>
> AIUI, if you disable use of the new protocol, it should work as before.
This is correct. Though generally not recommeded, you can switch it off
with the pg_server_prepare attribute like so:
$dbh->{pg_server_prepare} = 0;
This will force DBD::Pg to do the quoting itself, with the subsequent
penalty of speed and loss of auto type casting.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200511080815
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFDcKU7vJuQZxSWSsgRArwmAKDKe75V/TY4oYWLkiICN2osmJTmBwCcDXGy
p+yPZqpu0sv0Ov8hlBN0XkU=
=w+aj
-----END PGP SIGNATURE-----
MaXX <bs139412@skynet.be> writes:
> Can this be the cause of a huge loss of perf? I have the following query in
> a Perl script using DBI + DBD::Pg, AutoCommit => 0:
> SELECT stats_put_sources(?, ?, int4(?), int4(?))
> This syntax runs almost 10x faster than:
> SELECT stats_put_sources(?, ?, ?::int4, ?::int4)
You probably have no idea how hard that is to believe --- they should
certainly be just the same. Let's see a self-contained test case that
exhibits this problem.
regards, tom lane
Tom Lane wrote: > MaXX <bs139412@skynet.be> writes: >> Can this be the cause of a huge loss of perf? I have the following query >> in a Perl script using DBI + DBD::Pg, AutoCommit => 0: >> SELECT stats_put_sources(?, ?, int4(?), int4(?)) >> This syntax runs almost 10x faster than: >> SELECT stats_put_sources(?, ?, ?::int4, ?::int4) > You probably have no idea how hard that is to believe --- they should > certainly be just the same. Let's see a self-contained test case that > exhibits this problem. You're right and I'm stupid again... I found that I've changed from the Pg way to the SQL way *AND* commented a '$dbm->commit;' inside the loop. Removing the comment make the script slow as hell... Thats the only explanation. I may need a lot of rest... Sorry again, -- MaXX
On Nov 8, 2005, at 8:16 AM, Greg Sabino Mullane wrote:
> This is correct. Though generally not recommeded, you can switch it
> off
> with the pg_server_prepare attribute like so:
>
> $dbh->{pg_server_prepare} = 0;
>
> This will force DBD::Pg to do the quoting itself, with the subsequent
> penalty of speed and loss of auto type casting.
And a reduction in bugs... :-(
One I found the other day: if you set $dbh->{InactiveDestroy} it
still destroys all of your prepared statements. Context is when you
fork a child which needs to open its own connection, and the parent's
prepared statements go away.
Need to find some tuits to file the formal bug report.