Ticket #618 (new defect)
put in set search_path at top of all our scripts
|Reported by:||robe||Owned by:||pramsey|
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:
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.