Search code examples
ruby-on-railspostgresqlherokupostgisrds

Problems using controller on postgres DB on AWS RDS from Heroku app

I've setup a postgres DB to use postgis for a Heroku App I have, running on Ruby on Rails.

My steps have been:

  1. Create RDS DB running pg 9.4.9
  2. Enable rds.force_ssl on RDS Parameter Group.
  3. Backup and load my Heroku database into new postgres DB.
  4. Download Amazon RDS CA Certificate and place it under config/amazon-rds-ca-cert.pem.
  5. Add gem 'activerecord-postgis-adapter' to my gem file.
  6. Update database.yml file to the below:

:

default: &default
    adapter: postgis
    encoding: unicode
    pool: 5

production:
    <<: *default
    encoding: utf8
    database: <%= ENV['RDS_DB_NAME'] %>
    username: <%= ENV['RDS_USERNAME'] %>
    password: <%= ENV['RDS_PASSWORD'] %>
    host: <%= ENV['RDS_HOSTNAME'] %>
    port: <%= ENV['RDS_PORT'] %>
    postgis_extension: postgis
    schema_search_path: public,postgis
  1. Update DATABASE_URL parameter on Heroku to postgres://myuser:mypassword@mydbinstance.xxxxxxxxxxx.us-west-2.rds.amazonaws.com/mydbname?sslmode=verify-full&sslrootcert=config/amazon-rds-ca-cert.pem
  2. Additional steps in this very helpful question: Enabling Ruby PostGIS support on Heroku
  3. Push updated code and access my App.

When I try to access the database to verify connectivity it works ok:

nc -zv mydbinstance.xxxxxxxx.us-west-2.rds.amazonaws.com 5432
Connection to mydbinstance.xxxxxxxxx.us-west-2.rds.amazonaws.com 5432 port [tcp/postgresql] succeeded!

and when I navigate on the app I am able to see the results of queries. However, when I try to use a model that uses postgis, somehow my connections don't work correctly. Here's my Controller's code, which stores lat, lng pairs with bus stop information, and finds the ones close to a point given in parameters:

class TransitStopsController < ApplicationController
    def create
    end

    def show
        @transit_stop = TransitStop.find(params[:id])
        @transit_routes = @transit_stop.transit_routes
    end

    def get_nearby_stops
        radius_in_meters = params[:radius_in_meters].nil? ? 3219 : params[:radius_in_meters]
        @nearby_stops = TransitStop.close_to(params[:lat], params[:lng], radius_in_meters)
    end
end

My Model:

class TransitStop < ActiveRecord::Base
  has_many :transit_stops_transit_routes, foreign_key: "onestop_id", class_name: "TransitStopsTransitRoute"
  has_many :transit_routes, through: :transit_stops_transit_routes, foreign_key: "route_onestop_id", class_name: "TransitRoute"
  validates_uniqueness_of :onestop_id

  #Get transit stops close to a given lat, lng pair. Default distance = 2 miles
  scope :close_to, -> (lat, lng, distance_in_meters = 3219) {
  where(%{
    ST_DWithin(
      ST_GeographyFromText(
          'SRID=4326;POINT(' || transit_stops.lng || ' ' || transit_stops.lat || ')'
        ),
        ST_GeographyFromText('SRID=4326;POINT(%f %f)'),
        %d
      )
    } % [lng, lat, distance_in_meters])
  }
end

and my View (get_nearby_stops.jbuilder):

json.nearby_stops @nearby_stops do |transit_stop|
  json.region_id transit_stop.region_id
  json.lat transit_stop.lat
  json.lng transit_stop.lng
  json.onestop_id transit_stop.onestop_id
  json.name transit_stop.name
  json.osm_way_id transit_stop.osm_way_id
  json.osm_way_id transit_stop.served_by_vehicle_types
  json.timezone transit_stop.timezone
  json.wheelchair_boarding transit_stop.wheelchair_boarding
  json.created_or_updated_in_changeset_id transit_stop.created_or_updated_in_changeset_id
  json.transit_routes transit_stop.transit_routes
end

Routes.rb has line added to access method:

get 'transit_stops/get_nearby_stops' => 'transit_stops#get_nearby_stops'

When I try to access this via: https://myherokuapp.herokuapp.com/transit_stops/get_nearby_stops.json?lat=-122.49766&lng=37.71677

All I get is a message:

Application Error

An error occurred in the application and your page could not be served. If you are the application owner, check your logs for details.

When I look at my heroku logs all I see is a timeout error: 

app[web.1]: Processing by TransitStopsController#get_nearby_stops as JSON
app[web.1]:   Parameters: {"lat"=>"-122.49766", "lng"=>"37.71677"}
heroku[router]: at=error code=H12 desc="Request timeout" method=GET path="/transit_stops/get_nearby_stops.json?lat=-122.49766&lng=37.71677" host=myherokuapp.herokuapp.com request_id=1e081fdf-d0ce-4000-a6b4-4e75c176b8a2 fwd="10.0.0.1" dyno=web.1 connect=0ms service=30001ms status=503 bytes=0

The AWS Error Logs show nothing.

The strange thing to me is that I am able to perform the same query by connecting to Heroku's rails console:

irb(main):001:0> TransitStop.close_to(-122.49766,37.71677,5000)
=> #<ActiveRecord::Relation [#<TransitStop region_id: 1, lat: #<BigDecimal:7fe69f6c95c0,'-0.122504106E3',18(18)>...
...
etc

So at this point I don't understand what's causing this controller to fail only when I access it through my view, but not through console? Should the database url be different if I use postgis methods?

Any help would be highly appreciated.

EDIT: I've tested the show method of my controller, and it works ok:

app<a class="jive-link-external" href="http://web.1">http://web.1</a>: Started GET "/transit_stops/s-9q8ys6puje-655johnmuirave.json" for 159.16.243.2 at 2016-11-12 19:55:16 +0000
app<a class="jive-link-external" href="http://web.1">http://web.1</a>: Processing by TransitStopsController#show as JSON
app<a class="jive-link-external" href="http://web.1">http://web.1</a>:   Parameters: {"id"=>"s-9q8ys6puje-655johnmuirave"}
heroku<a href="https://forums.aws.amazon.com/">router</a>: at=info method=GET path="/transit_stops/s-9q8ys6puje-655johnmuirave.json" host=myapp.herokuapp.com request_id=15814367-5235-484b-bff9-7727a2349dd0 fwd="10.0.0.1" dyno=web.1 connect=0ms service=329ms status=200 bytes=1385
app<a class="jive-link-external" href="http://web.1">http://web.1</a>:   Rendered transit_stops/show.json.jbuilder (107.3ms)
app<a class="jive-link-external" href="http://web.1">http://web.1</a>: Completed 200 OK in 217ms (Views: 6.7ms | ActiveRecord: 207.4ms)<br class="jive-newline" />

Which to me translates to perhaps there being some problem in the database to perform the query I'm doing? The query itself should be fast, as when I test it in console I always get a response almost immediately, but perhaps something about the configuration of the database needs to be changed?

Solution

  • Just for other people to see, my problem was that after the query, my view was also producing several additional queries, because the field 

    transit_stop.transit_routes

    is a relationship to a different table. Thus, for every resulting stop the database was performing many other queries for every result I got. Since my query was returning several hundred results, the final view performed several hundred additional queries which caused a lot of overhead to my db.

    To detect this I updated log_statement = all to my db parameter group, so that I could see all activity coming into the database. I've updated my query to make it more efficient by reducing the number of results to only the n nearest ones, with a maximum of 10, as I don't really have any use on getting so many results.