Re: CREATE SYNONYM ...

hi tom,

first of all thank you for looking into this so quickly.

Tom Lane wrote:
> Michael Glaesemann <grzm(at)myrealbox(dot)com> writes:
>
>>On Mar 7, 2006, at 17:29 , Hans-Jürgen Schönig wrote:
>>
>>>this patch implements CREATE SYNONYM
>
>
>>Is this SQL spec or Oracle-specific?
>
>
> This is not in the spec.
>
> 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.

i have not seen too many using cross database link in oracle anyway.
some major installations i have heard of recently even stopped using
cross database transactions at all (too much overhead).
however, many people using oracle seriously (= beyond "select daddy from
parents") use synonyms to be compliant with older versions of some
software. especially for stored procedures this is widely used. people
use synonyms to "link" a function which is in one package into some
different namespace or so to a. avoid duplicate code or b. to avoid
cross-schema lookups and so forth.
to make it short: in our experience it is often used to solve problems
introduced in the past (which is a quite common scenario - crappy
applications are more widespread than good ones).

> 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.

good point. which other places do you have on the radar?
i can dig into this further. positive feedback is always highly appreciated.

> 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.

good point.
any other opionions here?

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

oracle documentation also states that using synonyms will add overhead.
people will know that and this should be part of the documentation.
however, i think - the performance impact when using this feature is
less painful for the customer than any kind of problem related to legacy
or duplicate code - people using features like that have to pay the
price for that.

> (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.)

i used a separate relation to be more flexible - we might also want to
support synonyms on tablespaces or whatever. we thought this would be
the better approach (also when thinking about dumps and lookups done by
the user)

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

i found out about pg_dump after posting ...
i have two babies ... - maybe sleep helps to prevent bugs ;).
documentation is on the way. i just wanted to post this code straight
away so that feedback can already be incooperated into this.

finally: we will do whatever is needed to get this patch approved. it is
sponsored work.

many thanks,

hans

--
Cybertec Geschwinde & Schönig GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at