[OPEN-ILS-DEV] Postgres schema search path considered harmful

[John Craig]

Hi Folks, In dealing with the 2.1.x upgrade scripts, I ran into a problem described in this bug report: https://bugs.launchpad.net/evergreen/+bug/881774 On a philosophical note: since there is no straightforward way to trace back to its intended home a database object that does not have an explicit schema name; it is sure a lot simpler to troubleshoot and maintain a script that always uses object names with their schema explicitly listed before the object name. This is the kind of thing where what may be dead obvious to the person who wrote the script (and is quite aware of what schema an object is part of) is painfully inscrutable to someone trying to deal with an issue or extend a piece of code that includes references to objects whose schema is not specified (and they do not already know). Yes, you can do SELECT statements on system tables to attempt to find the object, but, in the case where it is missing entirely, this becomes even more complicated--and rapidly turns into a real mess: If I find no references to its creation in the Evergreen scripts, I have to know to look in the Pg contrib modules (which may well be on a different machine entirely and in a directory I may have never had occasion to look at or even know about at all [as in the case where I did not do the installation of the Postgres part of the system). I would vote for not depending upon the schema search path (that is, do not use a schema search path, but rather always fully qualify object names). Ensuring that the code is as human-friendly as possible (including to newbies) seems to me to be a goal that is even more important in the open source world than it is in a closed-source project. This was brought home to me very strongly yesterday when I had an intern running the 2.0 to 2.1 upgrade script. Having encountered the error message from Postgres that said this: ERROR: function array_remove_item_by_value(text[], unknown) does not exist ... HINT: No function matches the given name and argument types. You might need to add explicit type casts. He followed the error message's misguided hint and went through the whole file adding explicit type casts to the second parameter (so it wouldn't be of unknown type). Well, that did not, in fact, fix the problem (and it took a long time to find each instance and add a type cast to the second parameter). Now, sure, a more experienced person would have realized that there were dozens of calls to array_remove_item_by_value before the point that Pg threw the error and so the hint related to the type issue was not the correct solution. However, if the search path setting had not been used at all in the creation of the script, the script's creator would have found the problem and resolved it (note also that the script's attempt to set the search path did not take). So, as a (so far) lone voice, I urge as standard practice, fully qualified object names in favor of use of search paths in SQL scripts. John __________ Information from ESET NOD32 Antivirus, version of virus signature database 6678 (20111202) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com