While supporting a long-term ROR web application, I got into a weird problem while setting up the environment to start up the app on my platform (Ruby 1.8.7, Rails 2.1.1, PostgreSQL 9 and Ubuntu 10.10). Symptoms of the problem show up when trying the following scenario:
In a development environment, start up the server, open a browser and enter the usual ' localhost:3000 ' URL to start the app showing home page; till now, no problems, but, when making any action (clicking a link or even trying to refresh the home page) causes the browser to generate a '500 Internal Server Error'. A problem in the session you might think, so did we.
Following the log files showed that the failure occurs when trying to unmarshal some binary data retrieved from the database; the data been saved in the database are different when retrieved! More digging down took us to the column in database where the binary data were saved. The column type was bytea, which is basically a variable length binary string. The problem turned out to be that, for this specific data type (bytea), the output representation of PostgreSQL 9 is different from previous versions. PostgreSQL 9 introduced a new 'hex' format for output; this format is different from the old 'escape' format that older versions of PostgreSQL used to provide. That was the problem, we save data in the database, expect it to be in 'escape' format when retrieved, but we get it in 'hex' format.
The solution is rather simple, tell PostgreSQL to return data in the old, familiar 'escape' format. This can be achieved using the following code
ALTER DATABASE database_name SET bytea_output TO 'escape';
This, being executed in PostgreSQL command line, guarantees that you get the data in the format you expect.