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 rake db:create, the database that gets
created works fine for my Rails app but the PostGIS extensions are not
available when I connect via pgAdmin3.
In other words, a database created with rake db:create will respond correctly
in rails console:
[6] pry(main)> ActiveRecord::Base.connection.execute("SELECT
PostGIS_full_version();").first
NOTICE: Function postgis_topology_scripts_installed() not found. Is topology
support enabled and topology.sql installed?
(0.8ms) SELECT PostGIS_full_version();
=> {"postgis_full_version"=>
"POSTGIS=\"2.1.1 r12113\" GEOS=\"3.4.2-CAPI-1.8.2 r3921\" PROJ=\"Rel. 4.8.0,
6 March 2012\" GDAL=\"GDAL 1.10.0, released 2013/04/24\" LIBXML=\"2.9.1\"
LIBJSON=\"UNKNOWN\" RASTER"}
However, this same database in pgAdmin3 will return the "No function available"
error in pgAdmin3.
I checked my host, username, etc and I couldn't find any discrepancies.
Nonetheless, when I avoid using rake db:create and instead create the database
directly in pgAdmin3, then run CREATE EXTENSION postgis; myself, run rake
db:migrate, then import my data, everything works OK and the PostGIS extensions
are available in both Rails console and pgAdmin3.
So, this seems like it is a Rails issue and not a PostGIS issue. Just wanted to
follow up to say this is resolved.
-Jason
On Jun 10, 2014, at 1:38 PM, Jason Fleetwood-Boldt <[email protected]> wrote:
>
>
> 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.
>
> Whenever I run the restore of my database, I get these kinds of errors
> (hundreds and hundreds of them... this just one example)
>
> pg_restore: creating FUNCTION geography_out(geography)
> pg_restore: [archiver (db)] Error from TOC entry 894; 1255 17188 FUNCTION
> geography_out(geography) postgres
> pg_restore: [archiver (db)] could not execute query: ERROR: could not access
> file "$libdir/postgis-1.5": No such file or directory
> Command was: CREATE FUNCTION geography_out(geography) RETURNS cstring
> LANGUAGE c IMMUTABLE STRICT
> AS '$libdir/postgis-1.5', 'geog...
> pg_restore: [archiver (db)] could not execute query: ERROR: function
> public.geography_out(geography) does not exist
> Command was: ALTER FUNCTION public.geography_out(geography) OWNER TO
> postgres;
>
>
> 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
> http://postgis.net/install/ but not matter what I do I always see these
> errors when I run restore.
>
>
> 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).
>
>
> Can anyone point me in the right direction? I've spent several hours googling
> for answers here and am completely stuck at this point.
>
> I am running Postgres 9.3.4.0
>
> -Jason
>
>
>
>
> On Jun 10, 2014, at 11:39 AM, Jason Fleetwood-Boldt <[email protected]>
> wrote:
>
>>
>> After dropping & recreating my Postgres database, I am seeing some very
>> strange behavior in pgAdmin while debugging my Rails app.
>>
>> I'm getting this error:
>> function st_distance(postgis.geography, unknown) does not exist
>>
>>
>> Here's how I'm setting up my database
>>
>> rake db:create
>> --> I now have 2 extensions plpgsql and postgis; also have two schemas
>> postgis and public
>>
>>
>> rake db:migrate
>>
>> --> I now have 3 extensions plpgsql, postgis, hstore
>> --> Also I now have 50 tables (what I am expecting)
>>
>>
>>
>> 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.
>>
>> 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:
>>
>> ERROR: function st_distance(postgis.geography, unknown) does not exist
>> LINE 1: SELECT DISTINCT places.*, ST_Distance("places"."coords", '00...
>> ^
>> HINT: No function matches the given name and argument types. You might need
>> to add explicit type casts.
>>
>>
>> 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.
>>
>> 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.
>>
>>
>>
>> My full query is below, as you can see it is rather large:
>>
>> 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
>>
>>
>>
>>
>>
>> For your reference, my database.yml file looks like this:
>>
>>
>> common: &common
>> adapter: postgis
>> host: localhost
>> username: <%= ENV['USER'] %>
>> encoding: unicode
>> pool: 5
>> timeout: 5000
>> postgis_extension: postgis
>> schema_search_path: public,postgis
>>
>>
>> development:
>> <<: *common
>> database: mn_development
>>
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> [email protected]
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> [email protected]
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
