Re: Gripe: bytea_output default => data corruption

On Thu, Oct 21, 2010 at 7:12 PM, ljb <ljb9832(at)pobox(dot)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.