-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
Show quoted text> I am actually not sure that this is a DBD::Pg bug, but that seemed to be
> the most probable case. We have seen some anomalous behaviour in a
> Class::DBI::Sweet application, which we traced to (int4) bind type
> getting picked for a string such as '%Brown%' that is supposed to match
> a varchar column. We are using Postgres 8.3.1 and DBD::Pg 2.5.1. All
Almost certainly a Class:DBI bug. Never underestimate the ability of
ORMs to mess things up for you.
A quick workaround may be to turn off server-side prepares:
$dbh->{pg_server_prepare} = 0;
Let's look at the trace output a bit:
* We split the statement into 10 placeholders:
Placeholder number, fooname, id:
#1 FOONAME: ((null)) ID: (146370120)
#2 FOONAME: ((null)) ID: (146391432)
#3 FOONAME: ((null)) ID: (146387424)
#4 FOONAME: ((null)) ID: (146390280)
#5 FOONAME: ((null)) ID: (146168808)
#6 FOONAME: ((null)) ID: (146391768)
#7 FOONAME: ((null)) ID: (146370640)
#8 FOONAME: ((null)) ID: (146343648)
#9 FOONAME: ((null)) ID: (146369000)
#10 FOONAME: ((null)) ID: (146370880)
End pg_st_split_statement
Immediate prepare decision: dml=1 direct=0 server_prepare=1
prepare_now=0 PGLIBVERSION=80104
End dbd_st_prepare
1 <- prepare= DBIx::ContextualFetch::st=HASH(0x86c6100) at
/usr/local/lib/perl5/site_perl/5.10.0/i686-linux/DBI.pm line 1652 via
at /usr/local/lib/perl5/site_perl/5.10.0/Ima/D\BI.pm line 398
<- prepare_cached= DBIx::ContextualFetch::st=HASH(0x86c6100) at
/usr/local/lib/perl5/site_perl/5.10.0/Ima/DBI.pm line 398 via at
/usr/local/lib/perl5/site_perl/5.10.0/Class/\DBI/Sweet.pm line 332
-> bind_param for DBD::Pg::st
* For some reason, we're now telling DBD::Pg that placeholders
5 and 6 should be bound as ints. This is the bug, and it's presumably
because DBIx::Something is making explicit bind_param() calls.
(DBIx::ContextualFetch::st=HASH(0x86c6100)~0x8b99070 5 undef 4)
Begin dbd_bind_ph (ph_name: 5)
Bind (5) (type=4)
Placeholder (5) bound as type (int4) (type_id=23), length 0, value of
((null))
End dbd_bind_ph
<- bind_param= 1 at
/usr/local/lib/perl5/site_perl/5.10.0/Class/DBI.pm line 646 via at
/usr/local/lib/perl5/site_perl/5.10.0/Class/DBI/Sweet.pm line 334
-> bind_param for DBD::Pg::st
(DBIx::ContextualFetch::st=HASH(0x86c6100)~0x8b99070 6 undef 4)
Begin dbd_bind_ph (ph_name: 6)
Bind (6) (type=4)
Placeholder (6) bound as type (int4) (type_id=23), length 0, value of
((null))
End dbd_bind_ph
...
* Now we try to fetch the value of Taint, which fails because
we haven't executed yet. Could be another minor bug.
(DBIx::ContextualFetch::st=HASH(0x8b99070)~INNER 'Taint')
Begin dbd_st_FETCH (key: Taint sth: 146362864)
Cannot fetch value of Taint pre-execute
* Next we execute with a list of values.
-> execute for DBD::Pg::st
(DBIx::ContextualFetch::st=HASH(0x86c6100)~0x8b99070 "%Allen%" '22971'
'23401' '22923' "%Brown%" '22971' '23401' '22923' 10 0)
Begin dbd_bind_ph (ph_name: 1)
Bind (1) (type=0)
Placeholder (1) bound as type (unknown) (type_id=705), length 7, value
of (%Allen%)
Begin dbd_bind_ph (ph_name: 5)
Bind (5) (type=0)
Placeholder (5) bound as type (int4) (type_id=23), length 7, value of
(%Brown%)
End dbd_bind_ph
* Now we create a prepared statement and immediately execute it.
This is where the $dbh->{pg_server_prepare} would cause an alternate
path:
PQexec* decision: dml=1 direct=0 server_prepare=1 numbound=2 numphs=10
default=0
PQexecPrepared
Begin pg_st_prepare_statement
New statement name (dbdpg_p3202_18), oldprepare is 0
PQprepare
Begin pg_error (message: ERROR: operator does not exist: character
varying ~~ integer at character 122
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
number: 7)
================END DBI TRACE(7) EXCERPT====================
Since some of the placeholders are the same:
"%Allen%" '22971' '23401' '22923' "%Brown%" '22971' '23401' '22923' 10 0
you may want to use dollar-sign placeholders instead of question marks.
Another "fix" would be to manually set those columns back to unknown
(or text) before execute is called, but after DBIx:: sets them to i
int4. I'm not sure how much control you have at that level of abstraction
though.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200804101803
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAkf+jtwACgkQvJuQZxSWSsgb1gCcCEGU8+D5ldwZ1z5coG9qmPUS
rVQAmwYd/L/e5H6agSB885oc/+HiqTKw
=GRuv
-----END PGP SIGNATURE-----
RT for rt.cpan.org