BYTEA / DBD::Pg change in 9.0 beta - Mailing list pgsql-hackers

From Jesper Krogh
Subject BYTEA / DBD::Pg change in 9.0 beta
Date
Msg-id 4BF2FE7C.8080201@krogh.cc
Whole thread Raw
Responses Re: BYTEA / DBD::Pg change in 9.0 beta
List pgsql-hackers
Hi.

I'm trying to do a test move of one of our applications onto 9.0beta1.
We use storable and serializes data into a bytea column in the database.
This script uses that:

#!/usr/bin/perl
use strict;
use warnings;
use Storable;
use DBI;
use DBD::Pg;
use Data::Dumper;

my $dbh = DBI->connect("dbi:Pg:dbname=testdb","","",{AutoCommit => 1});
my $sql = <<END
create table testtable (id serial, testbytea bytea);
END
;

eval {
$dbh->do($sql);
};
$dbh->do("delete from testtable");
my $href = { this => "1", that => "2"};
print "Before: " . Dumper($href) . "\n";
my $sth = $dbh->prepare("insert into testtable (testbytea) values (?)");
my $frozen = Storable::nfreeze($href);
$sth->bind_param(1, $frozen, { pg_type=>DBD::Pg::PG_BYTEA });
$sth->execute;
$sth = $dbh->prepare("select testbytea from testtable");
$sth->execute();
my $row = $sth->fetchrow_hashref();
my $href2 = Storable::thaw($row->{testbytea});

print Dumper($href2);


Running it against 8.4 gives:

$ perl bin/test-bytea
NOTICE:  CREATE TABLE will create implicit sequence "testtable_id_seq1" 
for serial column "testtable.id"
DBD::Pg::db do failed: ERROR:  relation "testtable" already exists at 
bin/efam/test-bytea line 16.
Before: $VAR1 = {          'that' => '2',          'this' => '1'        };

$VAR1 = {          'that' => '2',          'this' => '1'        };

Whereas 9.0beta1 gives:
$ perl bin/test-bytea
NOTICE:  CREATE TABLE will create implicit sequence "testtable_id_seq1" 
for serial column "testtable.id"
DBD::Pg::db do failed: ERROR:  relation "testtable" already exists at 
bin/efam/test-bytea line 16.
Before: $VAR1 = {          'that' => '2',          'this' => '1'        };

Storable binary image v60.48 more recent than I am (v2.7) at 
../../lib/Storable.pm (autosplit into ../../lib/auto/Storable/thaw.al) 
line 366, at bin/test-bytea line 28

Inspecting the data seems that it is the insert that does something to 
the data:

8.4 id |                                       testbytea
----+----------------------------------------------------------------------------------------  9 | 
\005\007\003\000\000\000\002\012\0012\000\000\000\004that\012\0011\000\000\000\004this
(1 row)

9.0beta1 id |                          testbytea
----+--------------------------------------------------------------  3 |
\x050703000000020a013200000004746861740a01310000000474686973
(1 row)


Jesper


pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: fillfactor gets set to zero for toast tables
Next
From: Heikki Linnakangas
Date:
Subject: Re: Keepalive for max_standby_delay