Re: [GENERAL] Perl DBI + binary data in psql not working

Paul Schilling writes:
> I am trying to use Apache::Session + psql and ran into a problem with
> binary data from Storable::nfreeze To demonstrate what is happening I
> wrote this quick script:
>
> #!/usr/bin/perl
> use DBI;
> use strict;
> use Storable qw(nfreeze thaw);
>
> my $dbh = DBI->connect("dbi:Pg:dbname=pfschill", '', '');
> my $st = $dbh->prepare_cached('
> INSERT INTO sessions (id, length, a_session) VALUES (?,?,?)');
>
> my $data = {'stuff' => 'other stuff'};
> my $serialized = nfreeze $data;
> my $session = "1234";
>
> $st->bind_param(1, $session);
> $st->bind_param(2, length $serialized);
> $st->bind_param(3, $serialized);
> $st->execute;
>
> Running this you get:
> DBD::Pg::st execute failed: ERROR: parser: parse error at or near ""
> Database handle destroyed without explicit disconnect.
>

Paul, the problem is that Postgres can't seem to save binary data in
TEXT fields. Apache::Session uses (n)freeze which produces binary data
for a TEXT field. So you can't use vanilla Apache::Session with
Postgres (ie. DBD::Pg) :(

The solution is to patch DBIStore.pm from Apache::Session 1.0.x to use
base64 encoding/decoding:

< use MIME::Base64 qw(encode_base64 decode_base64);
138,139c137,138
< my $serialized = encode_base64(nfreeze $session->{data});
<
---
> my $serialized = nfreeze $session->{data};
>
163d160
< my $serialized = encode_base64(nfreeze $session->{data});
165,167c162
< if (length $serialized > 8000) {
< die "session data too big";
< }
---
> my $serialized = nfreeze $session->{data};
202c197
< $session->{data} = thaw decode_base64($results->[0]);
---
> $session->{data} = thaw $results->[0];

You need the MIME::Base64 package from CPAN.

You might also realize that Postgres has an 8k limit on a row, so
saving big sessions is also a problem.

Jeffrey, I have cc'ed this to you. Maybe you can put two options for
the tie in one of the next version of Apache::Session

DataSourceCantStoreBinary and
DataSourceRowLimit

It would be also possible to write a subclass to DBIStore.pm

Regards,

Dirk