Thread: Do we want SYNONYMS?

Hey -general,

Command Prompt is currently considering writing a patch to provide
synonyms to PostgreSQL. Is this something the community is interested
in? Do we have use cases for it? MSSQL, DB2 and Oracle support them.

Reference thread:

http://archives.postgresql.org/pgsql-hackers/2010-11/msg02043.php

Joshua D. Drake
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On 12/6/2010 1:31 PM, Joshua D. Drake wrote:
> Hey -general,
>
> Command Prompt is currently considering writing a patch to provide
> synonyms to PostgreSQL. Is this something the community is interested
> in? Do we have use cases for it? MSSQL, DB2 and Oracle support them.
>
> Reference thread:
>
> http://archives.postgresql.org/pgsql-hackers/2010-11/msg02043.php
>
> Joshua D. Drake

I dont understand the need for it.  Dont view's do the exact same thing
(plus even more)?  What does a synonym offer that a view does not?

And perusing the thread, I see it might cause all name and operator
lookups to be slower?  (I didnt read all of it though)

I'm gonna have to go:  -1

-Andy

On Mon, 2010-12-06 at 13:57 -0600, Andy Colson wrote:
> On 12/6/2010 1:31 PM, Joshua D. Drake wrote:
> > Hey -general,
> >
> > Command Prompt is currently considering writing a patch to provide
> > synonyms to PostgreSQL. Is this something the community is interested
> > in? Do we have use cases for it? MSSQL, DB2 and Oracle support them.
> >
> > Reference thread:
> >
> > http://archives.postgresql.org/pgsql-hackers/2010-11/msg02043.php
> >
> > Joshua D. Drake
>
> I dont understand the need for it.  Dont view's do the exact same thing
> (plus even more)?  What does a synonym offer that a view does not?

SYNONYMS work for things that aren't a table.

JD
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

Synonyms would be very helpful to us. We just migrated our application
from Oracle, where we used synonyms to toggle between between two
schemas:  one schema could be loaded with new data, while synonyms
pointed the web application to the live schema. Once the data load was
done, we switched the web app's synonyms to switch to the new live schema.

We've hacked a solution in Postgres using search paths, but search paths
don't work as well as synonyms when the target objects are not in the
same schema ("database" in PG-ese, I think).

/mcr

"Joshua D. Drake" <jd@commandprompt.com> writes:
> On Mon, 2010-12-06 at 13:57 -0600, Andy Colson wrote:
>> I dont understand the need for it.  Dont view's do the exact same thing
>> (plus even more)?  What does a synonym offer that a view does not?

> SYNONYMS work for things that aren't a table.

The idea of synonyms for non-table things was pretty much rejected
already on the -hackers thread.

            regards, tom lane

On Mon, 2010-12-06 at 15:27 -0500, Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
> > On Mon, 2010-12-06 at 13:57 -0600, Andy Colson wrote:
> >> I dont understand the need for it.  Dont view's do the exact same thing
> >> (plus even more)?  What does a synonym offer that a view does not?
>
> > SYNONYMS work for things that aren't a table.
>
> The idea of synonyms for non-table things was pretty much rejected
> already on the -hackers thread.

Well I was referring to basically anything that is stored in pg_class
(not operators or functions).

Joshua D. Drake

>
>             regards, tom lane
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

>> SYNONYMS work for things that aren't a table.
>
> The idea of synonyms for non-table things was pretty much rejected
> already on the -hackers thread.

Again, in Oracle, we found synonyms on stored procedures and functions
as well as tables to be key.

/m

> What is "schema" in this context?

Oracle "schema" == Postgres "database":  a collection of objects
(tables, functions, triggers, views, etc) owned by a user.

"Joshua D. Drake" <jd@commandprompt.com> writes:
> On Mon, 2010-12-06 at 15:27 -0500, Tom Lane wrote:
>> "Joshua D. Drake" <jd@commandprompt.com> writes:
> On Mon, 2010-12-06 at 13:57 -0600, Andy Colson wrote:
>>>> I dont understand the need for it.  Dont view's do the exact same thing
>>>> (plus even more)?  What does a synonym offer that a view does not?

>>> SYNONYMS work for things that aren't a table.

>> The idea of synonyms for non-table things was pretty much rejected
>> already on the -hackers thread.

> Well I was referring to basically anything that is stored in pg_class
> (not operators or functions).

Well, that would more or less boil down to "you can use synonyms for
sequences" (there not being much else in pg_class that users have need
to refer to).  Plus "you can use synonyms for updates not just reading",
which views don't support without writing tedious and fragile rules.
Of course we might fix the latter problem someday, but progress in that
direction seems to be slow.

So I don't say that pg_class-only synonyms would be useless.  But let's
be sure people understand what they would do or not do before soliciting
opinions on how useful they are.

            regards, tom lane

Michael C Rosenstein <mcr@mdibl.org> writes:
>> What is "schema" in this context?

> Oracle "schema" == Postgres "database":  a collection of objects
> (tables, functions, triggers, views, etc) owned by a user.

That seems like a pretty unlikely equivalence.  What I'm afraid
you are really saying you want is cross-database synonyms (ie links
to objects in remote databases).  Which I'm pretty sure is not what
JD is offering to implement, though I think it is possible to do
in Oracle.

            regards, tom lane

I think aliasing non-table/view was mixed by Tom, but just as FYI, aliasing column names would be very helpful in my
apps.Aliasing "last_name", "lastname", "surname" together in a people table for example.  We have many design sites
thathave identical data concepts but with different names for the same thing.  It would be nice to just equate these
namesin the DB instead of in perl wrappers around sql calls.
 
 

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andy Colson
Sent: Monday, December 06, 2010 2:57 PM
To: jd@commandprompt.com
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Do we want SYNONYMS?

On 12/6/2010 1:31 PM, Joshua D. Drake wrote:
> Hey -general,
>
> Command Prompt is currently considering writing a patch to provide
> synonyms to PostgreSQL. Is this something the community is interested
> in? Do we have use cases for it? MSSQL, DB2 and Oracle support them.
>
> Reference thread:
>
> http://archives.postgresql.org/pgsql-hackers/2010-11/msg02043.php
>
> Joshua D. Drake

I dont understand the need for it.  Dont view's do the exact same thing 
(plus even more)?  What does a synonym offer that a view does not?

And perusing the thread, I see it might cause all name and operator 
lookups to be slower?  (I didnt read all of it though)

I'm gonna have to go:  -1

-Andy

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/6/10 4:09 PM, Tom Lane wrote:
> Michael C Rosenstein<mcr@mdibl.org>  writes:
>>> What is "schema" in this context?
>
>> Oracle "schema" == Postgres "database":  a collection of objects
>> (tables, functions, triggers, views, etc) owned by a user.
>
> That seems like a pretty unlikely equivalence.  What I'm afraid
> you are really saying you want is cross-database synonyms (ie links
> to objects in remote databases).  Which I'm pretty sure is not what
> JD is offering to implement, though I think it is possible to do
> in Oracle.

Nope, not talking about remote database links, but merely links to
different databases in the same process on the same host.

For example webAppUser sometimes needs to access the
public1.get_customer_name() function, the public1.order table and the
edit.account table. After a new data load of the public2 database, the
webAppUser would need to access the public2.get_customer_name()
function, the public2.order table and the edit.account table. By
switching the webAppUser's 'get_customer_name()' and 'account' synonyms,
this toggling between accessing public1 and public2 objects is quick,
easy and seamless.  The webAppUser code need only contain:
select get_customer_name();
or
select * from order;
without needing to be conscious of whether it is selecting from public1
or public2.

Synonyms are a great feature in Oracle. The lack of synonyms in
PostgreSQL was one of our biggest hesitations in switching. As I said,
however, we found a hacky workaround by toggling the webAppUser's search
path.

/m

On Mon, 06 Dec 2010 15:09:04 -0600, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> though I think it is possible to do
> in Oracle.


I'm not a DBA but the DBA I closely worked with at my last job had me do
maintenance on a VPN that went to another company -- basically we had
synonyms on both ends that let our databases be interconnected. They paid
to have access to our data via this VPN and the synonyms. I'm pretty sure
I remember things changing a few times and if the synonyms weren't
matching on both ends stuff would break. So yeah, I'm 99% this is possible
in Oracle and I don't know how anyone would replicate that type of an
environment in Postgres.


Regards,


Mark

Here's a short overview of what Oracle synonyms provide:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/schema.htm#i5669

/m

Michael C Rosenstein <mcr@mdibl.org> writes:
> For example webAppUser sometimes needs to access the
> public1.get_customer_name() function, the public1.order table and the
> edit.account table. After a new data load of the public2 database, the
> webAppUser would need to access the public2.get_customer_name()
> function, the public2.order table and the edit.account table. By
> switching the webAppUser's 'get_customer_name()' and 'account' synonyms,
> this toggling between accessing public1 and public2 objects is quick,
> easy and seamless.  The webAppUser code need only contain:
> select get_customer_name();
> or
> select * from order;
> without needing to be conscious of whether it is selecting from public1
> or public2.

> Synonyms are a great feature in Oracle. The lack of synonyms in
> PostgreSQL was one of our biggest hesitations in switching. As I said,
> however, we found a hacky workaround by toggling the webAppUser's search
> path.

[ shrug... ] Beauty is in the eye of the beholder, I guess.  To me the
search_path change seems like the natural way to do that, and flipping a
mess of synonyms the hack.  What happens when you miss one synonym?

            regards, tom lane

On 12/6/2010 3:30 PM, Michael C Rosenstein wrote:
> Here's a short overview of what Oracle synonyms provide:
> http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/schema.htm#i5669
>
>
> /m
>

Hum... can we move away from what oracle supports?  Cuz PG is not going
to support anything like it.

And can we get away from oracle parlance?  Michael, can you tell us, in
PG terms, what you could have used.  (err, just saw your "example
webAppUser" above... which I think pretty much covers that, so never mind)

Can someone post what the synonyms will do?  And what will be synonym'able?

(cuz JD said: SYNONYMS work for things that aren't a table.
then tlg said: synonyms for non-table things was pretty much rejected.

so we got... nothing then?)

-Andy

On 12/6/2010 3:41 PM, Andy Colson wrote:
> On 12/6/2010 3:30 PM, Michael C Rosenstein wrote:
>> Here's a short overview of what Oracle synonyms provide:
>> http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/schema.htm#i5669
>>
>>
>>
>> /m
>>
>
> Hum... can we move away from what oracle supports? Cuz PG is not going
> to support anything like it.
>
> And can we get away from oracle parlance? Michael, can you tell us, in
> PG terms, what you could have used. (err, just saw your "example
> webAppUser" above... which I think pretty much covers that, so never mind)
>
> Can someone post what the synonyms will do? And what will be synonym'able?
>
> (cuz JD said: SYNONYMS work for things that aren't a table.
> then tlg said: synonyms for non-table things was pretty much rejected.
>
> so we got... nothing then?)
>
> -Andy
>

oops: s/tlg/tgl/

> [ shrug... ] Beauty is in the eye of the beholder, I guess.  To me the
> search_path change seems like the natural way to do that, and flipping a
> mess of synonyms the hack.  What happens when you miss one synonym?

Changing Oracle synonyms is completely scriptable using the data
dictionary, so we never miss setting a synonym ;)

For the most part, search_path works great for us, but not in situations
in which some target objects are in one database, and other target
objects are in another database but have overlapping names with the
first database

For example, given the following three databases, the 'webAppUser"
end-user may need--transparently, i.e., without using dot notation--to
access the public1.get_bar() function and the edit1.customer table.

public1 DB
----------
get_foo()
customer (table)

public2 DB
----------
get_foo()
customer (table)

edit1 DB
--------
customer (table)

edit2 DB
--------
customer (table)

Setting its search_path to 'public1,edit1' works fine for the get_foo()
function, but not for the customer table.

I linked to the Oracle documentation not to suggest that Postgres must
implement exactly that, but to help convey exactly what I'm talking
about viz 'synonyms'.

I'd be glad to talk off-line w/ someone about the value of this feature,
but I'm not religious about it (and it's likely I don't know enough
about Postgres yet to find a more elegant solution).

Overall, we're very very very happy with Postgres, and excited that next
week we will be going live with our Postgres-converted web app (and
joyously dumping Oracle)!

/m

Andy Colson <andy@squeakycode.net> writes:
> Can someone post what the synonyms will do?  And what will be synonym'able?

> (cuz JD said: SYNONYMS work for things that aren't a table.
> then tgl said: synonyms for non-table things was pretty much rejected.

Well, to clarify: what was shot down IMO was the proposed implementation
with a separate catalog, which would have to be added to the lookup
rules for every kind of object, in particular complicating the
resolution rules for overloaded operators/functions even more than they
already are.

The simple fallback that we discussed was adding another "relkind" to
pg_class entries, so that you could have a pg_class row that was just a
reference to another one.  That wouldn't introduce any new lookup
complexity, because the synonym entry would be just like others (in
particular, you couldn't have a synonym with exactly the same schema
name + relname as some other pg_class row, so it adds no new ambiguity).
But it would only provide synonyms for denizens of pg_class, ie,
tables, views, sequences, indexes.

Now, if there's really interest in synonyms for functions and so on,
you could imagine extending the definitions of other system catalogs
such as pg_proc to similarly allow alias entries in them.  But it'd be a
significant amount of work for each object type you wanted synonyms for,
so you'd need to provide a convincing use-case for each one.  So far,
the plausible use-cases I've heard were just for tables, and maybe
sequences.  There's no data to share in a function.

In any case, references to remote objects such as Oracle can do
seem like an entirely separate issue.  I'd prefer to avoid the Oracle
terminology, if only to avoid confusion with that feature.

            regards, tom lane

    Michael C Rosenstein wrote:

> > What is "schema" in this context?
>
> Oracle "schema" == Postgres "database":  a collection of objects
> (tables, functions, triggers, views, etc) owned by a user.

That definition applies to an Oracle schema, but not to a postgres database.
Objects inside a postgres database are not confined to a unique owner. Even
objects inside the same postgres schema don't have that constraint.

Also the analogy fails in that in Oracle you can refer to schema.object
(which really means owner.object) whereas db.object doesn't work in postgres.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

"Daniel Verite" <daniel@manitou-mail.org> writes:
> Michael C Rosenstein wrote:
>> Oracle "schema" == Postgres "database":  a collection of objects
>> (tables, functions, triggers, views, etc) owned by a user.

> That definition applies to an Oracle schema, but not to a postgres database.
> Objects inside a postgres database are not confined to a unique owner. Even
> objects inside the same postgres schema don't have that constraint.

Hmm, perhaps that's related to something that was confusing me.  The
Oracle page that Michael linked to says that synonyms can

    * Mask the name and owner of an object

    * Enable restricted access similar to specialized views when
      exercising fine-grained access control

Taken at face value from a Postgres perspective, these statements seem
to imply that different ownership and permissions apply to a synonym
than to its referenced object; which seems like a completely horrid idea
from a security standpoint.  But maybe they are only trying to say that
a synonym hides which *schema* the referenced object is in, and that is
tantamount to hiding the owner if you have the mindset that owner ==
schema.  Can anyone elucidate on just what is behind those statements?

            regards, tom lane

Le lundi 06 décembre 2010 à 18:27 -0600, Sairam Krishnamurthy a écrit :

You should start a new thread for this


> Is there a way to call a rule when I use "COPY FROM" instead of
> "INSERT INTO"
>

from the doc :

COPY FROM will invoke any triggers and check constraints on the
destination table. However, it will not invoke rules.

http://www.postgresql.org/docs/9.0/static/sql-copy.html

--
Vincent Veyron
http://marica.fr/
Progiciel de gestion des dossiers de contentieux et d'assurance pour le service juridique

    Tom Lane wrote:

> Taken at face value from a Postgres perspective, these statements seem
> to imply that different ownership and permissions apply to a synonym
> than to its referenced object; which seems like a completely horrid idea
> from a security standpoint.  But maybe they are only trying to say that
> a synonym hides which *schema* the referenced object is in, and that is
> tantamount to hiding the owner if you have the mindset that owner ==
> schema.  Can anyone elucidate on just what is behind those statements?

From
http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authorization
.htm#i1009141

[quote]
A schema object and its synonym are equivalent with respect to privileges.
That is, the object privileges granted on a table, view, sequence, procedure,
function, or package apply whether referencing the base object by name or by
using a synonym.
[/quote]

...

[quote]
If you grant object privileges on a table, view, sequence, procedure,
function, or package by referring to the object through a synonym for the
object, then the effect is the same as if no synonym were used.
[/quote]

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> Command Prompt is currently considering writing a patch to provide
> synonyms to PostgreSQL. Is this something the community is interested
> in? Do we have use cases for it? MSSQL, DB2 and Oracle support them.
>

I must be missing something, but really, what's the point of synonyms?
 What's the real-world use case for them?

On Mon, Dec 6, 2010 at 4:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> [ shrug... ] Beauty is in the eye of the beholder, I guess.  To me the
> search_path change seems like the natural way to do that, and flipping a
> mess of synonyms the hack.  What happens when you miss one synonym?
>

That's exactly what I thought when I read it, too.

    Vick Khera wrote:

> On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drake <jd@commandprompt.com>
> wrote:
> > Command Prompt is currently considering writing a patch to provide
> > synonyms to PostgreSQL. Is this something the community is interested
> > in? Do we have use cases for it? MSSQL, DB2 and Oracle support them.
> >
>
> I must be missing something, but really, what's the point of synonyms?
>  What's the real-world use case for them?

It's about decoupling the name from the actual object, much like what soft
links are for file systems.
It's convenient when you need to change the underlying object without
touching the application code.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

I won't press the issue for Postgres any further, but I will attest that
synonyms work quite elegantly in Oracle, provide valuable functionality,
and do not generally sow confusion among skilled developers.  It sounds
like the proposed "synonym" feature for Postgres perhaps had a different
intention than I assumed, however, especially due to the differences
between the Oracle and PG viz. how "users," "schemas" and "databases" work.

Thanks.

/mcr

Ack, I misspoke in my example last night about our use-case for
synonyms:  we would ust them for trans-*schema* object referencing, not
trans-*database*.

Sorry about that--I fear that may have caused more confusion than necessary.

/m

On 12/7/2010 8:12 AM, Daniel Verite wrote:
>     Vick Khera wrote:
>
>> On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drake<jd@commandprompt.com>
>> wrote:
>>> Command Prompt is currently considering writing a patch to provide
>>> synonyms to PostgreSQL. Is this something the community is interested
>>> in? Do we have use cases for it? MSSQL, DB2 and Oracle support them.
>>>
>>
>> I must be missing something, but really, what's the point of synonyms?
>>   What's the real-world use case for them?
>
> It's about decoupling the name from the actual object, much like what soft
> links are for file systems.
> It's convenient when you need to change the underlying object without
> touching the application code.
>
> Best regards,

So, you could rename a table without having to change the code?  But you
cant rename a column, or drop one, and thats a much more common thing
I'd bet.  And eventually you would change the code, right?  Isn't it
much better to keep everyone on the same page?  If you have 10 program
using 10 different names for the same table... how can that possibly be
useful?  Just sounds confusing and troublesome.

I can see a situation for live/hot upgrades.  Having old code and new
code run at the same time.  But eventually the old code would go away,
and I think the same thing could be handled with views.  (perhaps
updateable view's would be required... but still)

I dont see a situation where an alias gives me something updateable
views dont.  I'd vote we spend time on updateable views instead.

And the types:

table: maybe useful for live upgrade, but views, transactons and stored
procs do the same thing.

views: just create the new view.  Have both.  when the old code goes
away, drop the old view.  No need for an alias.

sequence:  why bother?  Other than renaming during live upgrade, why
would you need an alias?

index:  again, why bother... code really should not ever be dependent on
an indexes name, correct?  And transactions take care of live updates.

So for the two use cases I've seen (live update, directing data flow
(which is kinda like a live update)) we already have tools that do it:
transactional ddl, views, schemas, stored procs, etc.  Updateable views
might be the only thing missing.

Also:  I wonder if it might be a bad idea.  The people coming from
oracle will see that PG supports synonyms, and they'll be all happy, but
when they get into the guts of their translate they find PG's synonyms
are different (and not compatible), and they have to throw it out and
use schemas instead.

On the other hand, now that I think about it, if its really easy, it
might help a few people out, then why not.  On the other other hand, if
its not so easy, I think the time would be better spent on updatable views.

So here is my new vote:
IF its easy and wont slow anything down: +1
IF its hard: -1 (and spend the time on more important things)

-Andy

On Tue, 2010-12-07 at 08:31 -0500, Vick Khera wrote:
> On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> > Command Prompt is currently considering writing a patch to provide
> > synonyms to PostgreSQL. Is this something the community is interested
> > in? Do we have use cases for it? MSSQL, DB2 and Oracle support them.
> >
>
> I must be missing something, but really, what's the point of synonyms?
>  What's the real-world use case for them?

For a PostgreSQL Person? I see no real benefit to be honest. For people
coming from Oracle, DB2 or MSSQL? I see a real benefit in terms of ease
of porting.

I asked on the Oracle free list[1] and Synonyms are used and used a lot
in Oracle. Anything we can do to help those folks run screaming from
err.... port to PostgreSQL seems like a good idea. (Assuming we can do
it reasonably)

Sincerely,

Joshua D. Drake

1. http://www.freelists.org/post/oracle-l/Synonyms

>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On Tue, 2010-12-07 at 09:14 -0500, Michael C Rosenstein wrote:
> I won't press the issue for Postgres any further, but I will attest that
> synonyms work quite elegantly in Oracle, provide valuable functionality,
> and do not generally sow confusion among skilled developers.  It sounds
> like the proposed "synonym" feature for Postgres perhaps had a different
> intention than I assumed, however, especially due to the differences
> between the Oracle and PG viz. how "users," "schemas" and "databases" work.

Your perception has been mirrored on the Oracle free list. Really what
PostgreSQL people need to come to grips with is whether or not we want
to make it easier for others to port to Pg or not. (assuming
reasonableness)



JD

>
> Thanks.
>
> /mcr
>
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

Hello

2010/12/7 Joshua D. Drake <jd@commandprompt.com>:
> On Tue, 2010-12-07 at 09:14 -0500, Michael C Rosenstein wrote:
>> I won't press the issue for Postgres any further, but I will attest that
>> synonyms work quite elegantly in Oracle, provide valuable functionality,
>> and do not generally sow confusion among skilled developers.  It sounds
>> like the proposed "synonym" feature for Postgres perhaps had a different
>> intention than I assumed, however, especially due to the differences
>> between the Oracle and PG viz. how "users," "schemas" and "databases" work.
>
> Your perception has been mirrored on the Oracle free list. Really what
> PostgreSQL people need to come to grips with is whether or not we want
> to make it easier for others to port to Pg or not. (assuming
> reasonableness)
>

it's question if this is task more for EnterpriseDB and less for PostgreSQL?

Pavel




>
>
> JD
>
>>
>> Thanks.
>>
>> /mcr
>>
>>
>
> --
> PostgreSQL.org Major Contributor
> Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
> Consulting, Training, Support, Custom Development, Engineering
> http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

"Joshua D. Drake" <jd@commandprompt.com> writes:
> On Tue, 2010-12-07 at 08:31 -0500, Vick Khera wrote:
>> I must be missing something, but really, what's the point of synonyms?
>> What's the real-world use case for them?

> For a PostgreSQL Person? I see no real benefit to be honest. For people
> coming from Oracle, DB2 or MSSQL? I see a real benefit in terms of ease
> of porting.

They're only going to make it easier to port if we cover *all* the
functionality of Oracle synonyms, with *exactly* the same behavior.
Otherwise this is just an advertising stunt ...

            regards, tom lane

On 12/07/2010 10:45 AM, Joshua D. Drake wrote:
> On Tue, 2010-12-07 at 08:31 -0500, Vick Khera wrote:
>> On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drake<jd@commandprompt.com>  wrote:
>>> Command Prompt is currently considering writing a patch to provide
>>> synonyms to PostgreSQL. Is this something the community is interested
>>> in? Do we have use cases for it? MSSQL, DB2 and Oracle support them.
>>>
>>
>> I must be missing something, but really, what's the point of synonyms?
>>   What's the real-world use case for them?
>
> For a PostgreSQL Person? I see no real benefit to be honest. For people
> coming from Oracle, DB2 or MSSQL? I see a real benefit in terms of ease
> of porting.
>
> I asked on the Oracle free list[1] and Synonyms are used and used a lot
> in Oracle. Anything we can do to help those folks run screaming from
> err.... port to PostgreSQL seems like a good idea. (Assuming we can do
> it reasonably)
>
> Sincerely,
>
> Joshua D. Drake

If I am following this thread correctly the biggest issue to date is
getting an apple to apple comparison. The confusion seems to be that
what is proposed for SYNONYMS in Pg is not actually a synonym for
SYNONYMS in Oracle.


--
Adrian Klaver
adrian.klaver@gmail.com

On 12/7/2010 1:07 PM, Adrian Klaver wrote:
> On 12/07/2010 10:45 AM, Joshua D. Drake wrote:
>> On Tue, 2010-12-07 at 08:31 -0500, Vick Khera wrote:
>>> On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drake<jd@commandprompt.com>
>>> wrote:
>>>> Command Prompt is currently considering writing a patch to provide
>>>> synonyms to PostgreSQL. Is this something the community is interested
>>>> in? Do we have use cases for it? MSSQL, DB2 and Oracle support them.
>>>>
>>>
>>> I must be missing something, but really, what's the point of synonyms?
>>> What's the real-world use case for them?
>>
>> For a PostgreSQL Person? I see no real benefit to be honest. For people
>> coming from Oracle, DB2 or MSSQL? I see a real benefit in terms of ease
>> of porting.
>>
>> I asked on the Oracle free list[1] and Synonyms are used and used a lot
>> in Oracle. Anything we can do to help those folks run screaming from
>> err.... port to PostgreSQL seems like a good idea. (Assuming we can do
>> it reasonably)
>>
>> Sincerely,
>>
>> Joshua D. Drake
>
> If I am following this thread correctly the biggest issue to date is
> getting an apple to apple comparison. The confusion seems to be that
> what is proposed for SYNONYMS in Pg is not actually a synonym for
> SYNONYMS in Oracle.
>
>
:-) I see what you did there!


I think it covers parts.  In both you can create an alias to a table,
both of which you can fire off insert/update/delete.  I assume in PG you
could have different permissions for the table and the alias, which I
assume you can do in oracle.

If we pretend oracle and PG both have the same thing as a schema, and
using PG's definition of schema:

I assume in oracle you can "create table synonym schemaA.bob for
schemaB.tablex"

And I assume you could do the same in PG.

However beyond that, I dont know what oracle supports that we'd need.

(need, as in, oracle synonyms between different database instances on
different computers is not going to happen.)

-Andy

On Tue, 2010-12-07 at 19:54 +0100, Pavel Stehule wrote:
> Hello
>
> 2010/12/7 Joshua D. Drake <jd@commandprompt.com>:
> > On Tue, 2010-12-07 at 09:14 -0500, Michael C Rosenstein wrote:
> >> I won't press the issue for Postgres any further, but I will attest that
> >> synonyms work quite elegantly in Oracle, provide valuable functionality,
> >> and do not generally sow confusion among skilled developers.  It sounds
> >> like the proposed "synonym" feature for Postgres perhaps had a different
> >> intention than I assumed, however, especially due to the differences
> >> between the Oracle and PG viz. how "users," "schemas" and "databases" work.
> >
> > Your perception has been mirrored on the Oracle free list. Really what
> > PostgreSQL people need to come to grips with is whether or not we want
> > to make it easier for others to port to Pg or not. (assuming
> > reasonableness)
> >
>
> it's question if this is task more for EnterpriseDB and less for PostgreSQL?

Well no I don't think that is a valid question honestly. EDB Advanced
server is a proprietary product that has zero standing with the
community direction. That is not a negative remark on EDB or Advanced
server just that it really isn't our concern.

JD
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On 2010-12-07, Andy Colson <andy@squeakycode.net> wrote:

> I think it covers parts.  In both you can create an alias to a table,
> both of which you can fire off insert/update/delete.  I assume in PG you
> could have different permissions for the table and the alias, which I
> assume you can do in oracle.
>
> If we pretend oracle and PG both have the same thing as a schema, and
> using PG's definition of schema:
>
> I assume in oracle you can "create table synonym schemaA.bob for
> schemaB.tablex"
>
> And I assume you could do the same in PG.
>
> However beyond that, I dont know what oracle supports that we'd need.

They want synonyms for functions, but as far as I can see the same can be
achieved with minimal extra work by creating a new LANGUAGE SQL function
that calls the original.

CREATE FUNCTION newschema.newname( atype ... ) RETURNS rtype
AS ' select oldschema.oldname ( $1 ... ) ' LANGUAGE SQL;

with apropriare values for the lowercase bits and elipsis.

--
⚂⚃ 100% natural

On Dec 13, 2010, at 12:03 PM, Jasen Betts wrote:

> On 2010-12-07, Andy Colson <andy@squeakycode.net> wrote:
>
>> I think it covers parts.  In both you can create an alias to a table,
>> both of which you can fire off insert/update/delete.  I assume in PG you
>> could have different permissions for the table and the alias, which I
>> assume you can do in oracle.
>>
>> If we pretend oracle and PG both have the same thing as a schema, and
>> using PG's definition of schema:
>>
>> I assume in oracle you can "create table synonym schemaA.bob for
>> schemaB.tablex"
>>
>> And I assume you could do the same in PG.
>>
>> However beyond that, I dont know what oracle supports that we'd need.
>
> They want synonyms for functions, but as far as I can see the same can be
> achieved with minimal extra work by creating a new LANGUAGE SQL function
> that calls the original.
>
> CREATE FUNCTION newschema.newname( atype ... ) RETURNS rtype
> AS ' select oldschema.oldname ( $1 ... ) ' LANGUAGE SQL;
>
> with apropriare values for the lowercase bits and elipsis.

This could possibly lead to performance issues , and there would be no error
or warning message if you occasionally drop the oldschema.oldname, rendering
the newschema.newname useless.

/A
--
Alexey Klyukin                    http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc

On Dec 7, 2010, at 2:10 AM, Tom Lane wrote:

> Andy Colson <andy@squeakycode.net> writes:
>> Can someone post what the synonyms will do?  And what will be synonym'able?
>
>> (cuz JD said: SYNONYMS work for things that aren't a table.
>> then tgl said: synonyms for non-table things was pretty much rejected.
>
> Well, to clarify: what was shot down IMO was the proposed implementation
> with a separate catalog, which would have to be added to the lookup
> rules for every kind of object, in particular complicating the
> resolution rules for overloaded operators/functions even more than they
> already are.
>
> The simple fallback that we discussed was adding another "relkind" to
> pg_class entries, so that you could have a pg_class row that was just a
> reference to another one.  That wouldn't introduce any new lookup
> complexity, because the synonym entry would be just like others (in
> particular, you couldn't have a synonym with exactly the same schema
> name + relname as some other pg_class row, so it adds no new ambiguity).
> But it would only provide synonyms for denizens of pg_class, ie,
> tables, views, sequences, indexes.
>
> Now, if there's really interest in synonyms for functions and so on,
> you could imagine extending the definitions of other system catalogs
> such as pg_proc to similarly allow alias entries in them.  But it'd be a
> significant amount of work for each object type you wanted synonyms for,
> so you'd need to provide a convincing use-case for each one.  So far,
> the plausible use-cases I've heard were just for tables, and maybe
> sequences.  There's no data to share in a function.


Agreed. I was also thinking about using catalog-specific changes to add
synonyms for objects other than tables, views and sequences. It also possible
that there's no need in synonyms for tables, but synonyms for table columns,
or database roles would be useful. Hence, the question is not only 'do we want
synonyms', but also, if we do, then for which kinds of objects?

>
> In any case, references to remote objects such as Oracle can do
> seem like an entirely separate issue.  I'd prefer to avoid the Oracle
> terminology, if only to avoid confusion with that feature.


Agreed as well.
--
Alexey Klyukin                    http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc