[postgis-users] Postgis function does not exist in pgAdmin; Rails has no problems

Tue Jun 10 11:34:39 PDT 2014

Hi

What is your Postgis version? With Postgres 9.3 I suspect you are running
something newer than Postgis 1.5, which would explain why
"$libdir/postgis-1.5" isn't found on your system. It seems like your
restoration script is breaking the postgis functions in your new DB by
replacing them with functions from your old DB. Could you try to create a
new dump without function definitions, or perhaps strip away the CREATE OR
REPLACE FUNCTION statements in your current dump?

Åsmund

On Tue, Jun 10, 2014 at 7:38 PM, Jason Fleetwood-Boldt <tech at datatravels.com
> 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 <tech at datatravels.com>
> 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
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140610/287bf83e/attachment.html>