Binary data getting borked when saving to PostgreSQL

Question

I'll let this irb session do the talking (junk omitted with ....).

Create a model with some Marshalled data. `user_info` is a binary column

irb(main):011:0> p2 = Provider.create(user_info: Marshal.dump(ActiveSupport::HashWithIndifferentAccess.new({foo: 1, bar: 2}))
   (0.7ms)  BEGIN
  SQL (11.6ms)  INSERT INTO "providers" (...."user_info") VALUES (....$9) RETURNING "id"  [.....["user_info", "\x04\bC:-ActiveSupport::HashWithIndifferentAccess{\aI\"\bfoo\x06:\x06EFi\x06I\"\bbar\x06;\x06Fi\a"]]
   (3.6ms)  COMMIT
=> #<Provider id: 98, ..... user_info: "\x04\bC:-ActiveSupport::HashWithIndifferentAccess{\aI\"\bf...", provider_id: nil>

Check the value. Looks right:

irb(main):013:0> p2.user_info
=> "\x04\bC:-ActiveSupport::HashWithIndifferentAccess{\aI\"\bfoo\x06:\x06EFi\x06I\"\bbar\x06;\x06Fi\a"

Bad things happen: here I reload it and the value completely changes into a format Marshal can't load

irb(main):014:0> p2.reload
  Provider Load (5.3ms)  SELECT "providers".* FROM "providers" WHERE "providers"."id" = $1 LIMIT 1  [["id", 98]]
=> #<Provider id: 98, ...., user_info: "x0408433a2d416374697665537570706f72743a3a4861736857...", provider_id: nil>

irb(main):015:0> p2.user_info
=> "x0408433a2d416374697665537570706f72743a3a4861736857697468496e646966666572656e744163636573737b07492208666f6f063a0645466906492208626172063b06466907"

This session is running on Heroku after I recently moved my database from shared to their new free Dev Postgres.

I'm not sure if this is a heroku problem, a postgres problem or a rails problem. The strange thing is: I also migrated another instance (production) of the same app to the new Dev Posgress and it's not having this problem. I found this but it appears to have been fixed years ago. Anyone have experience with this?

Update

Looks like I found an answer after all from this excellent answer on SO: https://stackoverflow.com/a/8541304/65311

possible duplicate of ActiveRecord loads binary field incorrectly on Heroku, fine on OSX — mu is too short
– mu is too short, Commented Aug 23, 2012 at 4:05
I'd be careful with using Marshal for things that go in the database. Normalization issues aside, the Marshal format changes over time so you could run into problems. JSON in a text column (or even hstore) would work better over time, YAML is another option. — mu is too short
– mu is too short, Commented Aug 23, 2012 at 4:08
Thanks @muistooshort. I plan to move to an ActiveRecord::Store. — devth
– devth, Commented Aug 24, 2012 at 13:57
The hstore version would be a good idea if you have hstore (I'm pretty sure all the PostgreSQL databases at Heroku support hstore these days): github.com/softa/activerecord-postgres-hstore — mu is too short
– mu is too short, Commented Aug 24, 2012 at 18:05
Developing on top of one database and deploying on another is a recipe for disaster. Database independence is a nice idea but largely a myth unless you're willing to write to the lowest common denominator, test like crazy, and (usually) write your own portability layer. — mu is too short
– mu is too short, Commented Aug 24, 2012 at 20:28

CraigKerstiens · Accepted Answer · 2012-08-24 00:39:51Z

2

Heroku's docs explain how to configure the change for bytea in their newer databases at: https://devcenter.heroku.com/articles/heroku-postgresql#troubleshooting

answered Aug 24, 2012 at 0:39

CraigKerstiens

5,9891 gold badge27 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

devth Over a year ago

I missed that. Thanks for the link. The thing I don't understand is why my production db continued using escaped after I migrated it from shared to Heroku Postgres, while my dev db didn't.

Collectives™ on Stack Overflow

Binary data getting borked when saving to PostgreSQL

Create a model with some Marshalled data. `user_info` is a binary column

Check the value. Looks right:

Bad things happen: here I reload it and the value completely changes into a format Marshal can't load

Update

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Create a model with some Marshalled data. user_info is a binary column

Check the value. Looks right:

Bad things happen: here I reload it and the value completely changes into a format Marshal can't load

Update

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Create a model with some Marshalled data. `user_info` is a binary column