Re: [GENERAL] Do we want SYNONYMS?

[Alexey Klyukin]

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

> Andy Colson  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


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

Re: [GENERAL] Do we want SYNONYMS?

[Alexey Klyukin]

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

> On 2010-12-07, Andy Colson  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


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

Re: [GENERAL] Do we want SYNONYMS?

[Jasen Betts]

On 2010-12-07, Andy Colson  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

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

Re: [GENERAL] Do we want SYNONYMS?

[Gurjeet Singh]

On Tue, Dec 7, 2010 at 1:54 PM, Pavel Stehule wrote:

> Hello
>
> 2010/12/7 Joshua D. Drake :
> > 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?
>
>
FWIW, EnterpriseDB Advanced Server has had the SYNONYM feature for quite a
while now: http://www.enterprisedb.com/documentation/ddl-synonims.html

Regards,
-- 
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device

Re: [GENERAL] Do we want SYNONYMS?

[Joshua D. Drake]

On Tue, 2010-12-07 at 19:54 +0100, Pavel Stehule wrote:
> Hello
> 
> 2010/12/7 Joshua D. Drake :
> > 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


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

Re: [GENERAL] Do we want SYNONYMS?

[Andy Colson]

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

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

Re: [GENERAL] Do we want SYNONYMS?

[Adrian Klaver]

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  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.kla...@gmail.com

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

Re: [GENERAL] Do we want SYNONYMS?

[Tom Lane]

"Joshua D. Drake"  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

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

Re: [GENERAL] Do we want SYNONYMS?

[Pavel Stehule]

Hello

2010/12/7 Joshua D. Drake :
> 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
>

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

Re: [GENERAL] Do we want SYNONYMS?

[Joshua D. Drake]

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


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

Re: [GENERAL] Do we want SYNONYMS?

[Joshua D. Drake]

On Tue, 2010-12-07 at 08:31 -0500, Vick Khera wrote:
> On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drake  
> 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


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

Re: [GENERAL] Do we want SYNONYMS?

[Dmitriy Igrishin]

2010/12/7 Andy Colson 

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

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



-- 
// Dmitriy.

Re: [GENERAL] Do we want SYNONYMS?

[Andy Colson]

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

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

Re: [GENERAL] Do we want SYNONYMS?

[Michael C Rosenstein]

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


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

Re: [GENERAL] Do we want SYNONYMS?

[Michael C Rosenstein]

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


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

Re: [GENERAL] Do we want SYNONYMS?

[Daniel Verite]

Vick Khera wrote:

> On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drake 
> 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

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

Re: [GENERAL] Do we want SYNONYMS?

[Vick Khera]

On Mon, Dec 6, 2010 at 4:38 PM, Tom Lane  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.

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

Re: [GENERAL] Do we want SYNONYMS?

[Vick Khera]

On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drake  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?

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

Re: [GENERAL] Do we want SYNONYMS?

[Dmitriy Igrishin]

Hey Daniel,

Again link to oracle.com...

During this thread I believe that synonyms gives nothing
except confusion and mess.

2010/12/7 Daniel Verite 

>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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
// Dmitriy.

Re: [GENERAL] Do we want SYNONYMS?

[Daniel Verite]

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

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

Re: [GENERAL] Do we want SYNONYMS?

[Tom Lane]

"Daniel Verite"  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

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

Re: [GENERAL] Do we want SYNONYMS?

[Daniel Verite]

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

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

Re: [GENERAL] Do we want SYNONYMS?

[Tom Lane]

Andy Colson  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

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

Re: [GENERAL] Do we want SYNONYMS?

[Michael C Rosenstein]

[ 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

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

Re: [GENERAL] Do we want SYNONYMS?

[Gauthier, Dave]

Not multiple databases, multiple sites looking at the same DB, each using a 
somewhat different naming system.  And then apps/scripts from one site (using 
that venacular) are shared with others at other sites (using a different 
venacular).  So even within a site you have multiple ways of querying the table.

Views don't seem like a good option because you'd have to know ahead of time 
what view to use.  Also, for one table there may be many different columns that 
have multiple names.  So you'd need one view per name combo.

Instead, something like...

  create table foo (last_name:lastname:surname text, first_name:firstname text, 
date_of_birth:dob:year_born int);

... would be real sweet !

Yes, it can be (and is being) done externally with wrappers.  If a user wants 
to query the DB directly, they have to know the names that were chosen/used 
when the table was defined.





From: Dmitriy Igrishin [mailto:dmit...@gmail.com]
Sent: Monday, December 06, 2010 4:26 PM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Do we want SYNONYMS?


2010/12/7 Dmitriy Igrishin mailto:dmit...@gmail.com>>

2010/12/7 Gauthier, Dave 
mailto:dave.gauth...@intel.com>>

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 that have identical data concepts but with different names for the 
same thing.  It would be nice to just equate these names in the DB instead of 
in perl wrappers around sql calls.
Interesting, how will you maintain synonyms in a many databases ?
IMO it is more simple to make abstraction level at the application side
in one place rather than create synonyms in different databases.
And if you just standardize the naming in a different databases why
not use views ?



-Original Message-
From: 
pgsql-general-ow...@postgresql.org<mailto:pgsql-general-ow...@postgresql.org> 
[mailto:pgsql-general-ow...@postgresql.org<mailto:pgsql-general-ow...@postgresql.org>]
 On Behalf Of Andy Colson
Sent: Monday, December 06, 2010 2:57 PM
To: j...@commandprompt.com<mailto:j...@commandprompt.com>
Cc: pgsql-general@postgresql.org<mailto: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<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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



--
// Dmitriy.




--
// Dmitriy.

Re: [GENERAL] Do we want SYNONYMS?

[Andy Colson]

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/

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

Re: [GENERAL] Do we want SYNONYMS?

[Andy Colson]

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

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

Re: [GENERAL] Do we want SYNONYMS?

[Tom Lane]

Michael C Rosenstein  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

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

Re: [GENERAL] Do we want SYNONYMS?

[Dmitriy Igrishin]

2010/12/7 Michael C Rosenstein 

> On 12/6/10 4:09 PM, Tom Lane wrote:
>
>> Michael C Rosenstein  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.
>
There are NOTIFY/LISTEN system in PostgreSQL and you can use
appropriate function on some event (data loaded in you case), for example.

>
> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
// Dmitriy.

Re: [GENERAL] Do we want SYNONYMS?

[Michael C Rosenstein]

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

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

Re: [GENERAL] Do we want SYNONYMS?

[Mark Felder]

On Mon, 06 Dec 2010 15:09:04 -0600, Tom Lane  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

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

Re: [GENERAL] Do we want SYNONYMS?

[Michael C Rosenstein]

On 12/6/10 4:09 PM, Tom Lane wrote:

Michael C Rosenstein  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

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

Re: [GENERAL] Do we want SYNONYMS?

[Dmitriy Igrishin]

2010/12/7 Dmitriy Igrishin 

>
>
> 2010/12/7 Gauthier, Dave 
>
> 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 that have identical data concepts but with different names for
>> the same thing.  It would be nice to just equate these names in the DB
>> instead of in perl wrappers around sql calls.
>>
> Interesting, how will you maintain synonyms in a many databases ?
> IMO it is more simple to make abstraction level at the application side
> in one place rather than create synonyms in different databases.
>
And if you just standardize the naming in a different databases why
not use views ?

>
>
>>
>> -Original Message-
>> From: pgsql-general-ow...@postgresql.org [mailto:
>> pgsql-general-ow...@postgresql.org] On Behalf Of Andy Colson
>> Sent: Monday, December 06, 2010 2:57 PM
>> To: j...@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
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> // Dmitriy.
>
>
>


-- 
// Dmitriy.

Re: [GENERAL] Do we want SYNONYMS?

[Dmitriy Igrishin]

2010/12/7 Gauthier, Dave 

> 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 that have identical data concepts but with different names for
> the same thing.  It would be nice to just equate these names in the DB
> instead of in perl wrappers around sql calls.
>
Interesting, how will you maintain synonyms in a many databases ?
IMO it is more simple to make abstraction level at the application side
in one place rather than create synonyms in different databases.


>
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:
> pgsql-general-ow...@postgresql.org] On Behalf Of Andy Colson
> Sent: Monday, December 06, 2010 2:57 PM
> To: j...@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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
// Dmitriy.

Re: [GENERAL] Do we want SYNONYMS?

[Gauthier, Dave]

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 that have identical data concepts but with different names for the 
same thing.  It would be nice to just equate these names in the DB instead of 
in perl wrappers around sql calls.
 

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andy Colson
Sent: Monday, December 06, 2010 2:57 PM
To: j...@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

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

Re: [GENERAL] Do we want SYNONYMS?

[Tom Lane]

Michael C Rosenstein  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

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

Re: [GENERAL] Do we want SYNONYMS?

[Tom Lane]

"Joshua D. Drake"  writes:
> On Mon, 2010-12-06 at 15:27 -0500, Tom Lane wrote:
>> "Joshua D. Drake"  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

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

Re: [GENERAL] Do we want SYNONYMS?

[Dmitriy Igrishin]

Ahh, catalog :-)
But PostgreSQL has a templates. If I understood you correctly,
the problem is to let the application works with same object names
of the objects in a different databases?

2010/12/6 Michael C Rosenstein 

> What is "schema" in this context?
>>
>
> Oracle "schema" == Postgres "database":  a collection of objects (tables,
> functions, triggers, views, etc) owned by a user.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
// Dmitriy.

Re: [GENERAL] Do we want SYNONYMS?

[Michael C Rosenstein]

What is "schema" in this context?


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



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

Re: [GENERAL] Do we want SYNONYMS?

[Michael C Rosenstein]

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



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

Re: [GENERAL] Do we want SYNONYMS?

[Joshua D. Drake]

On Mon, 2010-12-06 at 15:27 -0500, Tom Lane wrote:
> "Joshua D. Drake"  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


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

Re: [GENERAL] Do we want SYNONYMS?

[Tom Lane]

"Joshua D. Drake"  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

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

Re: [GENERAL] Do we want SYNONYMS?

[Dmitriy Igrishin]

Hey Michael,

2010/12/6 Michael C Rosenstein 

> 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.
>
Interesting. What is "schema" in this context?

>
> 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
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
// Dmitriy.

Re: [GENERAL] Do we want SYNONYMS?

[Dmitriy Igrishin]

What is synonym? Is it a reference? Can I dump DDL of the object
by synonym? If no, I personally don't see how it can be used.
Maybe it can be used to create 7 synonyms for some table and let
application use different synonym depends on day of the week... :-)
I don't see how it can be used...

2010/12/6 Joshua D. Drake 

> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
// Dmitriy.

Re: [GENERAL] Do we want SYNONYMS?

[Michael C Rosenstein]

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


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

Re: [GENERAL] Do we want SYNONYMS?

[Joshua D. Drake]

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


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

Re: [GENERAL] Do we want SYNONYMS?

[Andy Colson]

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

Re: [GENERAL] Do we want SYNONYMS?

[Dmitriy Igrishin]

Hey, Joshua, -general,

If the user create a schema for placing synonyms for all functions
of all schemas in the database then will it be possible to make dump
of this schema but not only with CREATE synonyms clauses, but with
functions definitions also ? :-) It would be nice.


2010/12/6 Joshua D. Drake 

> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
// Dmitriy.

[GENERAL] Do we want SYNONYMS?

[Joshua D. Drake]

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


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