hi tom,

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

Tom Lane wrote:
Michael Glaesemann <[EMAIL PROTECTED]> 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

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,


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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not

Reply via email to