[postgis-devel] [PostGIS] #618: put in set search_path at top of all our scripts

[PostGIS]

#618: put in set search_path at top of all our scripts
---------------------+------------------------------------------------------
 Reporter:  robe     |       Owner:  pramsey      
     Type:  defect   |      Status:  new          
 Priority:  medium   |   Milestone:  PostGIS 1.5.3
Component:  postgis  |     Version:  1.5.X        
 Keywords:           |  
---------------------+------------------------------------------------------
 People can mark this invalid if they want, but I think we should put in a

 SET search_path = public;

 at the top of our scripts (and people can of course change it if they want
 which they would have to if they wanted to install postgis in a separate
 schema anyway).  This I see is a standard for most of the contribs in the
 contribs folder.  This little change would have saved me a bit of grief.

 After I got over being steamed having to restore data for 2 hours (luckily
 I had a good backup and on the bright side it did force me to test my
 backup) :), it occurred to me this would never have happened had that
 stupid drop cascade geometry not be in place or we had set search_path =
 public on.

 You see I've been following Paul's words of wisdom for a while:

 http://blog.cleverelephant.ca/2010/09/postgis-back-up-restore.html

 Except in addition to that I had set the default schema of my data not to
 be public so users less experienced than me (the IRONY) would not
 accidentally put data in public.

 So what happened is I forgot to do a set search_path when doing my (should
 have been a minor) postgis upgrade.  To my disbelief my indexes no longer
 worked.  It then occurred to me that the upgrade had reinstalled all the
 postgis functions and operators in my default schema, but I don't think it
 tried to install a new geometry or geography type.

 Stupid me -- I assumed if I uninstalled, it would uninstall all the stuff
 it had installed in my default schema (which it did), but the drop
 geometry cascade (thank you very much), also wiped out a good chunk of
 stuff in public (AND ALL MY GEOMETRY COLUMNS in my data schema).

 Sorry for the long story.

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/618>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.