Opened 10 years ago

Closed 6 years ago

Last modified 3 years ago

#618 closed defect (invalid)

put in set search_path at top of all our scripts

Reported by: robe Owned by: pramsey
Priority: medium Milestone: PostGIS Fund Me
Component: postgis Version: 1.5.X
Keywords: Cc:

Description

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.

Change History (3)

comment:1 Changed 9 years ago by robe

Milestone: PostGIS 1.5.3PostGIS Future

comment:2 Changed 6 years ago by robe

Resolution: invalid
Status: newclosed

this is a non-issue now that we have extensions. Extensions are smart enough to remember the schema they were installed in and only upgrade that.

comment:3 Changed 3 years ago by robe

Milestone: PostGIS FuturePostGIS Fund Me

Milestone renamed

Note: See TracTickets for help on using tickets.