Re: CREATE SYNONYM ...

On 3/7/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> I'm inclined to reject this patch on the grounds that it doesn't do
> what Oracle does and does not look like it could be extended to do what
> Oracle does. My understanding is that what Oracle people mostly use
> synonyms for is to provide cross-database access --- and this can't do
> that. I'm not in favor of providing syntax compatibility if we don't
> have functional compatibility; I think that isn't doing anyone any
> favors. And if the behavior does get used, then we'd have a backwards
> compatibility problem if anyone ever wants to do it right.

People in Oracle use synonyms for two reasons... either as a synonym to an
object over a database link or to an object in another schema. I have an
almost completed patch similar to this one that does act as Oracle does
(albeit limited for database links because we don't support them as Oracle
does such as object(at)remotedb).

I'm also quite dubious that this would work properly, because it hooks
> into table and function lookup in only one place respectively. It's
> hard to believe that only one of the many lookups for tables and
> functions needs to be changed.

I did pretty much the same thing for candidate lookups and haven't found a
problem yet, but that's not to say there isn't one.

The semantics of namespace search seem wrong; I would think that a
> synonym in schema A should mask a table in schema B if A precedes B
> on the search path, but this doesn't work that way.

This is correct, A should always precede B in namespace lookups.

I'm also not very happy about adding an additional catalog search to
> function and table lookup, which are already quite expensive enough.

(The last two objections might both be addressed by forgetting the
> notion of a separate catalog and instead making synonyms be alternative
> kinds of entries in pg_class and pg_proc. However, that does nothing to
> help with the cross-database problem, and might indeed hinder it.)

Don't know how to really get around the additional lookup without extending
pg_class and pg_proc. Even so, this would still add overhead to catalog
searches.

Just for the record, this is lacking pg_dump support as well as
> documentation.

True.

I'd be glad to submit my patch and/or cleanup this one if its something the
community would be willing to accept.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324