Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #585

Issue where postgres randomly gets into state where it returns hstore as string

Show all headers | View raw

Summary: rails app using postgres randomly gets into a state where hstore array is returned as a string, and cannot be fixed without rebuilding the database

We have an app using rails 4.04 with ruby 1.9.3, postgres database version 9.2.6. Every day, in the test environment, we drop the database, create a new database, and restore the data from a production backup using pg_restore. The hstore extension is installed, and there is some hstore data in the database. The app doesn't get much usage in the test environment right now. For the purposes of this problem, we can say it gets no usage other than deployment.

The deployment process seems to work perfectly well about 99 times out of 100, but every once in a while, the app suddenly doesn't work. Trying to open the app, you get an error in the log like: ActionView::Template::Error(undefeined metghod 'keys' for "\"width\"=>"131.0\"".String)

The problem is that it is returning an hstore array as a string. From a non-working instance, here is some text from an irb session:

    irb(main):002:0> r=Row.first
    unknown OID: data(-1892003119) (SELECT  "rows".* FROM "rows"   ORDER BY "rows"."id" ASC LIMIT 1)
    => #<Row id: 1, data: "\"test123\"=>\"5\"", created_at: "2013-10-31 18:56:45", updated_at: "2013-10-31 18:57:05">

From a working instance, here is the same query:

    irb(main):002:0> r=Row.first
    => #<Row id: 1, data: {"test123"=>"5"}, created_at: "2013-10-31 18:56:45", updated_at: "2013-10-31 18:57:05">

You can see that in the broken instance, it is returning the hstore data as a string. Some pertinent info is that once it gets in this broken state, you can drop and restore the database, or rename the database, and as long as it's on the same postgres sever, it's still broken - you have to reinstall posgtgres for it to work. And if you point the app (change database.yml) to point to a different server, it works.

This happens with no code changes. Same ruby on rails code works, then on redeployment it doesn't work. We cannot figure out any repro scenario. It works, it works, it works, then we do the same thing we've done a hundred times and it breaks.

I posted this problem in the rails group before, but I'm reposting it here because it's clearly a postgres bug - Without changing the rails code, we can reinstall postgres and setup the database again and it starts working.

Has anyone ever seen anything like this?

Back to comp.databases.postgresql | Previous | Next — Next in thread | Find similar

Thread

Issue where postgres randomly gets into state where it returns hstore as string zebharadon@gmail.com - 2014-08-25 11:39 -0700
  Re: Issue where postgres randomly gets into state where it returns hstore as string Jasen Betts <jasen@xnet.co.nz> - 2014-08-26 06:55 +0000
    Re: Issue where postgres randomly gets into state where it returns hstore as string zebharadon@gmail.com - 2014-08-27 14:51 -0700
      Re: Issue where postgres randomly gets into state where it returns hstore as string Jasen Betts <jasen@xnet.co.nz> - 2014-08-28 12:47 +0000
  Re: Issue where postgres randomly gets into state where it returns hstore as string mattheww@chiark.greenend.org.uk (Matthew Woodcraft) - 2014-08-28 23:05 +0100
    Re: Issue where postgres randomly gets into state where it returns hstore as string mattheww@chiark.greenend.org.uk (Matthew Woodcraft) - 2014-08-28 23:15 +0100

csiph-web