[postgis-users] Postgis function does not exist in pgAdmin; Rails has no problems
sracine at igreffe.net
sracine at igreffe.net
Tue Jun 10 13:00:39 PDT 2014
Hello Jason,
Do you use EnterpriseDB setup to install PostgreSQL and PostGIS? Are
you on Windows, Like-Nix or Mac platform? Which version of PostGIS WAS
installed in your previous backup? And which one is installed now on
your new setup?
It sounds like you try to restore some PostGIS functions from one
version to another one installed on your new setup. For example if
your backup contains PostGIS functions who work with version 1.5 and
your new installation is set on 2.1, it will not work. Type "SELECT
postgis_version()" in both databases (if you can for the old one) and
if you have different version numbers, you could have to do an hard
upgrade of PostGIS. See postgis_restore.pl script in PosgreSQL
library. You will need to install Perl Langage if you're not on Linux.
If you have to do an hard upgrade, please use manual restoring and
don't use ALTER EXTENSION postgis UPGRADE/UPDATE. What I heard about
it is that doesn't work fine for hard upgrade.
http://www.postgis.org/docs/postgis_installation.html#hard_upgrade
Another think to check is that POSTGIS functions must be in 'public'
schema. Do you have them in it?
Hope it will be useful
Sylvain Racine
iGreffe Géomatique
Granby
sracine at igreffe.net
http://www.igreffe.net
Jason Fleetwood-Boldt <tech at datatravels.com> a écrit :
> 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
--
Sylvain Racine
iGreffe Géomatique
50, rue Saint-Hubert, no. 5
Granby (Québec) J2G 5L9
T: 450.770.9974
C: sracine at igreffe.net
Site: www.igreffe.net
More information about the postgis-users
mailing list