Saturday, June 8, 2013

Heroku Postgres hell!


I have been struggling for days over an issue with my application displaying a 500 error on heroku, I just solved it and wanted to share what I found.

When working with Heroku, postgres is the DB of choice. The application was working up until a week ago when I merged in a fairly big change, this is when it stopped working.

Heroku logging was less than useless, it just showed me:
2013-06-09T05:43:49.447049+00:00 heroku[router]: at=info method=GET path=/ host=dmchallenge.herokuapp.com fwd="149.135.145.46" dyno=web.1 connect=1ms service=78ms status=500 bytes=1266
The way I finally figured out what was going on was to install the free heroku add-on for +New Relic.

I was getting the following error:
ActionView::Template::Error: PG::Error: ERROR: column "2013-06-01" does not existLINE 1: ..." WHERE "activities"."user_id" = $1 AND (date >= "2013-06-0... ^: SELECT SUM("activities"."value") AS sum_id FROM "activities" WHERE "activities"."user_id" = $1 AND (date >= "2013-06-01" and date <= "2013-06-30")
So, the solution in the end was change:
user.activities.where('date >= "' + start_date.to_s + '" and date <= "' + end_date.to_s + '"').sum(:value)
to this:
user.activities.where("date >= '" + start_date.to_s + "' and date <= '" + end_date.to_s + "'").sum(:value)
My lesson is now learnt.