Thread: Writing binary data (bytea) fails in PostgreSQL 9.3, succesful in PostgreSQL 9.0

Dear list,

I am trying to write binary data to bytea column in PostgreSQL 9.3 with
unixODBC, but the process fails. Symptom is, that some data is escaped
in bytea column after write, and the overall length of binary data is
thus increased during write,resulting corrupted data. Same code works
perfectly with PostgreSQL 9.0, length of binary data is preserved.

Exact versions of servers are PostreSQL 9.3.6 and 9.0.3.

Versions of drivers used (rpm packages, up to date Fedora 20):

----8<----
postgresql-odbc-09.03.0400-3.fc20.x86_64
perl-DBD-ODBC-1.50-3.fc20.x86_64
unixODBC-2.3.2-4.fc20.x86_64
----8<----

I have tried to isolate the problem, and wrote a perl script to be able
to recreate the problem. Please see the details below.

Should I use some extra configuration parameters to force binary data
write for postgres 9.3, or is there something else I'm doing wrong?

Perl DBD::Pg -driver (not odbc) seems to handle binary data write
correctly (code not provided here), but odbc is really THE option for
me, since I am using odbc functionality from R, using RODBC. This code
excerpt is simply an isolation of problems I faced in RODBC.

Details follow:

* perl script to reconstruct the problem in postgresql 9.0 and
postgresql 9.3 databases.
----8<----
#!/usr/bin/perl -w
use strict;

use DBI qw(:sql_types);

# postgresql 9.0
my $data_source90 = q/dbi:ODBC:pg90/;
# postgresql 9.3
my $data_source93 = q/dbi:ODBC:pg93/;

# Connect to the data source and get a handle for that connection.
my $dbh90 = DBI->connect($data_source90)
     or die "Can't connect to $data_source90: $DBI::errstr";

my $dbh93 = DBI->connect($data_source93)
     or die "Can't connect to $data_source93: $DBI::errstr";

# read file
my $buffer = "";
#my $infile = "read_me_in.png";
my $infile = "read_me_in.wld";
open (INFILE, "<", $infile) or die "Not able to open the file. \n";
binmode (INFILE);
my $file;
#Read file in 64K blocks
while ( (read (INFILE, $buffer, 65536)) != 0 ) {
   $file .= $buffer;
}
close (INFILE) or die "Not able to close the file: $infile \n";

print length($file), "\n";

my $sth90 = $dbh90->prepare("INSERT INTO data_queue (data) VALUES (?)")
     or die "Can't prepare statement: $DBI::errstr";
$sth90->bind_param(1, $file, {TYPE => SQL_BINARY});
$sth90->execute();

my $sth93 = $dbh93->prepare("INSERT INTO data_queue (data) VALUES (?)")
     or die "Can't prepare statement: $DBI::errstr";
$sth93->bind_param(1, $file, {TYPE => SQL_BINARY});
$sth93->execute();

# Disconnect the database from the database handle.
$dbh90->disconnect;
$dbh93->disconnect;
----8<----


Excerpt from psql, after data is input to database:

postgres 9.3 (binary write failed)

----8<----
data_static=>  select length(data) from data_queue ;
  length
--------
     134
(1 row)

data_static=>  select encode(data,'escape') from data_queue ;
encode

-----------------------------------------------------------------------------------------------------------------------------------------------------

1802\\0568571428571\\0120\\0560000000000\\0120\\0560000000000\\012\\0551742\\0568571428573\\01298901\\0564285714286\\0127779128\\0565714286640\\012
(1 row)

data_static=> \d data_queue
                Table "public.data_queue"
           Column          |           Type           | Modifiers
--------------------------+--------------------------+-----------
  id                       | bigint                   |
  data                     | bytea                    |
----8<----


postgres 9.0 (all OK)

----8<----
data_static=> select encode(data,'escape') from data_queue ;
        encode
--------------------
  1802.8571428571   +
  0.0000000000      +
  0.0000000000      +
  -1742.8571428573  +
  98901.4285714286  +
  7779128.5714286640+

(1 row)


data_static=> \d data_queue
                Table "public.data_queue"
           Column          |           Type           | Modifiers
--------------------------+--------------------------+-----------
  id                       | bigint                   |
  data                     | bytea                    |

data_static=> select length(data) from data_queue ;
  length
--------
      95
(1 row)
----8<----

Best regards,

M.Sc Esa Östring
Data scientist
Infotripla Oy
Tampere, Finland