On Wed, Jun 2, 2021, at 18:36, Marko Tiikkaja wrote:
If a database object is to be accessed unqualified by all users, isn't the 'public' schema a perfect fit for it? How will it be helpful to create different database objects in different schemas, if also adding all such schemas to the search_path so they can be accessed unqualified? In such a scenario you risk unintentionally creating conflicting objects, and whatever schema happened to be first in the search_path will be resolved. Seems insecure and messy to me.
Heh. This is actually exactly what I wanted to do.
The use case is: version upgrades. I want to be able to have a search_path of something like 'pg_catalog, compat, public'. That way we can provide compatibility versions of newer functions in the "compat" schema, which get taken over by pg_catalog when running on a newer version. That way all the compatibility crap is clearly separated from the stuff that should be in "public".
That's a neat trick, probably the best solution in a really old PostgreSQL version, before we had extensions.
But if running a recent PostgreSQL version, with support for extensions, I think an even cleaner solution
would be to package such compatibility versions in a "compat" extension, that would just install them into the public schema.
Then, when upgrading, you would just not install the compat extension.
And if you wonder what functions in public come from the compat extension, you can use use \dx+.
/Joel