Heroku上的Rails应用程序无法写入PostgreSQL数据库,只能读取

Heroku上的Rails应用程序无法写入PostgreSQL数据库,只能读取

问题描述:

我有一个Ruby on Rails应用程序,可以在本地与sqlite3数据库配合使用,并且可以毫无问题地保存和检索记录。

I have a Ruby on Rails app that works fine locally with a sqlite3 database and can save and retrieve records without issue.

http://moviedata.herokuapp.com/ 使用Postgresql数据库,即使看起来像日志说的,记录也不会保存他们是。从db读取的记录罚款并按预期显示数据。

When deployed to Heroku at http://moviedata.herokuapp.com/ using a postgresql database, records are not saving even though it looks like the logs say they are. Records read from the db fine and data is displayed as expected.

添加记录的尾部日志为:

The tailed logs for adding a record are:

2012-08-21T19:51:31+00:00 app[web.1]: 
2012-08-21T19:51:31+00:00 app[web.1]: 
2012-08-21T19:51:31+00:00 app[web.1]: Started POST "/" for 50.53.6.156 at 2012-08-21 19:51:31 +0000
2012-08-21T19:51:31+00:00 app[web.1]:   Parameters: {"utf8"=>"✓", "authenticity_token"=>"+BYQLzhrfDkUVW8UaHikHpmtGHxpeQ/yF4VByHh9m1I=", "movie"=>{"title"=>"The Running Man", "description"=>"A documentary about a public execution game show.", "year"=>"1987", "genre"=>"science fiction"}, "commit"=>"Create Movie"}
2012-08-21T19:51:31+00:00 app[web.1]: Processing by MoviesController#index as HTML
2012-08-21T19:51:31+00:00 app[web.1]:   Rendered movies/index.html.erb within layouts/application (5.1ms)
2012-08-21T19:51:31+00:00 app[web.1]: Completed 200 OK in 9ms (Views: 6.7ms | ActiveRecord: 0.9ms)
2012-08-21T19:51:31+00:00 heroku[router]: POST moviedata.herokuapp.com/ dyno=web.1 queue=0 wait=0ms service=17ms status=200 bytes=3479

添加记录后, heroku pg命令在postgres数据库上显示相同数量的行(11)。

The 'heroku pg' command shows the same number of rows (11) on the postgres database after a record is added.

这是我用来学习Rails和Heroku平台的简单应用程序。要重现此内容,只需访问 http://moviedata.herokuapp.com/ 并单击新电影,输入表单中的一些垃圾数据,然后点击创建电影。记录应该保存并显示在首页的列表中,但不是。

This is a simple app I built to learn Rails and the Heroku platform. To reproduce this, just visit http://moviedata.herokuapp.com/ and click "New Movie", enter some junk data in the form, and hit "create movie". The record should be saved and show up in the list on the front page, but it doesn't.

也许我需要打开,配置或激活以便能够写入postgres数据库?我似乎很奇怪,它可以读取但不能写入。还有什么比日志更好的方法来解决问题?

Is there perhaps something I have to turn on, configure, or activate in order to be able to write to the postgres database? Seems very strange to me that it could be read from but not written to. Any better way to troubleshoot than the logs?

我在本地使用Ruby 1.9.3,Rails,3.2.8,PostgreSQL 9.1.5,SQLite 3.7.9, Heroku Toolbelt 2.30.3。

Locally I'm using Ruby 1.9.3, Rails, 3.2.8, PostgreSQL 9.1.5, SQLite 3.7.9, Heroku Toolbelt 2.30.3.

编辑/更新:我将本地版本切换为使用psql。它也遇到不保存记录的相同问题。在用户设置为log_statement ='all'的情况下,在/var/log/postgresql/posgresql-9.1.main.log上的登录显示了很多选择,但是在尝试添加记录时,该日志显示了从未被命中的数据库。

Edit/Update: I switched the local version to use psql. It also experiences the same problem where records are not saved. With the user set to log_statement='all' The log in at /var/log/postgresql/posgresql-9.1.main.log shows lots of selects, but when the record add is attempted, the log shows the database never being hit.

领班显示要发布的数据,如下所示:

Foreman shows the data being posted, like so:

22:38:03 web.1     | Started POST "/" for 127.0.0.1 at 2012-08-21 22:38:02 -0700
22:38:03 web.1     | Processing by MoviesController#index as HTML
22:38:03 web.1     |   Parameters: {"utf8"=>"✓", "authenticity_token"=>"0AyxRbwl/Kgi05uI1KX8uxVUJjx9ylAA1ltdWgmunm4=", "movie"=>{"title"=>"Army of Darkness", "description"=>"A man fights the living dead using a boomstick.", "year"=>"1997", "genre"=>"horror"}, "commit"=>"Create Movie"}
22:38:03 web.1     |   Movie Load (0.8ms)  SELECT "movies".* FROM "movies" ORDER BY title
22:38:03 web.1     |   Rendered movies/index.html.erb within layouts/application (14.9ms)

提交失败的确听起来像一个很好的解释。我不确定如何检查驱动程序是否设置为提交或查看提交失败的方式/时间。

A failed commit does sound like a great explanation. I'm not yet sure how to check whether the driver is set to commit or to see how/when a commit might have failed.

这是一个非常简单的应用程序,没有负载平衡或复杂的配置,并且大多数代码是由 generate scaffold命令生成的,但是完全有可能在命中数据库之前某处违反了某些约束。也许有一种方法可以将工头(或Rails)日志级别提高到11?我还尝试使用Thin代替,并在log /文件夹中搜索日志文件,除了上面记录的内容外,没有发现其他任何内容。

This is a very simple application, with no load balancing or complex configuration and most of the code was generated by the 'generate scaffold' command, but it's entirely possible that there's some constraint that's being violated somewhere before the db is ever hit. Perhaps there's a way to crank the Foreman (or Rails) log level up to 11? I also tried using thin instead and scoured the log files in the log/ folder and didn't find anything other than what's logged above.

这听起来很像是一个事务问题,在工作后您不会 COMMIT 进行事务处理,因此更改丢失。如果您的SQLite驱动程序默认为在没有显式 COMMIT 或回滚的情况下关闭的 COMMIT ting事务,那么您的Pg驱动程序默认为 ROLLBACK ,您将得到描述的行为。如果SQLite默认情况下默认自动执行每个语句,而Pg驱动程序驱动程序默认情况下打开事务,则会发生同样的情况。

This sounds a lot like a transaction issue, where you aren't COMMITting your transactions after you do work, so the changes are lost. If your SQLite driver defaults to COMMITting transactions that're closed without an explicit COMMIT or rollback, and your Pg driver defaults to ROLLBACK, you'd get the behaviour described. The same will happen if the SQLite defaults to autocomitting each statement by default, and the Pg driver driver defaults to opening a transaction.

如果要上线,请使用与部署时相同的本地数据库进行测试。

This is one of the many good reasons to use the same local database for testing as you're going to deploy to when you want to go live.

如果您使用的是普通的Pg实例,我会告诉您要在 postgresql.conf 中启用 log_statement ='all',重新加载Pg,并查看日志。您无法在Heroku上执行此操作,但可以使用 heroku logs --ps postgres 访问Pg日志。尝试运行 ALTER USER my_heroku_user SET log_statement ='all'; ,重新测试并检查日志。

If you were on a normal Pg instance I'd tell you to enable log_statement = 'all' in postgresql.conf, reload Pg, and watch the logs. You can't do that on Heroku, but you do have access to the Pg logs with heroku logs --ps postgres. Try running ALTER USER my_heroku_user SET log_statement = 'all';, re-testing, and examining the logs.

或者,在本地安装Pg。

Alternately, install Pg locally.

想到的其他不太可能的可能性:

Other less likely possibilities that come to mind:


  • 您正在使用长时间运行的SERIALIZABLE事务进行读取,因此其快照永远不会更新。

  • 对数据库对象的权限导致 INSERT s, UPDATE 等等失败,而您的应用则忽略了由此产生的错误。再次,不太可能。

  • 您有请勿执行规则,这些规则没有达到您的期望,或者之前触发器返回 NULL ,从而无声地将操作转换为无操作。如果您正在使用SQLite进行测试,似乎不太可能。

  • 您正在写入的数据库与读取的数据库不同。在尝试从热备份集群等中读取的设置中并非没有可能。

  • You're using long-running SERIALIZABLE transactions for reads, so their snapshot never gets updated. Pretty unlikely.
  • Permissions on database objects are causing INSERTs, UPDATEs, etc to fail, and your app is ignoring the resulting errors. Again, unlikely.
  • You have DO INSTEAD rules that don't do what you expect, or BEFORE triggers that return NULL, thus silently turning operations into no-ops. Seems unlikely if you're testing with SQLite.
  • You're writing to a different DB than you're reading from. Not impossible in setups that're attempting to read from a cluster of hot standbys, etc.