Thread: CREATE SYNONYM ...

this patch implements CREATE SYNONYM

syntax:

   CREATE SYNONYM [TABLE | INDEX | SEQUENCE | VIEW] synname ON orgname;

   CREATE SYNONYM FUNCTION synname ON funcname(arg, arg, ...);

   DROP SYNONYM [TABLE | INDEX | SEQUENCE | VIEW] synname;

   DROP SYNONYM FUNCTION synname(arg, arg, ...);


for details about synonyms see pg_synonym table.
The synonym is just like a unix hardlink.
Every user who has CREATE rights can create a synonym.

This feature is especially important to people who want to port from
Oracle to PostgreSQL (almost every customer who ports larger Oracle
applications will asked for it).

Documentation will be submitted this week.
The patch applies without error against 8.1.3.

    Many thanks and best regards,

        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

On Mar 7, 2006, at 17:29 , Hans-Jürgen Schönig wrote:

> this patch implements CREATE SYNONYM

<snip />

> This feature is especially important to people who want to port
> from Oracle to PostgreSQL (almost every customer who ports larger
> Oracle applications will asked for it).

Is this SQL spec or Oracle-specific?

Michael Glaesemann
grzm myrealbox com

this is actually what oracle is doing:

http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96540/statements_72a.htm

    best regards,

        hans




Michael Glaesemann wrote:
> On Mar 7, 2006, at 17:29 , Hans-Jürgen Schönig wrote:
>
>> this patch implements CREATE SYNONYM
>
>
> <snip />
>
>> This feature is especially important to people who want to port  from
>> Oracle to PostgreSQL (almost every customer who ports larger  Oracle
>> applications will asked for it).
>
>
> Is this SQL spec or Oracle-specific?
>
> Michael Glaesemann
> grzm myrealbox com
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend


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

Michael Glaesemann <grzm@myrealbox.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'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.

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.

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

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

            regards, tom lane

hi tom,

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



Tom Lane wrote:
> Michael Glaesemann <grzm@myrealbox.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

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


we should definitely work together.
what is the status of your patch?
maybe we can discuss this off list?

    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

On Tue, 7 Mar 2006, [ISO-8859-1] Hans-J�rgen Sch�nig wrote:

> > 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'd generally agree with Tom's assessment for this.  That seems to be the
most reasonable behavior to me.

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

I'd personally be more interested in what the impact is on people not
using synonyms. How free is any search for synonyms if you aren't using
the feature?

On Tue, 7 Mar 2006, Jonah H. Harris wrote:

> On 3/7/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> >
> > I'd personally be more interested in what the impact is on people not
> > using synonyms. How free is any search for synonyms if you aren't using
> > the feature?
>
>
> Unless synonym enablement were a configurable parameter (which wouldn't
> really make sense), the cost would be the same whether they're used or not
> during searching.

Right, but the response was that "using" synonyms incurred a cost and this
should be documented.  However, if there's a cost to people not using
synonyms there's a higher barrier to entry for the feature.

On Tue, Mar 07, 2006 at 12:39:55PM -0800, Stephan Szabo wrote:
>
> On Tue, 7 Mar 2006, Jonah H. Harris wrote:
>
> > On 3/7/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> > >
> > > I'd personally be more interested in what the impact is on people not
> > > using synonyms. How free is any search for synonyms if you aren't using
> > > the feature?
> >
> >
> > Unless synonym enablement were a configurable parameter (which wouldn't
> > really make sense), the cost would be the same whether they're used or not
> > during searching.
>
> Right, but the response was that "using" synonyms incurred a cost and this
> should be documented.  However, if there's a cost to people not using
> synonyms there's a higher barrier to entry for the feature.

Wouldn't the cost only be incurred if you searched for something in
pg_class that wasn't there, and therefor had to fall back to pg_synonym?
(At least I'd hope it was coded this way, but I didn't look at the
patch...)
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

On Tue, 7 Mar 2006, Jim C. Nasby wrote:

> On Tue, Mar 07, 2006 at 12:39:55PM -0800, Stephan Szabo wrote:
> >
> > On Tue, 7 Mar 2006, Jonah H. Harris wrote:
> >
> > > On 3/7/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> > > >
> > > > I'd personally be more interested in what the impact is on people not
> > > > using synonyms. How free is any search for synonyms if you aren't using
> > > > the feature?
> > >
> > >
> > > Unless synonym enablement were a configurable parameter (which wouldn't
> > > really make sense), the cost would be the same whether they're used or not
> > > during searching.
> >
> > Right, but the response was that "using" synonyms incurred a cost and this
> > should be documented.  However, if there's a cost to people not using
> > synonyms there's a higher barrier to entry for the feature.
>
> Wouldn't the cost only be incurred if you searched for something in
> pg_class that wasn't there, and therefor had to fall back to pg_synonym?

I think if synonyms were search path dependant that wouldn't be true since
you'd need to know if there was a synonym that shadowed another item.

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Tue, 7 Mar 2006, Jim C. Nasby wrote:
>> Wouldn't the cost only be incurred if you searched for something in
>> pg_class that wasn't there, and therefor had to fall back to pg_synonym?

> I think if synonyms were search path dependant that wouldn't be true since
> you'd need to know if there was a synonym that shadowed another item.

Right, and that's exactly why I complained.  Even if pg_synonym is
empty, it takes nonzero effort to find that out.

One reason I like the alternative of putting synonym entries into the
regular catalogs is that it eliminates the need for extra searches:
you'd make exactly the same searches as you did before.  Now, to the
extent that this requires making catalog entries longer, there'd be a
distributed overhead that might partially cancel that out --- but I
don't see any reason that the entries have to get longer for regular
tables.  The link field could be a nullable field at the end, and
the flag that it's a synonym would just be another relkind value.

I don't think the case for pg_proc synonyms has been made adequately at
all, so I'd personally just blow off that part of the proposal.  There's
no real cost to just making another copy of the proc.

(Actually, I don't think the case for table synonyms has been made
adequately either; "Oracle has it" is *not* enough reason to take on
another feature that we'll have to maintain forever, especially given
that we're being told that one of the major use-cases for synonyms
isn't going to be supported.  AFAICS this patch does nothing you
couldn't do much better with a quick search-and-replace over your
application code.  In short, I remain unsold.)

            regards, tom lane

Tom Lane wrote:

> (Actually, I don't think the case for table synonyms has been made
> adequately either; "Oracle has it" is *not* enough reason to take on
> another feature that we'll have to maintain forever, especially given
> that we're being told that one of the major use-cases for synonyms
> isn't going to be supported.  AFAICS this patch does nothing you
> couldn't do much better with a quick search-and-replace over your
> application code.  In short, I remain unsold.)

What I don't really understand is what part of this cannot be achieved
by changing the search_path.  The only case I can think of is when you
have tables A and B in schemas R and S, but you want to use R.A and S.B.
So there's no way to change search_path for this.  But is this really
the intended use case?

I wonder whether synonyms were introduced in Oracle because of that idea
of theirs that each user has its own schema, and can access that schema
only; so to use a table in another schema you need to create a synonym.
We don't have that limitation so we don't need that usage either.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Jonah H. Harris wrote:

> Now, one may argue that it's incorrect/bad application-design to not use
> fully qualified names, however, there are cases (especially in VERY large
> database applications) where you do not want to use fully qualified naming.
> In PostgreSQL, the alternative to synonyms is to have a monstrous search
> path $user, public, HR, AP, AR, GL, FA, COMMON...  Not that we have Oracle
> Applications running on PostgreSQL, but 11i has something like 130+? schemas
> which would be pretty nasty and semi-unprofessional as a search_path rather
> than as something defined similar to synonyms.

Well, if you don't want to have a monstrous search path with 130+
schemas, then you'll have a monstrous amount of synonyms.  Given that
schemas are a way to separate the object namespace, it seems more
sensible to me to propagate the user of reasonable search paths than the
use of hundreds (thousands?) of synonyms.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

"Jonah H. Harris" <jonah.harris@gmail.com> writes:
> Like I said, sometimes the user doesn't have a choice.   Sure, it's easy to
> tell someone that has a 300-line PHP application to fix their code, but I've
> worked with people who have hundreds of thousands of lines of code and they
> don't just say, "gee, let's just search-and-replace everything!"; that's a
> testing nightmare.

To be blunt, those people aren't going to be moving to Postgres anyhow.
If the notion of fixing this issue daunts them, they are not going to be
willing to deal with the other incompatibilities between Oracle and PG.

And we are *not* buying into the notion of becoming a bug-compatible
Oracle clone.

(If EnterpriseDB wants to try to do that, fine; they'll be earning their
money the old-fashioned way...)

            regards, tom lane

Stephan Szabo wrote:
> On Tue, 7 Mar 2006, Jonah H. Harris wrote:
>
>
>>On 3/7/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
>>
>>>I'd personally be more interested in what the impact is on people not
>>>using synonyms. How free is any search for synonyms if you aren't using
>>>the feature?
>>
>>
>>Unless synonym enablement were a configurable parameter (which wouldn't
>>really make sense), the cost would be the same whether they're used or not
>>during searching.
>
>
> Right, but the response was that "using" synonyms incurred a cost and this
> should be documented.  However, if there's a cost to people not using
> synonyms there's a higher barrier to entry for the feature.
>


the costs will only be added if the "real table" is not found.
therefore there is no impact on "normal" users.

again, the most important benefit is not 0.001% more speed but the
possibility to port from other databases easier and to treat legacy
problems.

here at cybertec we are facing more and more problems with legacy
databases and porting "crap" every day.

    many thanks and best regards,

        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

> One reason I like the alternative of putting synonym entries into the
> regular catalogs is that it eliminates the need for extra searches:
> you'd make exactly the same searches as you did before.  Now, to the
> extent that this requires making catalog entries longer, there'd be a
> distributed overhead that might partially cancel that out --- but I
> don't see any reason that the entries have to get longer for regular
> tables.  The link field could be a nullable field at the end, and
> the flag that it's a synonym would just be another relkind value.


i don't think this would be extensible in the way the current code is.


> I don't think the case for pg_proc synonyms has been made adequately at
> all, so I'd personally just blow off that part of the proposal.  There's
> no real cost to just making another copy of the proc.
>
>

<snip>

AFAICS this patch does nothing you
> couldn't do much better with a quick search-and-replace over your
> application code.  In short, I remain unsold.)

in this case you are absolutely wrong - this is far from reality.
assume somebody started off with a DB2 based application. the program
was good. then it was ported to oracle. meanwhile 300 features were
changed, adapted, replaced, 5 programmers died and 20 left the company.
finally some other things were changed -> the internal structures of
stored procedures ended up as "don't touch me".
"sed s/ /gi ..." will be the key to introducing a significant amount of
unpredictable problems - in business applications nobody will even
CONSIDER touching something like that. i am not saying that cleaning up
is a good thing - in some cases it is simply not doable because the guy
who wrote the code died 5 years ago (this is a real story by the way).

i have seen databases where we had to define DELETE rules DO INSTEAD
NOTHING because nobody knew where a bad delete actually came from - THIS
is the kind of problem we are talking about.
to me using an alternative name is definitely not something which is bad
at all.

the fact that oracle supports something is definitely not an argument.
however, oracle invented this feature for a situation like the one i
described above. the problem is: this is a quite common scenario.

assume we would fix:
    - search_path issue which was brought up
    - pg_dump
    - the docs

would there be a serious chance to get that approved?

    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

> What I don't really understand is what part of this cannot be achieved
> by changing the search_path.  The only case I can think of is when you
> have tables A and B in schemas R and S, but you want to use R.A and S.B.
> So there's no way to change search_path for this.  But is this really
> the intended use case?

yes, this is a very practical case ...


> I wonder whether synonyms were introduced in Oracle because of that idea
> of theirs that each user has its own schema, and can access that schema
> only; so to use a table in another schema you need to create a synonym.
> We don't have that limitation so we don't need that usage either.


i am sure this was a reason but not the only one.
some other reason could be (again, bad but widespread): somebody defined
a bad data structure where everything is in separate tables (tom's cars
are in table A, bruce's cars are in table B). somebody finally finds out
that this was a bad idea (3mio lines of code were built on top of this
crap) and that those tables should be combined. a synonym will help.
just think of broken applications such as SAP - everything is in a
separate table (maybe they have 100000000000000 which only stored
desciptions), if you want to cleanup a synonym is less error prone than
'sed -e ...'.

if synonyms are a broken concept then the same would apply for softlinks
and hardlinks supported by filesystem - still people like soft/hardlinks
and they are widely adopted because they are useful. of course, you can
live without file systems links if you can afford changing the path
after every line of shell code.

    best regards,

        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

Alvaro Herrera wrote:
> Jonah H. Harris wrote:
>
>
>>Now, one may argue that it's incorrect/bad application-design to not use
>>fully qualified names, however, there are cases (especially in VERY large
>>database applications) where you do not want to use fully qualified naming.
>>In PostgreSQL, the alternative to synonyms is to have a monstrous search
>>path $user, public, HR, AP, AR, GL, FA, COMMON...  Not that we have Oracle
>>Applications running on PostgreSQL, but 11i has something like 130+? schemas
>>which would be pretty nasty and semi-unprofessional as a search_path rather
>>than as something defined similar to synonyms.
>
>
> Well, if you don't want to have a monstrous search path with 130+
> schemas, then you'll have a monstrous amount of synonyms.  Given that
> schemas are a way to separate the object namespace, it seems more
> sensible to me to propagate the user of reasonable search paths than the
> use of hundreds (thousands?) of synonyms.
>


synonyms are easier to handle for an application - adding the search
path to an existing application can be too intrusive and error prone.

    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

> I agree with this to some extent.
>
> The main use case, aside from database link objects, is really for
> generally large applications such as a large ERP system.  Most ERP
> systems have a general or foundation-like schema where common objects
> lie and each module is separated using schemas.

absolutely - just like jonah stated before; it is not about 100 lines
PHP code.
recently PostgreSQL was more and more adopted for "enterprise
applications" (whatever this might be).


> As an example, you would have HR, AP, AR, GL, FA, COMMON, ... schemas
> which encapsulate the functionality of their respective modules whether
> it be procedures, functions, views, tables, etc.  For each module to be
> able to access, for example, the HR.EMPLOYEE table, they generally refer
> to just EMPLOYEE which is a synonym to HR.EMPLOYEE.
>
> Now, one may argue that it's incorrect/bad application-design to not use
> fully qualified names, however, there are cases (especially in VERY
> large database applications) where you do not want to use fully
> qualified naming.  In PostgreSQL, the alternative to synonyms is to have
> a monstrous search path $user, public, HR, AP, AR, GL, FA, COMMON...
> Not that we have Oracle Applications running on PostgreSQL, but 11i has
> something like 130+? schemas which would be pretty nasty and
> semi-unprofessional as a search_path rather than as something defined
> similar to synonyms.  Another consideration is poor application design
> which uses the same named table in one schema which acts differently
> than the same named table in another schema... synonyms resolve this
> issue which could be problematic if not impossible to solve using
> search_path alone.

nothing to add - this is how things work in reality ...

    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

Tom Lane wrote:
> "Jonah H. Harris" <jonah.harris@gmail.com> writes:
>
>>Like I said, sometimes the user doesn't have a choice.   Sure, it's easy to
>>tell someone that has a 300-line PHP application to fix their code, but I've
>>worked with people who have hundreds of thousands of lines of code and they
>>don't just say, "gee, let's just search-and-replace everything!"; that's a
>>testing nightmare.
>
>
> To be blunt, those people aren't going to be moving to Postgres anyhow.
> If the notion of fixing this issue daunts them, they are not going to be
> willing to deal with the other incompatibilities between Oracle and PG.


i wouldn't say so. assume running 100 oracle databases. some day some
financial guy will come around and tell you that you have to migrate
because he wants to save money.
these guys usually won't dicuss how to port - they simply tell you that
things have to be ported ...


> And we are *not* buying into the notion of becoming a bug-compatible
> Oracle clone.

absolutely - i think we all agree here. the reason why PostgreSQL is so
strong nowadays is that it has been designed and implemented nicely.

in Oracle even "select 1 + 1" is a problem for no reason ;)


> (If EnterpriseDB wants to try to do that, fine; they'll be earning their
> money the old-fashioned way...)

i think nobody wants to work on crappy applications or even port them.
in most cases it is simply a demand :(.

    best regards,

        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

On Wed, 8 Mar 2006, [ISO-8859-1] Hans-Jürgen Schönig wrote:

> Stephan Szabo wrote:
> > On Tue, 7 Mar 2006, Jonah H. Harris wrote:
> >
> >
> >>On 3/7/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> >>
> >>>I'd personally be more interested in what the impact is on people not
> >>>using synonyms. How free is any search for synonyms if you aren't using
> >>>the feature?
> >>
> >>
> >>Unless synonym enablement were a configurable parameter (which wouldn't
> >>really make sense), the cost would be the same whether they're used or not
> >>during searching.
> >
> >
> > Right, but the response was that "using" synonyms incurred a cost and this
> > should be documented.  However, if there's a cost to people not using
> > synonyms there's a higher barrier to entry for the feature.
> >
>
>
> the costs will only be added if the "real table" is not found.
> therefore there is no impact on "normal" users.

Doesn't that pretty much go against the (I thought) outstanding behavioral
question of whether the synonyms are scoped and obey search path?  If they
do, I don't see how the above rule can hold since finding the "real table"
is insufficient to know if there's an earlier synonym.

On Tue, 2006-03-07 at 17:14 -0500, Tom Lane wrote:
> (Actually, I don't think the case for table synonyms has been made
> adequately either; "Oracle has it" is *not* enough reason to take on
> another feature that we'll have to maintain forever, especially given
> that we're being told that one of the major use-cases for synonyms
> isn't going to be supported.

I'm inclined to agree. The points raised about the difficulties of
managing large numbers of schemas are legitimate, but I don't see that
synonyms are a very effective solution. If we're going to make it less
painful to work on applications with many tens of schemas, that's a
worthwhile project, but I think we should take a fresh look at the
problem rather than just blindly copying a construct from Oracle.

BTW, AFAICS synonyms for tables and views can be approximated by views:
you pay a small runtime hit to expand the view definition, but that's
fairly cheap for a simple view. Synonyms for functions can be
approximated by shell functions defined in SQL -- those can even be
inlined, reducing the amount of overhead. It's not as easy to define
synonyms for sequences, but that is hardly justification for the
feature.

-Neil

* Neil Conway (neilc@samurai.com) wrote:
> BTW, AFAICS synonyms for tables and views can be approximated by views:
> you pay a small runtime hit to expand the view definition, but that's
> fairly cheap for a simple view. Synonyms for functions can be
> approximated by shell functions defined in SQL -- those can even be
> inlined, reducing the amount of overhead. It's not as easy to define
> synonyms for sequences, but that is hardly justification for the
> feature.

If all you were doing with the view was reading from it then sure...
Creating the rules for all the views you want to make them updatable
wouldn't be fun.  That's actually the one thing I like about synonyms
over create view a as select * from b.a;

We've got a number of synonyms in our Oracle databases (no
cross-database ones or anything like that...) and I'd like to get rid of
them but the folks using them aren't too keen on that for various
reasons...  It'd be nice to be able to support them in Postgres without
having to go through alot of work.  Tom's suggestion for just an
additional catalog entry for them would be exactly what I'd need and
seems pretty straight-forward and simple to me.

Just my 2c.

    Thanks,

        Stephen

On Wed, 8 Mar 2006, Jonah H. Harris wrote:

> On 3/8/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> >
> > Doesn't that pretty much go against the (I thought) outstanding behavioral
> > question of whether the synonyms are scoped and obey search path?  If they
> > do, I don't see how the above rule can hold since finding the "real table"
> > is insufficient to know if there's an earlier synonym.
> >
>
> There is a cost for synonyms no matter how you look at it.

Yes, however there are two slightly separate discussions going on and I
think you're taking them as a single discussion. One is about the feature
in general. One is about this patch in particular and the approach taken
in it. I'm mostly talking about the latter and specifically, what are the
costs of this particular way of implementing synonyms for people not using
the feature.

Even if we want a feature, there's a particular patch (or particular
patches) that implement the feature that come for review. If one thinks
the approach taken by the patch gives costs that are greater than our
desire for the feature, then one argues against it.  That's irrespective
of whether that person believes in the feature as a whole.  If you notice,
AFAICS I haven't said, "we shouldn't implement synonyms" or "synonyms are
unnecessary", but instead effectively "what are the costs of implementing
synonyms" and "that analysis of the cost for this approach seems wrong".

> Assume your user has it's own schema, that there is a synonym in public for
> EMPLOYEE which pointed to HR.EMPLOYEE, and your search path is
> $user,public.  If you do a SELECT * FROM EMPLOYEE, the search order is still
> the same as it is in PostgreSQL now, there's no EMPLOYEE table in the $user
> schema, so when it gets to searching public, it finds the synonym.  The only
> alternative in this scenario is to create the EMPLOYEE table in public
> (which is pretty stupid in most cases), or to set the search path to
> $user,public,hr.  Again, this doesn't cover the "same-named tables in
> multiple schemas" argument, but it does illustrate that PostgreSQL's
> namespace scoping remains the same.

IMHO, that's insufficient analysis, precisely for the reason it doesn't
cover multiple schemas with the same objct.

If your search path is A,B and there is a B.EMPLOYEE table and an
A.EMPLOYEE synonym to HR.EMPLOYEE, which table does select * from EMPLOYEE
read?  If the behavior is find the table through the whole path, then find
the synonym, it's B.EMPLOYEE and there's only cost to people who aren't
using the feature for error cases. If the behavior is find either table or
synonym in each search path entry in order (thus HR.EMPLOYEE) and finding
synonyms requires a separate search of the catalogs, then it seems like
everyone pays whether or not they are using the feature.

So far, there have been statements made that the cost to people not using
the feature is minimal in this approach because the extra search only
happens if the table isn't found. However, I still am not seeing how that
approach gives the second behavior (assuming that's what we want). To
discuss how to implement a feature we need at least an understanding of
what the behavior an approach implements and the costs that approach
incurs.

On Wed, 2006-03-08 at 08:16 -0500, Stephen Frost wrote:
> If all you were doing with the view was reading from it then sure...
> Creating the rules for all the views you want to make them updatable
> wouldn't be fun.

Well, updateable views are on the TODO list: expending our finite
development resources implementing those would add plainly useful
functionality and improve our conformance with the SQL standard, neither
of which can be said about synonyms.

> It'd be nice to be able to support them in Postgres without
> having to go through alot of work.

I'm still unconvinced that this feature would be sufficiently useful to
justify the maintenance burden, in addition to the added complexity:
even if it is implemented in a way that imposes minimal *runtime*
overhead, new features add complexity: introducing a bunch of new DDL
commands and a new concept ("synonyms") makes the system more difficult
for users to understand.

-Neil

Neil Conway wrote:

>
>
> I'm still unconvinced that this feature would be sufficiently useful to
> justify the maintenance burden, in addition to the added complexity:
> even if it is implemented in a way that imposes minimal *runtime*
> overhead, new features add complexity: introducing a bunch of new DDL
> commands and a new concept ("synonyms") makes the system more difficult
> for users to understand.

Synonyms appear to me a little like domains. I like them to abstract
from proprietary data types. Similar, leightweight synonyms (pg_class
entries) allow some abstraction if needed, without using rules. I don't
think that synonyms are more difficult to understand than domains.

Regards,
Andreas

On Wed, 8 Mar 2006, Jonah H. Harris wrote:

> On 3/8/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> >
> > Yes, however there are two slightly separate discussions going on and I
> > think you're taking them as a single discussion.
>
>
> I agree that there are two discussions happening in this thread, but I don't
> think anyone has agreed at all that this patch as it is would be acceptable
> for various reasons.  There are a couple things that Hans and I will discuss
> about the patch assuming we decide this is a feature that would be nice for
> PostgreSQL.

What feature though?  Part of the definition of a feature like synonym has
to nail down things like how it interacts with search path. The message I
was responding to was talking about the patch and seeming to say that
there wasn't a cost for non-users because the search was done iff a
candidate object wasn't found. IMHO, this is a different feature than a
synonym feature for which each search path entry is checked so that
synonyms in earlier path entries shadow later concrete objects. We
probably don't want both features even if we want either, but they're
really different features.

> With synonyms, the search path for Joe would remain $user, public and one
> could easily do
> CREATE SYNONYM public.employee FOR hr.employee;
> CREATE SYNONYM public.commissions FOR crm.commissions;

I would say that that's a really bad choice, and Joe should have his
synonyms somewhere other than public so as not to pollute other people's
default search path with his particular needs that may not be the same as
someone else's.  What does Jane do now when she needs the opposite set and
why is Joe's choice more relevant than Jane's?

This is not a negative effect of synonyms, merely this use.

> I guess synonym searching could be done iff no object were found in the
> current search.  I don't know why I thought it would be just as costly
> (perhaps too much Sam Adams).  The worst-case scenario would be an
> additional search only if an object weren't found in a catalog search,
> basically this would be the cost of using synonyms and wouldn't affect
> performance for everyone else.  Oracle does have a small cost only when
> using synonyms as well.

Yeah, that just seems less consistent with the rest of the way schema
searches work right now for other objects.

On Wed, 8 Mar 2006, Jonah H. Harris wrote:

> On 3/8/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> >
> > What feature though?  Part of the definition of a feature like synonym has
> > to nail down things like how it interacts with search path
>
>
> I've said how it interacts with the search path (with Oracle) several times
> and illustrated it in the last one, let's not go through this again.

Argh.

You responded with a statement about synonyms having cost in response to a
message of mine in response to a message from postgres@cybertec.at which
said "the costs will only be added if the "real table" is not found.
therefore there is no impact on "normal" users." Your idea and that of the
person who submitted this patch thus seem to be slightly different. Maybe
I'm confused, but to me this seems to show incomplete acceptance of this
point when the patch submitter and one of the vocal proponents have
different models in mind. I'd rather the feature follows the model you
described (although I may have greater concerns of the cost) because it
seems consistent with other lookups.

Hans-Jürgen Schönig wrote:
> This feature is especially important to people who want to port from
> Oracle to PostgreSQL (almost every customer who ports larger Oracle
> applications will asked for it).

Does any SQL-like database system other than Oracle have this feature?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Jonah H. Harris wrote:
> On 3/13/06, *Peter Eisentraut* <peter_e@gmx.net
> <mailto:peter_e@gmx.net>> wrote:
>
>     Does any SQL-like database system other than Oracle have this feature?
>
>
> I know that SQL Server, DB2, SAP DB/MAX DB, and Mimer have it.

Introduced in MSSQL2005:
http://msdn2.microsoft.com/en-us/library/ms177544.aspx

Regards,
Andreas

Peter Eisentraut wrote:
> Hans-Jürgen Schönig wrote:
>
>>This feature is especially important to people who want to port from
>>Oracle to PostgreSQL (almost every customer who ports larger Oracle
>>applications will asked for it).
>
>
> Does any SQL-like database system other than Oracle have this feature?
>


hi peter,

the most popular are: db2, max db, informix, ms sql.
in other words: all databases which are widely used and widely accepted.
there is no big database vendor who does not support it (taking into
consideration that mysql is not a database).

    best regards,

        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

=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> writes:
> Peter Eisentraut wrote:
>> Does any SQL-like database system other than Oracle have this feature?

> the most popular are: db2, max db, informix, ms sql.
> in other words: all databases which are widely used and widely accepted.

That argument only holds water if they all support it with the same
semantics, a fact not in evidence.  I'm not impressed by whether they
all have things they call synonyms, unless they all work alike.  What
are their approaches to permissions?  What happens if the referenced
object is dropped or modified?  Can you make a synonym to something that
doesn't exist yet?  What sorts of objects can have synonyms?

            regards, tom lane

Tom Lane wrote:

>=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> writes:
>
>
>>Peter Eisentraut wrote:
>>
>>
>>>Does any SQL-like database system other than Oracle have this feature?
>>>
>>>
>
>
>
>>the most popular are: db2, max db, informix, ms sql.
>>in other words: all databases which are widely used and widely accepted.
>>
>>
>
>That argument only holds water if they all support it with the same
>semantics, a fact not in evidence.  I'm not impressed by whether they
>all have things they call synonyms, unless they all work alike.  What
>are their approaches to permissions?  What happens if the referenced
>object is dropped or modified?  Can you make a synonym to something that
>doesn't exist yet?  What sorts of objects can have synonyms?
>
>
>
>

"all" might also be a bit of an overstatement. AFAIK Sybase at least
doesn't have these.

Even if they don't all have precisely the same semantics, though, is
there an objection in principle to providing synonyms?

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:
> Even if they don't all have precisely the same semantics, though, is
> there an objection in principle to providing synonyms?

The point I was trying to bring out is that they aren't standard,
which amounts to an objection in principle.  I'd at least like to see
some effort made to demonstrate that we are adopting semantics that
match a majority of other DBs, rather than inventing something in a
vacuum which is what appears to be happening in this thread.

            regards, tom lane

Tom Lane wrote:

>Andrew Dunstan <andrew@dunslane.net> writes:
>
>
>>Even if they don't all have precisely the same semantics, though, is
>>there an objection in principle to providing synonyms?
>>
>>
>
>The point I was trying to bring out is that they aren't standard,
>which amounts to an objection in principle.  I'd at least like to see
>some effort made to demonstrate that we are adopting semantics that
>match a majority of other DBs, rather than inventing something in a
>vacuum which is what appears to be happening in this thread.
>
>
>
I agree.

Maybe one of the proponents could put together a comparison matrix of
how this is done in each of the databases previously mentioned.

cheers

andrew