bytea corruption?

From: Nathan Jahnke <njahnke(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: bytea corruption?
Date: 2009-08-21 21:26:49
Message-ID: 89e8c360908211426r4deefa0bo1352018591a74b4f@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

got some binary data that changes when i insert and retrieve it later
from bytea column:

http://nate.quandra.org/data.bin.0.702601051229191

running 8.3 on debian 5.0.

example:

root=# create database testdb;
CREATE DATABASE
root=# \c testdb
You are now connected to database "testdb".
testdb=# create table testtable (id serial, data bytea);
NOTICE: CREATE TABLE will create implicit sequence "testtable_id_seq"
for serial column "testtable.id"
CREATE TABLE
testdb=#

--

#!/usr/bin/perl -w

use DBI;
use Digest::MD5 qw(md5 md5_hex md5_base64);

my $fh;
open( $fh, '/tmp/data.bin.0.702601051229191' ) or die $!;
binmode $fh;
my $data = do { local( $/ ) ; <$fh> } ;
close($fh);

#$data = '123abc';

my $encodeddata = $data;
$encodeddata =~ s!(\\|[^ -~])!sprintf("\\%03o",ord($1))!ge; #prepare
data for bytea column storage

my $connection = DBI->connect_cached("dbi:Pg:dbname=testdb;port=5432",
"root", "", {RaiseError=>1});

my $insert_sth = $connection->prepare('insert into testtable (data)
values (?) returning id');
$insert_sth->execute($encodeddata);
my $ref = $insert_sth->fetchrow_hashref;
my $id = $ref->{id};

my $getall_sth = $connection->prepare('select * from testtable where id=?');
$getall_sth->execute($id);
my $newref = $getall_sth->fetchrow_hashref;
my $newdata = $newref->{data};
$newdata =~ s!\\(?:\\|(\d{3}))!$1 ? chr(oct($1)) : "\\"!ge; #decode
bytea column storage format

print md5_hex($data).' ';
print '!' if md5_hex($data) ne md5_hex($newdata);
print '= '.md5_hex($newdata);

print "\n";

__END__

hash of data changes ... if you uncomment the $data = '123abc' line
you can see that it works with those six bytes fine, and it also works
with most other binary data, just not this binary data. any insight
would be appreciated. thanks.

nathan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alban Hertroys 2009-08-21 22:23:49 Re: Comparing arrays of composite types
Previous Message Tom Lane 2009-08-21 20:12:40 Re: Comparing arrays of composite types