Обсуждение: Perl DBI + binary data in psql not working

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.

The sessions table is:
+-----------------------------+----------------------------------+-------+
|         Field               |              Type                | Length|
+-----------------------------+----------------------------------+-------+
| id                          | char() not null default ''       |    16 |
| length                      | int4 not null default 0          |     4 |
| a_session                   | lo                               |     4 |
+-----------------------------+----------------------------------+-------+

I have also tried the text type.  I can't tell if this is just a DBD::Pg
problem with binary data or if I am having a problem with binary data in
psql as well (which is why I am posting this to both lists, sorry.)

Does anyone have a solution to this?  I can fall back to either using the
Apache::Session::File or MySQL for the sessions but I would rather not.

Thanks.
--
Paul Schilling
pfschill@direct-learning.com

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