On Thu, Oct 21, 2010 at 7:12 PM, ljb
<ljb9832@pobox.com> wrote:
Again: My complaint is that pre-9.0 libpq-based clients mis-decode the new
default hex format bytea data without reporting an error, and this danger is
insufficiently documented in the release notes.
I had some hoops thru which I had to jump to make our app compatible with both 8.x and 9.x so we could safely migrate our servers without having to coordinate a code push. It wasn't that bad, but part of the problem is that the DBD::Pg driver does not understand the new format... but it does unescape the "\x" marker for me to a simple "x", since that follows the traditional un-escaping rules for values returned from Pg. I don't like overriding defaults in the DB settings unless I *really* have to.
So my code now looks something like this:
if (substr($value,2,100) =~ m/^[0-9a-f]+$/) {
# hex coding of bytea from Postgres 9.0+
$self->log_debug('bytea hex decode');
# remove the leading \x. DBD::Pg descapes \x to x, so just remove x
$value =~ s/^[^0-9a-f]+//;
$value = pack('H*',$value); # convert hex to bytes
} else {
$self->log_debug('bytea escape decoded');
# Postgres < 9.0 encode auto handled by DBD::Pg
}
Unfortunately, Greg is saying that he has no time right now to release an updated DBD::Pg despite the fact that the code is written and merged into the development source tree... so it may be a while before perl people are happy. Luckily my above code will work even when DBD::Pg learns to do auto-escaping of bytea new format.
At least the current $dbh->quote() method still seems to create acceptable escaping for postgres 9.0 when you tell it you have a bytea field type.