<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><br><br>I concluded that the issue here is that somehow the rails rake task accesses the Postgres instance differently from pgAdmin3. Not sure exactly why ( I dig through the Rails source for a while but was unable to come up with a conclusive theory ), but when I use <b>rake db:create</b>, the database that gets created works fine for my Rails app but the PostGIS extensions are not available when I connect via pgAdmin3. <div><br></div><div>In other words, a database created with rake db:create will respond correctly in rails console:</div><div><br></div><div><div style="margin: 0px; font-size: 19px; font-family: 'Andale Mono'; color: rgb(254, 246, 249); background-color: rgb(114, 31, 152);">[6] pry(main)> <span style="text-decoration: underline ; color: #011b9c">ActiveRecord</span>::<span style="text-decoration: underline ; color: #011b9c">Base</span>.connection.execute(<span style="color: #58d944">"SELECT PostGIS_full_version();"</span>).first</div><div style="margin: 0px; font-size: 19px; font-family: 'Andale Mono'; color: rgb(254, 246, 249); background-color: rgb(114, 31, 152);">NOTICE: Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?</div><div style="margin: 0px; font-size: 19px; font-family: 'Andale Mono'; color: rgb(254, 246, 249); background-color: rgb(114, 31, 152);"> <span style="color: #ff67fc"> (0.8ms)</span> SELECT PostGIS_full_version();</div><div style="margin: 0px; font-size: 19px; font-family: 'Andale Mono'; color: rgb(88, 217, 68); background-color: rgb(114, 31, 152);"><span style="color: #fef6f9">=> {</span>"postgis_full_version"<span style="color: #fef6f9">=></span></div><div style="margin: 0px; font-size: 19px; font-family: 'Andale Mono'; color: rgb(88, 217, 68); background-color: rgb(114, 31, 152);"><span style="color: #fef6f9"> </span>"POSTGIS=<span style="color: #56d5e2">\"</span>2.1.1 r12113<span style="color: #56d5e2">\"</span> GEOS=<span style="color: #56d5e2">\"</span>3.4.2-CAPI-1.8.2 r3921<span style="color: #56d5e2">\"</span> PROJ=<span style="color: #56d5e2">\"</span>Rel. 4.8.0, 6 March 2012<span style="color: #56d5e2">\"</span> GDAL=<span style="color: #56d5e2">\"</span>GDAL 1.10.0, released 2013/04/24<span style="color: #56d5e2">\"</span> LIBXML=<span style="color: #56d5e2">\"</span>2.9.1<span style="color: #56d5e2">\"</span> LIBJSON=<span style="color: #56d5e2">\"</span>UNKNOWN<span style="color: #56d5e2">\"</span> RASTER"<span style="color: #fef6f9">}</span></div><div><span style="color: #fef6f9"><br></span></div><div><span style="color: #fef6f9"><br></span></div><div>However, this same database in pgAdmin3 will return the "No function available" error in pgAdmin3.</div><br>I checked my host, username, etc and I couldn't find any discrepancies.<br><br>Nonetheless, <i>when I avoid using rake db:create</i> and instead create the database directly in pgAdmin3, then run <b>CREATE EXTENSION postgis;</b> myself, run <b>rake db:migrate</b>, then import my data, everything works OK and the PostGIS extensions <i>are available in both</i> Rails console and pgAdmin3.</div><div><br></div><div><div>So, this seems like it is a Rails issue and not a PostGIS issue. Just wanted to follow up to say this is resolved.</div><div><br></div><div>-Jason<br><br><br><br><br>On Jun 10, 2014, at 1:38 PM, Jason Fleetwood-Boldt <<a href="mailto:tech@datatravels.com">tech@datatravels.com</a>> wrote:<br><br><blockquote type="cite"><br><br>I've narrowed down my problem (I think) but I really stuck on something here, any advice to point me in the right direction would be greatly appreciated. <br><br>Whenever I run the restore of my database, I get these kinds of errors (hundreds and hundreds of them... this just one example)<br><br>pg_restore: creating FUNCTION geography_out(geography)<br>pg_restore: [archiver (db)] Error from TOC entry 894; 1255 17188 FUNCTION geography_out(geography) postgres<br>pg_restore: [archiver (db)] could not execute query: ERROR: could not access file "$libdir/postgis-1.5": No such file or directory<br> Command was: CREATE FUNCTION geography_out(geography) RETURNS cstring<br> LANGUAGE c IMMUTABLE STRICT<br> AS '$libdir/postgis-1.5', 'geog...<br>pg_restore: [archiver (db)] could not execute query: ERROR: function public.geography_out(geography) does not exist<br> Command was: ALTER FUNCTION public.geography_out(geography) OWNER TO postgres;<br><br><br>So, it seems like something is not set up correctly with Postgis. I've tried droping & recreating my database (both from pgAdmin and also from rails rake tasks) multiple times, and followed the instructions here <a href="http://postgis.net/install/">http://postgis.net/install/</a> but not matter what I do I always see these errors when I run restore. <br><br><br>It seems like these functions aren't being created correctly, which makes sense to me, because then after I load up the database I am unable to use them in pgAdmin (except my rails app continues to work, completely counter-intuitively).<br><br><br>Can anyone point me in the right direction? I've spent several hours googling for answers here and am completely stuck at this point. <br><br>I am running Postgres 9.3.4.0<br><br>-Jason<br><br><br><br><br>On Jun 10, 2014, at 11:39 AM, Jason Fleetwood-Boldt <tech@datatravels.com> wrote:<br><br><blockquote type="cite"><br>After dropping & recreating my Postgres database, I am seeing some very strange behavior in pgAdmin while debugging my Rails app. <br><br>I'm getting this error:<br>function st_distance(postgis.geography, unknown) does not exist<br><br><br>Here's how I'm setting up my database<br><br>rake db:create<br>--> I now have 2 extensions plpgsql and postgis; also have two schemas postgis and public<br><br><br>rake db:migrate<br><br>--> I now have 3 extensions plpgsql, postgis, hstore<br>--> Also I now have 50 tables (what I am expecting)<br><br><br><br>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.<br><br>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:<br><br>ERROR: function st_distance(postgis.geography, unknown) does not exist<br>LINE 1: SELECT DISTINCT places.*, ST_Distance("places"."coords", '00...<br> ^<br>HINT: No function matches the given name and argument types. You might need to add explicit type casts.<br><br><br>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. <br><br>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.<br><br><br><br>My full query is below, as you can see it is rather large:<br><br>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<br><br><br><br><br><br>For your reference, my database.yml file looks like this:<br><br><br>common: &common<br> adapter: postgis<br> host: localhost<br> username: <%= ENV['USER'] %><br> encoding: unicode<br> pool: 5<br> timeout: 5000<br> postgis_extension: postgis<br> schema_search_path: public,postgis<br><br><br>development:<br> <<: *common<br> database: mn_development<br><br><br><br><br>_______________________________________________<br>postgis-users mailing list<br>postgis-users@lists.osgeo.org<br>http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users<br></blockquote><br>_______________________________________________<br>postgis-users mailing list<br>postgis-users@lists.osgeo.org<br>http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users<br></blockquote><br></div></div></body></html>