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

Tue Jun 10 13:46:46 PDT 2014

Jason,

can you try 

SELECT postgis_full_version();

To see if you get anything.  If rails works, then you probably have PostGIS
installed but a newer version 2.0 or 2.1 and might have a mix of stuff from
old 1.5.

The best way to upgrade a 1.5 to 2.something is going hard upgrade:
Basically filtering out all the 1.5 functions etc. from your backup as
documented here:

http://postgis.net/docs/manual-2.1/postgis_installation.html#hard_upgrade

hope that helps,
Regina

  _____  

From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Jason
Fleetwood-Boldt
Sent: Tuesday, June 10, 2014 1:39 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Postgis function does not exist in pgAdmin;
Rails has no problems

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140610/ecc8bed7/attachment.html>