ActiveRecord loads binary field incorrectly on Heroku, fine on OSX

Question

I have a rails 3.1 app that stores images in a binary field in a postgresql database (I am aware of potential issues with storing images in a database, but have to do so for now). Everything works fine locally in development mode and specs on OSX, but all images are broken in the app deployed to Heroku. I've verified that the data in the database is correct by pointing my local machine at the same database that the heroku instance uses, and all images displayed correctly.

So, the problem seems to lie in ActiveRecord (running on Heroku) loading the data from the database. I'm also guessing it is an encoding issue. Running the rails console locally I can verify that the bytesize of these fields are correct, but running the rails console on Heroku shows an incorrect bytesize. In fact, loading an N byte file via ActiveRecord on Heroku results in a bytesize of 2N+1 for all files.

Any help is greatly appreciated.

Answer 1

The 2n+1 smells like you're getting hex output from your bytea instead of the old escaped format. I'm guessing that you're using a dedicated database and that means PostgreSQL 9.0 which has a different default encoding for bytea:

When migrating from PostgeSQL 8.x to PostgreSQL 9.x you can run into issues with binary string compatibility. The default representation is hex in version 9 but version 8 it is set to escaped. You can make PostgreSQL 9 use escaped by manually setting bytea_output.

If I'm right then you can use the instructions in the above link or use this summarized version:

1. In the command line type "heroku config vars" to get your system details.
2. Extract the PostgreSQL username from your DATABASE_URL which looks like postgres://username:password@host/database_name.
3. Use heroku pg:psql to get a PostgreSQL console.
4. Execute ALTER ROLE username SET bytea_output TO 'escape'; from the the psql console where, of course, username is the username from (1).
5. Exit psql do a heroku restart to restart your application.

Then try again and hopefully you'll get the right bytes.