Re: RFD: schemas and different kinds of Postgres objects

"Zeugswetter Andreas SB SD" <ZeugswetterA(at)spardat(dot)at> writes:
> When configured for historical behavior would need to:
> 1. have search path: temp, any, system
> 2. guard against duplicate table names across all schemas (except temp schema)

This would be a *whole* lot simpler if we forgot the notion of "any"
and made the search order look like

(temp, private, public, system)

where the public namespace is world-writable but the private per-user
ones are (typically at least) not.

It occurs to me that we can get both backward-compatible and SQL92
semantics with this same search path; the only thing that needs to
be different in the two cases is whether the default place to create
objects is your private schema or the public one. If you don't ever
use your private schema then it doesn't matter if it's on the search
path or not. I would still prefer that the search path be a settable
option, since a paranoid person might well wish to not have public in
his path at all ... but the default could be as-above.

> Or are you thinking about a per session behavior ?
> I would rather envision a per database behavior.
> Maybe the easy way out would be a "default creation schema" property for
> each user, that would default to the username. If you want everything in one
> schema simply alter the users.

I hadn't really gotten to the point of thinking about exactly what and
where the control knobs should be. I suspect you are right that we will
want the default behavior to be selectable on a per-user or per-database
basis, which seems to eliminate the option of using GUC (at least in its
current form). We could easily add a field to pg_shadow or pg_database
respectively to determine the default behavior. It'd be nice though if
the behavior could be changed after connection by a SET statement, which
would be lots easier if the setting were GUC-controlled. Peter, you see
any way to resolve that?

regards, tom lane