<html><head><meta http-equiv="Content-Type" content="text/html charset=us-ascii"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><div><br></div><div>After dropping & recreating my Postgres database, I am seeing some very strange behavior in pgAdmin while debugging my Rails app. </div><div><br></div><div>I'm getting this error:</div><div><span style="font-family: Monaco; font-size: 12px;">function st_distance(postgis.geography, unknown) does not exist</span></div><div><br></div><div><br></div><div>Here's how I'm setting up my database</div><div><br></div><div>rake db:create</div><div>--> I now have 2 extensions <b>plpgsql</b> and <b>postgis</b>; also have two schemas <b>postgis</b> and <b>public</b></div><div><br></div><div><br></div><div>rake db:migrate</div><div><br></div><div><div>--> I now have 3 extensions <b>plpgsql, </b> <b>postgis, hstore</b></div></div><div>--> Also I now have 50 tables (what I am expecting)</div><div><br></div><div><br></div><div><br></div><div>Then I restore my production dump to my local dev machine. At this point, everything looks ok when I examine it in pgAdmin, but I am unable to use any Postgis functions.</div><div><br></div><div>Now here's where it gets weird. When my query runs in Rails, it works fine (as expected). However, when i run it in pgAdmin, it fails, giving me this error:</div><div><span style="font-family: Monaco; font-size: 12px;"><br></span></div><div><span style="font-family: Monaco; font-size: 12px;">ERROR:</span><span style="font-family: Monaco; font-size: 12px;"> </span><span style="font-family: Monaco; font-size: 12px;">function st_distance(postgis.geography, unknown) does not exist</span></div><div style="margin: 0px; font-size: 12px; font-family: Monaco;">LINE 1: SELECT DISTINCT places.*, ST_Distance("places"."coords", '00...</div><div style="margin: 0px; font-size: 12px; font-family: Monaco;"> ^</div><div style="margin: 0px; font-size: 12px; font-family: Monaco;">HINT: No function matches the given name and argument types. You might need to add explicit type casts.</div><div><br></div><div><br></div><div><b>When this query runs inside of my Rails app it executes as expected -- which is behavior I've never seen from Rails before, so I must be missing something basic. </b></div><div><br></div><div>Can anyone point in the right direction here, I'm really stuck trying to figure why Rails behaves differently than pgAdmin on the exact same database.</div><div><br></div><div><br></div><div><br></div><div>My full query is below, as you can see it is rather large:</div><div><br></div><div>SELECT DISTINCT places.*, ST_Distance("places"."coords", '0020000001000010e6c0527e94b7b289544044645492ff4ba5') AS distance, CASE WHEN (premium_listings.id IS NOT NULL AND (ST_Distance(places.coords, '0020000001000010e6c0527e94b7b289544044645492ff4ba5')*0.621371) < premium_listings.reach_radius_mi) THEN 1 ELSE 0 END as premium_listing, ur.rating AS user_rating, CASE WHEN fp.id IS NULL THEN 'false' ELSE 'true' END AS user_favorite, CASE WHEN featured_places.count > 0 THEN 'true' ELSE 'false' END AS featured FROM "places" LEFT OUTER JOIN premium_listing_places ON premium_listing_places.place_id = places.id LEFT OUTER JOIN premium_listings ON (premium_listings.id = premium_listing_places.premium_listing_id AND premium_listings.starts_at < NOW() AND premium_listings.ends_at > NOW()) LEFT OUTER JOIN ratings AS ur ON ur.rateable_id = places.id AND ur.rateable_type = 'Place' AND ur.user_id = -1 LEFT OUTER JOIN user_favorite_places AS fp ON fp.place_id = places.id AND fp.user_id = -1 LEFT OUTER JOIN (SELECT places.id AS id, COUNT(*) AS count FROM "promotions" INNER JOIN "place_promotions" ON "place_promotions"."promotion_id" = "promotions"."id" INNER JOIN "places" ON "places"."id" = "place_promotions"."place_id" WHERE "promotions"."featured" = 't' AND ((("promotions"."starts_at" <= '2014-06-10' AND "promotions"."ends_at" >= '2014-06-10') OR ("promotions"."starts_at" <= '2014-06-10' AND "promotions"."ends_at" IS NULL))) GROUP BY places.id) AS featured_places ON featured_places.id = places.id JOIN taggings place_taggings_e280fa7 ON place_taggings_e280fa7.taggable_id = places.id AND place_taggings_e280fa7.taggable_type = 'Place' WHERE "places"."status" = 'approved' AND (ST_DWithin("places"."coords", '0020000001000010e6c0527e94b7b289544044645492ff4ba5', 50000)) AND (place_taggings_e280fa7.tag_id = 80 OR place_taggings_e280fa7.tag_id = 102 OR place_taggings_e280fa7.tag_id = 8 OR place_taggings_e280fa7.tag_id = 76 OR place_taggings_e280fa7.tag_id = 260 OR place_taggings_e280fa7.tag_id = 261 OR place_taggings_e280fa7.tag_id = 217 OR place_taggings_e280fa7.tag_id = 226 OR place_taggings_e280fa7.tag_id = 258 OR place_taggings_e280fa7.tag_id = 286 OR place_taggings_e280fa7.tag_id = 155 OR place_taggings_e280fa7.tag_id = 262) GROUP BY places.id, premium_listings.id, ur.rating, fp.id, featured_places.count ORDER BY premium_listing DESC, distance LIMIT 50</div><div><br></div><div><br></div><div><br></div><div><br></div><div><br></div><div>For your reference, my database.yml file looks like this:</div><div><br></div><div><br></div><div><div>common: &common</div><div> adapter: postgis</div><div> host: localhost</div><div> username: <%= ENV['USER'] %></div><div> encoding: unicode</div><div> pool: 5</div><div> timeout: 5000</div><div> postgis_extension: postgis</div><div> schema_search_path: public,postgis</div><div><br></div><div><br></div><div>development:</div><div> <<: *common</div><div> database: mn_development</div></div><div><br></div><div><br></div><div><br></div><div><br></div></body></html>