<div dir="ltr">Hi<div><br></div><div>What is your Postgis version? With Postgres 9.3 I suspect you are running something newer than Postgis 1.5, which would explain why <span style="font-family:'Lucida Grande';font-size:13px"> </span><span style="font-family:'Lucida Grande';font-size:13px">"$libdir/postgis-1.5" </span>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?<br>
</div><div><br></div><div>Åsmund</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jun 10, 2014 at 7:38 PM, Jason Fleetwood-Boldt <span dir="ltr"><<a href="mailto:tech@datatravels.com" target="_blank">tech@datatravels.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div style="word-wrap:break-word"><div><br></div><div><br></div><div>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. </div>
<div><br></div><div>Whenever I run the restore of my database, I get these kinds of errors (hundreds and hundreds of them... this just one example)</div><div><div style="margin:0px;font-size:13px;font-family:'Lucida Grande';min-height:16px">
<br></div><div style="margin:0px;font-size:13px;font-family:'Lucida Grande'">pg_restore: creating FUNCTION geography_out(geography)</div><div style="margin:0px;font-size:13px;font-family:'Lucida Grande'">pg_restore: [archiver (db)] Error from TOC entry 894; 1255 17188 FUNCTION geography_out(geography) postgres</div>
<div style="margin:0px;font-size:13px;font-family:'Lucida Grande'">pg_restore: [archiver (db)] could not execute query: ERROR: could not access file "$libdir/postgis-1.5": No such file or directory</div>
<div style="margin:0px;font-size:13px;font-family:'Lucida Grande'"> Command was: CREATE FUNCTION geography_out(geography) RETURNS cstring</div><div style="margin:0px;font-size:13px;font-family:'Lucida Grande'">
LANGUAGE c IMMUTABLE STRICT</div><div style="margin:0px;font-size:13px;font-family:'Lucida Grande'"> AS '$libdir/postgis-1.5', 'geog...</div><div style="margin:0px;font-size:13px;font-family:'Lucida Grande'">
pg_restore: [archiver (db)] could not execute query: ERROR: function public.geography_out(geography) does not exist</div><div style="margin:0px;font-size:13px;font-family:'Lucida Grande'"> Command was: ALTER FUNCTION public.geography_out(geography) OWNER TO postgres;</div>
</div><div><br></div><div><br></div><div>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/" target="_blank">http://postgis.net/install/</a> but not matter what I do I always see these errors when I run restore. </div>
<div><br></div><div><br></div><div>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).</div>
<div><br></div><div><br></div><div>Can anyone point me in the right direction? I've spent several hours googling for answers here and am completely stuck at this point. </div><div><br></div><div>I am running Postgres 9.3.4.0</div>
<div><br></div><div>-Jason</div><div><br></div><div><br></div><div><br></div><br><div><div><div class="h5"><div>On Jun 10, 2014, at 11:39 AM, Jason Fleetwood-Boldt <<a href="mailto:tech@datatravels.com" target="_blank">tech@datatravels.com</a>> wrote:</div>
<br></div></div><blockquote type="cite"><div><div class="h5"><div style="word-wrap:break-word"><div><br></div><div>After dropping & recreating my Postgres database, I am seeing some very strange behavior in pgAdmin while debugging my Rails app. </div>
<div><br></div><div>I'm getting this error:</div><div><span style="font-family:Monaco;font-size:12px">function st_distance(postgis.geography, unknown) does not exist</span></div><div><br></div><div><br></div><div>Here's how I'm setting up my database</div>
<div><br></div><div>rake db:create</div><div>--> I now have 2 extensions <b>plpgsql</b> and <b>postgis</b>; also have two schemas <b>postgis</b> and <b>public</b></div><div><br></div><div><br></div><div>rake db:migrate</div>
<div><br></div><div>--> I now have 3 extensions <b>plpgsql, </b> <b>postgis, hstore</b></div><div>--> Also I now have 50 tables (what I am expecting)</div><div><br></div><div><br></div><div><br></div><div>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.</div>
<div><br></div><div>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:</div><div><span style="font-family:Monaco;font-size:12px"><br>
</span></div><div><span style="font-family:Monaco;font-size:12px">ERROR:</span><span style="font-family:Monaco;font-size:12px"> </span><span style="font-family:Monaco;font-size:12px">function st_distance(postgis.geography, unknown) does not exist</span></div>
<div style="margin:0px;font-size:12px;font-family:Monaco">LINE 1: SELECT DISTINCT places.*, ST_Distance("places"."coords", '00...</div><div style="margin:0px;font-size:12px;font-family:Monaco"> ^</div>
<div style="margin:0px;font-size:12px;font-family:Monaco">HINT: No function matches the given name and argument types. You might need to add explicit type casts.</div><div><br></div><div><br></div><div><b>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. </b></div>
<div><br></div><div>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.</div><div><br></div><div><br></div><div><br></div>
<div>My full query is below, as you can see it is rather large:</div><div><br></div><div>SELECT DISTINCT places.*, ST_Distance("places"."coords", '0020000001000010e6c0527e94b7b289544044645492ff4ba5') AS distance, CASE WHEN (<a href="http://premium_listings.id" target="_blank">premium_listings.id</a> 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 <a href="http://fp.id" target="_blank">fp.id</a> 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 = <a href="http://places.id" target="_blank">places.id</a> LEFT OUTER JOIN premium_listings ON (<a href="http://premium_listings.id" target="_blank">premium_listings.id</a> = 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 = <a href="http://places.id" target="_blank">places.id</a> AND ur.rateable_type = 'Place' AND ur.user_id = -1 LEFT OUTER JOIN user_favorite_places AS fp ON fp.place_id = <a href="http://places.id" target="_blank">places.id</a> AND fp.user_id = -1 LEFT OUTER JOIN (SELECT <a href="http://places.id" target="_blank">places.id</a> 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 <a href="http://places.id" target="_blank">places.id</a>) AS featured_places ON <a href="http://featured_places.id" target="_blank">featured_places.id</a> = <a href="http://places.id" target="_blank">places.id</a> JOIN taggings place_taggings_e280fa7 ON place_taggings_e280fa7.taggable_id = <a href="http://places.id" target="_blank">places.id</a> 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 <a href="http://places.id" target="_blank">places.id</a>, <a href="http://premium_listings.id" target="_blank">premium_listings.id</a>, ur.rating, <a href="http://fp.id" target="_blank">fp.id</a>, featured_places.count ORDER BY premium_listing DESC, distance LIMIT 50</div>
<div><br></div><div><br></div><div><br></div><div><br></div><div><br></div><div>For your reference, my database.yml file looks like this:</div><div><br></div><div><br></div><div><div>common: &common</div><div> adapter: postgis</div>
<div> host: localhost</div><div> username: <%= ENV['USER'] %></div><div> encoding: unicode</div><div> pool: 5</div><div> timeout: 5000</div><div> postgis_extension: postgis</div><div> schema_search_path: public,postgis</div>
<div><br></div><div><br></div><div>development:</div><div> <<: *common</div><div> database: mn_development</div></div><div><br></div><div><br></div><div><br></div><div><br></div></div></div></div>_______________________________________________<br>
postgis-users mailing list<br><a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></blockquote>
</div><br></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>