PermaLink Postgresql 9.x non-Backwards Compatibility02/16/2012 03:02 AM
Porting a Linux app from Postgresl 8.4 to 9.1 was a bit too painful, so I thought I'd write up some of the things in case others hit the same problems. :-P

Most of the issues are from how differently it treats BYTEA (Postgresql BLOB equivalent) fields.

In order to insert binary data into these columns in the database, the official way to do this is to use an INSERT statement with backslash escaped non-viewable characters. This is done in the form
INSERT INTO mytable (bindata) VALUES(E'\000\\blablahascii'::bytea)
The E prefix is used to indicate that this is an escaped string. In 9.x, if you include the E, you'll get random 'invalid byte sequence for encoding "UTF8": 0x00' errors. If you remove the E, your SQL statements will not be compatible w/ pre-9.x databases that do expect the E prefix.

You can work around this, and keep your code compatible w/ all versions of Postgresql by using hex strings passed into the decode function like so
INSERT INTO mytable (bindata) VALUES ( DECODE('00FFAABBDEADBEEF','HEX') )

You can also use parameterized queries to do the insert by using an STL string (which can contain binary data) using the libpqxx library, but this isn't documented very well
   C.prepare("MyInsert", "INSERT INTO mytable (bindata) VALUES ($1::bytea)")
("bytea", pqxx::prepare::treat_binary);
  const string bin_data("x \0 \x01 \x02 \xff y", 11);
  T.prepared("MyInsert")(bin_data).exec();
It's probably a tossup as to which is more efficient because you'd have to convert your binary array into an STL string with this technique.

The other thing that bit me was Postgresql 9.x changes the default select output representation of BYTEA fields to "hex" (looks like a hex string prefixed with \x, e.g. '\xDEADBEEF') instead of the old "escape" format (same format as the way you insert it). The 3.1 version of the libpqxx apparently doesn't support the "hex" format, so if you read blobs back into binarystrings, they'll be corrupted. You can work around this by editing your postgresql.conf file and setting the bytea_output flag to "escape".

Comments :v
No comments.

Start Pages
RSS News Feed RSS Comments Feed CoComment Integrated
The BlogRoll
Calendar
June 2024
Su
Mo
Tu
We
Th
Fr
Sa
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Search
Contact Me
About Ken
Full-stack developer (consultant) working with .Net, Java, Android, Javascript (jQuery, Meteor.js, AngularJS), Lotus Domino