Saturday, February 5, 2011

PostgreSQL 9.0 ... "Feature" for bytea Type


Several of the functions in the PostgreSHR project that I'm currently working on need to save large objects into tables. These include the message log (ie: what messages has this service already processed) for query continuation, the contents of clinical documents, etc... etc...
Anyways, a couple of days ago I upgraded from Postgres 8.4 to 9.0. Everything seemed to work fine, until I started analyzing the log files of the message persistence service components. The following error appeared:

Data at the root level is invalid (1,1)

On a formatter within the Everest Framework. After searching the database, I found that messages were being persisted into the message table properly, however whenever I read back the data NPGSQL would return a corrupted byte array. After some digging, I found that all calls to bytea columns from NPGSQL returned corrupted data.
Apparently this is due to a change in PostgreSQL 9.0 that affects the encoding of bytea columns. So, to anyone struggling with seemingly nonsensical data loss from PGSQL 9.0 tables, here is the fix:

ALTER DATABASE XXXXXXDB SET bytea_output='ESCAPE';
After applying this to the database all functionality was restored!

No comments:

Post a Comment