Skip Menu |

This queue is for tickets about the DBD-Pg CPAN distribution.

Report information
The Basics
Id: 34840
Status: rejected
Priority: 0/
Queue: DBD-Pg

Owner: Nobody in particular
Requestors: fenlisesi [...]

Bug Information
Severity: Important
Broken in: 2.5.1
Fixed in: (no value)

Subject: (int4) bind type picked for varchar column.
MIME-Version: 1.0
X-Mailer: MIME-tools 5.418 (Entity 5.418)
Content-Type: text/plain
Charset: utf8
Content-Disposition: inline
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 14723
Download (untitled) / with headers
text/plain 14.3k

Message body is not shown because it is too large.

X-Spam-Status: No, hits=-2.6 required=8.0 tests=BAYES_00
In-Reply-To: <rt-3.6.HEAD-10198-1207853805-818.34840-4-0 [...]>
X-Mailer: JoyMail 2.01
Content-Type: text/plain; charset="utf-8"
X-RT-Original-Encoding: ascii
Received: from ( []) by (Postfix) with SMTP id 421394D8077 for <bug-DBD-Pg [...]>; Thu, 10 Apr 2008 18:05:08 -0400 (EDT)
Received: (qmail 25112 invoked from network); 10 Apr 2008 22:05:07 -0000
Received: from ( by with QMQP; 10 Apr 2008 22:05:07 -0000
Received: from (HELO ( by (qpsmtpd/0.43rc1) with ESMTP; Thu, 10 Apr 2008 15:05:02 -0700
Received: from ([] helo=localhost) by with smtp (Exim 4.60) (envelope-from <greg [...]>) id 1Jk4tN-0000ew-Ny; Thu, 10 Apr 2008 17:05:13 -0500
Delivered-To: cpan-bug+DBD-Pg [...]
Subject: Re: [ #34840] (int4) bind type picked for varchar column.
Return-Path: <greg [...]>
X-Original-To: bug-DBD-Pg [...]
X-PGP-Key: 2529 DF6A B8F7 9407 E944 45B4 BC9B 9067 1496 4AC8
Date: Thu, 10 Apr 2008 22:05:13 -0000
X-Spam-Level: *
Message-Id: <534654b0e37eed6a7223fcdca0ed9aba [...]>
To: bug-DBD-Pg [...]
From: "Greg Sabino Mullane" <greg [...]>
X-RT-Original-Encoding: utf-8
RT-Message-ID: <rt-3.6.HEAD-10198-1207865116-127.34840-0-0 [...]>
Content-Length: 4563
Download (untitled) / with headers
text/plain 4.4k
-----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/ line 1652 via at /usr/local/lib/perl5/site_perl/5.10.0/Ima/D\ line 398 <- prepare_cached= DBIx::ContextualFetch::st=HASH(0x86c6100) at /usr/local/lib/perl5/site_perl/5.10.0/Ima/ line 398 via at /usr/local/lib/perl5/site_perl/5.10.0/Class/\DBI/ 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/ line 646 via at /usr/local/lib/perl5/site_perl/5.10.0/Class/DBI/ 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 PGP Key: 0x14964AC8 200804101803 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkf+jtwACgkQvJuQZxSWSsgb1gCcCEGU8+D5ldwZ1z5coG9qmPUS rVQAmwYd/L/e5H6agSB885oc/+HiqTKw =GRuv -----END PGP SIGNATURE-----
MIME-Version: 1.0
X-Mailer: MIME-tools 5.418 (Entity 5.418)
Content-Disposition: inline
Charset: utf8
Message-Id: <rt-3.6.HEAD-9093-1208887880-1131.34840-0-0 [...]>
Content-Type: text/plain
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Original-Encoding: utf-8
Content-Length: 152
Download (untitled) / with headers
text/plain 152b
Rejecting as not-a-DBD::Pg bug. Please re-open if this is a mistake, but be aware that some other part of your toolchain is calling $dbh->bind_param().

This service is sponsored and maintained by Best Practical Solutions and runs on infrastructure.

Please report any issues with to