Re: RFD: schemas and different kinds of Postgres objects - Mailing list pgsql-hackers

Tom Lane wrote:
> 
> Fernando Nasser <fnasser@redhat.com> writes:
> > In the historical mode:  look into schema B (=> not found), look into
> > ANY schema (finds it in A).  Works as it is today.
> 
> No, it doesn't work the same as today, because in that implementation
> both A and B can create the same tablename without complaint.

I agree that we won't be able to catch this as an error unless we turn 
another switch that requires unique names (there goes one of the
advantages
of having schemas, but there is always the option of leaving it on).

In this case it would be more close to the current behavior but what is
left of the SQL-Schemas will be more of a syntactic sugar (although it
can
be still used by the DBA to better organize the grant of privileges).

Anyway, it would be a DBA option to live with not detecting duplicate
names. And, I hope all our tools, graphical or not, will make it clear
what
is the schema things are defined into, so it would not be difficult to 
figure out what is going wrong if something goes wrong (and we can also
print the relation oid on messages).  

>  It then
> becomes very unclear which instance other people will get (unless your
> "any" placeholder somehow implies a search order).
> 

If someone is just using the current mode, there shouldn't be (all names
are
database-unique).

The only case where this situation can happen is if someone is trying
to use schemas and the historical non-schema organization in the same
database, right?  Can we make the search order per database?)

One possibility is to state that this is not recommended (one should 
organize things as schemas or not at all in a database) and say that 
the search order, besides the current AuthId, is unspecified (random). 

Another possibility is to allow only one object with that name in the
"any" space.  If someone means an object that was defined on a schema,
he/she can qualify the name with the schema (good practice).  The only
case where this is not possible is the legacy case, where there is 
exactly one object with that name anyway.

I prefer this second solution.

> The idea of being able to put an "any" placeholder into the search list
> is an interesting one, though.  If we can resolve the ambiguity problem
> it might be a useful feature.
> 

See above.

> I am a little troubled by the idea of placing "any" before the system
> schema (what if JRandomLuser creates a table named "pg_class"?) but it
> might be workable at the tail end of the path.
> 

Yes, I thought of that as I was typing, but it was not the important
point at that time.  You're right, should go at the end.

-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9