Do we want SYNONYMS?

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 <jd@commandprompt.com>

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.

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

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 <jd@commandprompt.com>

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.

Hey Michael,

2010/12/6 Michael C Rosenstein <mcr@mdibl.org>

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.

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

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 <mcr@mdibl.org>

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.

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

2010/12/7 Gauthier, Dave <dave.gauthier@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.

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

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

--
// Dmitriy.

2010/12/7 Dmitriy Igrishin <dmitigr@gmail.com>

2010/12/7 Gauthier, Dave <dave.gauthier@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-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

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

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