CREATE SYNONYM ...

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

On 3/7/06, Tom Lane <tgl@sss.pgh.pa.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@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

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 3/7/06, Hans-Jürgen Schönig <postgres@cybertec.at> wrote:

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

The last time I worked on it was on 8.0 (I think), but it wouldn't take much
to get it up to speed on 8.2. It's actually very similar to yours so it
would probably be just as easy to start off with your patch. I'm open to
whatever but I'm really busy so I can only devote some time to it if it's
likely to be accepted.

-Jonah

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

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

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

On 3/7/06, Tom Lane <tgl@sss.pgh.pa.us> 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.)

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.

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.

Without the database link case, the functional reason for not using
search_path is surely reduced but it is in no way wholly eliminated either.
Some users don't have the ability to choose how vendors/developers write
their software and they can't easily just convert an entire application to
use search_path where they once had synonyms (especially if the application
is fairly sizable).

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

On 3/7/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:

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?

Not totally intended, but (unfortunately) used nonetheless.

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.

No, one could do fully qualified naming in Oracle; synonyms do have other
purposes outside of this single one listed.

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

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

On 3/7/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:

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.

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.

Also, there's *usually* not thousands of synonyms, usually tens or
hundreds. Again, they are mainly used to easily reference objects which
exist in other schemas or where there are duplicate object names across
schemas.

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

"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