当保存到PostgreSQL时，二进制数据会变成borked [英] Binary data getting borked when saving to PostgreSQL

问题描述

我会让这个irb会话进行谈话（用 .... 省略垃圾。）

用一些Marshalled数据创建一个模型。 user_info 是一个二元列

  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 （...user_info）VALUES（.... $ 9）RETURNINGid[..... [user_info，\x04\bC：-ActiveSupport :: HashWithIndifferentAccess {\\ \\\\\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> 
  

检查值。看起来正确：

<$ p $ user_info
=>\x04\bC：-ActiveSupport :: HashWithIndifferentAccess {\ aI \\\ \\ bfoo \x06：\x06EFi\x06I\\bbar\x06; \x06Fi\a

不好的事情发生了：在这里我重新加载它并且完全查看它的值更改为Marshal无法加载的格式

  irb（main）：014：0> p2.reload 
 Provider加载（5.3ms）SELECTproviders。* FROMprovidersWHEREproviders。id= $ 1 LIMIT 1 [[id，98]] 
 => ＃< Provider id：98，....，user_info：x0408433a2d416374697665537570706f72743a3a4861736857 ...，provider_id：nil> 
 
 irb（main）：015：0> p2.user_info 
 => x0408433a2d416374697665537570706f72743a3a4861736857697468496e646966666572656e744163636573737b07492208666f6f063a0645466906492208626172063b06466907
  

在最近将我的数据库从共享位置移动到新的空闲位置后，此会话在Heroku上运行 Dev Postgres 。

I'米不知道这是一个英雄问题，postgres问题或轨道问题。奇怪的是：我也将同一个应用程序的另一个实例（制作）迁移到了新的Dev Posgress，并且没有这个问题。我发现这个，但似乎有多年前已经确定。任何人都有这方面的经验？

更新

看起来我终于从这个优秀的答案中找到了答案SO： https://stackoverflow.com/a/8541304/65311

解决方案

Heroku的文档解释了如何在更新的数据库中配置bytea的更改： https://devcenter.heroku.com/articles/heroku-postgresql#troubleshooting

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

解决方案

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

这篇关于当保存到PostgreSQL时，二进制数据会变成borked的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！