![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
As I have written more and more databases within a given PostgreSQL cluster, I have run into a bit of a problem with "version control" of sets of utility domains, types, functions and the like.
Every database created comes with a default schema named public. And anything created in that database is automatically part of the public schema. Although somethings are private to a given database, somethings are not.
Domains and functions in the public schema are available to other databases. If I redefine a function in my new database that has been used in other databases, I've just created a problem in the other databases. Not good.
I've decided, therefore, to create a schema for each database and create everything inside that schema. Then I can change the schema search path to check for the local schema FIRST, then public. Or not check public at all.
I think this works. I'm going to try it for a bit and see if it deals with the issue.
EDIT. It seems that the default search_path starts with "$user" which means that the default schema for everything has the same name as the current user (assuming the schema has been created). I'll just create it that way and make sure that I always connect to a particular database as the user that owns the database. Problem solved.
Every database created comes with a default schema named public. And anything created in that database is automatically part of the public schema. Although somethings are private to a given database, somethings are not.
Domains and functions in the public schema are available to other databases. If I redefine a function in my new database that has been used in other databases, I've just created a problem in the other databases. Not good.
I've decided, therefore, to create a schema for each database and create everything inside that schema. Then I can change the schema search path to check for the local schema FIRST, then public. Or not check public at all.
I think this works. I'm going to try it for a bit and see if it deals with the issue.
EDIT. It seems that the default search_path starts with "$user" which means that the default schema for everything has the same name as the current user (assuming the schema has been created). I'll just create it that way and make sure that I always connect to a particular database as the user that owns the database. Problem solved.
Tags: