Another proposal for table synonyms

Hello,

Here is the proposal to add synonyms to PostgreSQL. Initial goal is to add synonyms
for relations (tables, views, sequences) and an infrastructure to allow synonyms
for other database objects in the future.

A thread with discussion of an old proposal by Jonah Harris is here:
http://archives.postgresql.org/pgsql-hackers/2006-03/msg00519.php

-----
Synonyms are database objects, which act as an alias for other objects. In
this proposal the synonyms for tables, views and sequences will be considered.

The new command, CREATE SYNONYM, defines a synonym. The syntax is:

CREATE SYNONYM synonym_name FOR object_type object_name
where
synonym_name: fully-qualified name (FQN) of the synonym
object_type: {TABLE | VIEW | SEQUENCE}. In the future, new object_types, such as
functions, can be added.
object_name: FQN of a database table, view or sequence.

Another new command, DROP SYNONYM, is used for deleting an already existing
synonym without removing the object the synonym references. The syntax is:

DROP SYNONYM synonym_name
where synonym_name is a FQN of a synonym.

Comments will be supported on synonyms with the following command:
COMMENT ON SYNONYM synonym_name IS comment_text
where synonym_name is a FQN of a synonym.

To support addition of new database objects types that can be referenced by
synonyms a new system catalog, pg_synonym, is to be added, with an oid to
support comments on synonym, and the following schema:

synname name name of the synonym
synnamespace oid OID of the namespace that contains the synonym
synclassid oid OID of the system catalog that contains the referenced object
synobjid oid OID of the referenced object

When resolving the synonym name, the usual search_path lookup rules apply,
i.e. first, the object of the appropriate type is looked into the schema, then
the synonym, afterwards the process iterates with the next schema from the
search_path. Note that the table synonym with the same FQN as an existing
table will be masked by that table.

To speedup the synonym name resolution a new syscache, SYNNAMENSPCLASS
{synname, synnamespace, synclassid} will be introduced. This cache will be
accessed if the query to the RELNAMENSP syscache will return no result, with
the DB object's catalog OID set to pg_class OID.

For table and view synonyms, INSERT/UPDATE/DELETE/SELECT will be supported.
For sequences SELECT will be supported. The commands will translate synonyms
to the referenced database objects on the parser stage.

All types of synonyms will be supported as table arguments/return value types,
as well as actual values (i.e. currval/nextval will accept a sequence
synonym).

The following DDL will work transparently with table synonyms (sequences and
views if the corresponding command applies to them):
COPY, LOCK, TRUNCATE, EXPLAIN, EXPLAIN ANALYZE.

The following DDL commands will cause an error when called for tables
(sequences, views) synonyms:
ALTER {TABLE|VIEW|SEQUENCE},
ANALYZE,
CLUSTER,
COMMENT ON {TABLE | VIEW | SEQUENCE} .. IS,
DROP {TABLE | VIEW | SEQUENCE},
GRANT,
REVOKE,
VACUUM.
For these commands additional checks for synonyms will be introduced on a
per-command basis.

A dependency of the referenced object on a synonym will be added when adding a
new synonym to forbid removing a referenced object without removing the
synonym first (without using CASCADE). On DROP SYNONYM the related dependency
will be removed.

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

Alexey Klyukin <alexk(at)commandprompt(dot)com> writes:
> To support addition of new database objects types that can be referenced by
> synonyms a new system catalog, pg_synonym, is to be added, with an oid to
> support comments on synonym, and the following schema:

This is not going to work, at least not without making every type of
lookup consult pg_synonym too, which I think can be considered DOA
because of its performance impact on people who aren't even using the
feature. It's also quite unclear how you prevent duplicate names
if the synonyms are in their own catalog. (And no, the part of your
proposal that says you're not preventing that isn't acceptable from
a usability standpoint.)

You could reasonably support synonyms for tables/views by storing them
in pg_class with a new relkind. This doesn't cover synonyms for other
object types, but since the total world demand for such a feature is
approximately zero, that's not really a problem.

regards, tom lane

On Nov 30, 2010, at 6:28 PM, Tom Lane wrote:

> Alexey Klyukin <alexk(at)commandprompt(dot)com> writes:
>> To support addition of new database objects types that can be referenced by
>> synonyms a new system catalog, pg_synonym, is to be added, with an oid to
>> support comments on synonym, and the following schema:
>
> This is not going to work, at least not without making every type of
> lookup consult pg_synonym too, which I think can be considered DOA
> because of its performance impact on people who aren't even using the
> feature.

For those not using synonyms it would result in an extra syscache lookup for
each schema from the search_path that doesn't contain the table with the
specified name. If the table is specified with A FQN or contained in the first
schema from the search_path no extra lookup would occur. Is it considered a
big impact? The number of such lookups can be reduced if we traverse the
search_path for the tables first, and then look for the synonyms, although
that would change the lookup rules stated in this proposal

> It's also quite unclear how you prevent duplicate names
> if the synonyms are in their own catalog. (And no, the part of your
> proposal that says you're not preventing that isn't acceptable from
> a usability standpoint.)

What's wrong with the usability of that feature? The fact that the table with
the same FQN as a synonym masks the latter can be clearly stated in the
documentation. Are you expecting lots of people to name the synonym exactly
the same as one of the database tables and wonder why is the table and not the
synonym gets accessed? As an alternative, a warning during table/synonym
creation/renaming can be emitted if the name clash occurs.

>
> You could reasonably support synonyms for tables/views by storing them
> in pg_class with a new relkind. This doesn't cover synonyms for other
> object types, but since the total world demand for such a feature is
> approximately zero, that's not really a problem.

I think that would almost guarantee that synonyms for other kinds of objects
(i.e. databases, such kind of synonyms were requested in the past) would never
be added.

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

Alexey Klyukin <alexk(at)commandprompt(dot)com> writes:
> On Nov 30, 2010, at 6:28 PM, Tom Lane wrote:
>> This is not going to work, at least not without making every type of
>> lookup consult pg_synonym too, which I think can be considered DOA
>> because of its performance impact on people who aren't even using the
>> feature.

> For those not using synonyms it would result in an extra syscache lookup for
> each schema from the search_path that doesn't contain the table with the
> specified name. If the table is specified with A FQN or contained in the first
> schema from the search_path no extra lookup would occur. Is it considered a
> big impact?

Yes. It'll be slow and it will render code that's already unreasonably
complicated into an unreadable morass. We are not going there.

(Just to be clear, it's not the table search case I'm worried about;
it's operator/function lookup that I think this would be completely
unacceptable for. And if you're only going to support table/view
synonyms then you might as well put them in pg_class.)

> I think that would almost guarantee that synonyms for other kinds of objects
> (i.e. databases, such kind of synonyms were requested in the past) would never
> be added.

That's fine with me.

regards, tom lane

Alexey,

> Here is the proposal to add synonyms to PostgreSQL. Initial goal is to add synonyms
> for relations (tables, views, sequences) and an infrastructure to allow synonyms
> for other database objects in the future.

Can you explain, for our benefit, the use case for this? Specifically,
what can be done with synonyms which can't be done with search_path and
VIEWs?

I ask partly because I've migrated some Oracle databases to PostgreSQL,
and did not find replacing the functionality of synonyms to be at all
difficult. Presumably you've run into a case which was difficult?

BTW, I have a specific use case for *column* synonyms which isn't
currently covered by our existing tools.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

Hi all,

On Tue, 2010-11-30 at 12:05 -0800, Josh Berkus wrote:
> Can you explain, for our benefit, the use case for this? Specifically,
> what can be done with synonyms which can't be done with search_path and
> VIEWs?

I had a few cases where synonyms for user/data base names would have
helped me slightly (not something I couldn't work around).

The actual use case was when I wanted to change the name of a data base
and user names (just a configuration coherency thing) - using a synonym
I could have done it without downtime by creating the synonym first,
then reconfiguring each application machine individually (they are
redundant, and can be restarted transparently). Without the synonyms, I
had to wait for the next full downtime (which we do quite rarely) and
reconfigure the DB and all application boxes at the same time.

Ideally the user/DB name synonym would be like a hard link, equivalent
in all respects to the original name, so that you can delete the
original name and the synonym continues to work.

Likely the pg_hba.conf would need to still distinguish between the
DB/user names and their synonyms - not sure if that could be useful or
would be a PITA.

Of course this has nothing to do with the table synonyms - there I
didn't have yet any reason I would use one...

Cheers,
Csaba.

On Nov 30, 2010, at 10:05 PM, Josh Berkus wrote:

> Alexey,
>
>> Here is the proposal to add synonyms to PostgreSQL. Initial goal is to add synonyms
>> for relations (tables, views, sequences) and an infrastructure to allow synonyms
>> for other database objects in the future.
>
> Can you explain, for our benefit, the use case for this? Specifically,
> what can be done with synonyms which can't be done with search_path and
> VIEWs?

Well, porting applications from other database systems that support synonyms
(i.e. Oracle, DB2, SQL Server).

>
> I ask partly because I've migrated some Oracle databases to PostgreSQL,
> and did not find replacing the functionality of synonyms to be at all
> difficult. Presumably you've run into a case which was difficult?

Frankly, I don't have a specific use case, but there were some requests in
this list asking for synonyms, and adding support for them is a TODO item in
wiki.

>
> BTW, I have a specific use case for *column* synonyms which isn't
> currently covered by our existing tools.

Is this the feature the community would benefit from? We can consider adding
column synonyms if we won't hardwire synonyms to pg_class objects.

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

> Well, porting applications from other database systems that support synonyms
> (i.e. Oracle, DB2, SQL Server).

SQL Server supports synonyms? If it's not Oracle-only, it's a more
powerful argument to have the feature.

(IMHO, the main reason why Oracle has synonyms is that their
implementation of SCHEMA is broken.)

There are two more arguments for table synonyms:

1. to support application versioning with an easier syntax than
updatable views.

2. to provide an alternative to the difficult-to-manage search_path

However, the latter does mean that there needs to be a fixed
order-of-resolution for synonyms which conflict with the name of objects
in other schema. And one which doesn't break backwards compatiblity.

I'd love to hear from someone at EDB: how are you dealing with synonym
name collisions right now?

> Is this the feature the community would benefit from? We can consider adding
> column synonyms if we won't hardwire synonyms to pg_class objects.

Actually, we'd just put that one in pg_attribute. I worked out a
back-of-the-napkin design, and it wouldn't require any new tables. A
new column, yes. But no new catalog tables.

So, I don't support your idea of having a completely separate catalog.
Sorry.

The use case for simple column synonyms is supporting application
versioning by allowing changes to column names without needing to
refactor all applications. Later, we could also implement "calculated
columns" where the synonym points to an expression rather than a direct
column link. All sorts of use cases for that.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Wed, 2010-12-01 at 12:13 -0800, Josh Berkus wrote:
> > Well, porting applications from other database systems that support synonyms
> > (i.e. Oracle, DB2, SQL Server).
>
> SQL Server supports synonyms? If it's not Oracle-only, it's a more
> powerful argument to have the feature.

Oracle, DB2 and MSSQL support Synonyms.

> I'd love to hear from someone at EDB: how are you dealing with synonym
> name collisions right now?

I think the way we deal with that is the way PostgreSQL deals with it.
Unique names per search path.

> > Is this the feature the community would benefit from? We can consider adding
> > column synonyms if we won't hardwire synonyms to pg_class objects.

Column synonyms don't exist as far as I can tell (at least in Oracle)[1]

>
> So, I don't support your idea of having a completely separate catalog.
> Sorry.

Yeah we have been talking about this internally and it seems (at least
to me) that the 85% solution in pg_class that supports
tables/views/sequences is best.

I posted to the Oracle list asking how many of them use Synonyms and
they are definitely a used feature.

JD

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

>> I'd love to hear from someone at EDB: how are you dealing with synonym
>> name collisions right now?
>
> I think the way we deal with that is the way PostgreSQL deals with it.
> Unique names per search path.

Have you had an employment change I didn't know about, JD? ;-)

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Wed, Dec 1, 2010 at 8:46 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>>> I'd love to hear from someone at EDB: how are you dealing with synonym
>>> name collisions right now?
>>
>> I think the way we deal with that is the way PostgreSQL deals with it.
>> Unique names per search path.
>
> Have you had an employment change I didn't know about, JD?  ;-)

I was wondering the same thing. And trying to figure out what I could
get him to work on :-p

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, 2010-12-01 at 12:46 -0800, Josh Berkus wrote:
> >> I'd love to hear from someone at EDB: how are you dealing with synonym
> >> name collisions right now?
> >
> > I think the way we deal with that is the way PostgreSQL deals with it.
> > Unique names per search path.
>
> Have you had an employment change I didn't know about, JD? ;-)

DOH!, no. It is an interesting thought though. However, I think the
marketing folks over there would just up and die if I came on board.

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

Excerpts from Josh Berkus's message of mié dic 01 17:13:35 -0300 2010:
>
> > Well, porting applications from other database systems that support synonyms
> > (i.e. Oracle, DB2, SQL Server).
>
> SQL Server supports synonyms? If it's not Oracle-only, it's a more
> powerful argument to have the feature.

I think it's worth mentioning that in SQL Server, synonyms are not
schema-qualified; they're "global" objects.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Wed, Dec 1, 2010 at 3:55 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Josh Berkus's message of mié dic 01 17:13:35 -0300 2010:
>>
>> > Well, porting applications from other database systems that support synonyms
>> > (i.e. Oracle, DB2, SQL Server).
>>
>> SQL Server supports synonyms?  If it's not Oracle-only, it's a more
>> powerful argument to have the feature.
>
> I think it's worth mentioning that in SQL Server, synonyms are not
> schema-qualified; they're "global" objects.

Seems like they have more than one kind.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7001.htm

The section entitled "notes on public synonyms" is particularly
interesting, as it seems to imply that under some but not all
conditions synonyms get materialized inside schemas that use them.
The list of objects for which they support synonyms is also
interesting.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Excerpts from Robert Haas's message of jue dic 02 17:27:01 -0300 2010:
> On Wed, Dec 1, 2010 at 3:55 PM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
> > Excerpts from Josh Berkus's message of mié dic 01 17:13:35 -0300 2010:
> >>
> >> > Well, porting applications from other database systems that support synonyms
> >> > (i.e. Oracle, DB2, SQL Server).
> >>
> >> SQL Server supports synonyms?  If it's not Oracle-only, it's a more
> >> powerful argument to have the feature.
> >
> > I think it's worth mentioning that in SQL Server, synonyms are not
> > schema-qualified; they're "global" objects.
>
> Seems like they have more than one kind.
>
> http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7001.htm

Yeah, the Oracle system is a lot more complex than SQL Server's, but I
was only talking about the latter, for which see here:

http://www.databasejournal.com/features/mssql/article.php/3635426/SYNONYM-in-SQL-Server-2005.htm

> The list of objects for which they support synonyms is also
> interesting.

The bit that allows a synonym to reference another synonym seems like
worth considering further (either reject them altogether, or have some
way to deal with possible cycles). I think the original proposal in
this thread didn't mention them at all.

(I don't think we should consider synonyms for either functions or
stored procedures; that would make the current mess of function
resolution rules a lot messier.)

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Thu, Dec 2, 2010 at 3:43 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Robert Haas's message of jue dic 02 17:27:01 -0300 2010:
>> On Wed, Dec 1, 2010 at 3:55 PM, Alvaro Herrera
>> <alvherre(at)commandprompt(dot)com> wrote:
>> > Excerpts from Josh Berkus's message of mié dic 01 17:13:35 -0300 2010:
>> >>
>> >> > Well, porting applications from other database systems that support synonyms
>> >> > (i.e. Oracle, DB2, SQL Server).
>> >>
>> >> SQL Server supports synonyms?  If it's not Oracle-only, it's a more
>> >> powerful argument to have the feature.
>> >
>> > I think it's worth mentioning that in SQL Server, synonyms are not
>> > schema-qualified; they're "global" objects.
>>
>> Seems like they have more than one kind.
>>
>> http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7001.htm
>
> Yeah, the Oracle system is a lot more complex than SQL Server's, but I
> was only talking about the latter, for which see here:
>
> http://www.databasejournal.com/features/mssql/article.php/3635426/SYNONYM-in-SQL-Server-2005.htm

Well, that seems primarily designed to cut down on three and four part
names. We don't have that problem anyway.

>> The list of objects for which they support synonyms is also
>> interesting.
>
> The bit that allows a synonym to reference another synonym seems like
> worth considering further (either reject them altogether, or have some
> way to deal with possible cycles).

It's pretty trivial to do cycle-detection at runtime.

> I think the original proposal in
> this thread didn't mention them at all.
>
> (I don't think we should consider synonyms for either functions or
> stored procedures; that would make the current mess of function
> resolution rules a lot messier.)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Excerpts from Robert Haas's message of jue dic 02 21:10:48 -0300 2010:
> On Thu, Dec 2, 2010 at 3:43 PM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
> > Excerpts from Robert Haas's message of jue dic 02 17:27:01 -0300 2010:

> > Yeah, the Oracle system is a lot more complex than SQL Server's, but I
> > was only talking about the latter, for which see here:
> >
> > http://www.databasejournal.com/features/mssql/article.php/3635426/SYNONYM-in-SQL-Server-2005.htm
>
> Well, that seems primarily designed to cut down on three and four part
> names. We don't have that problem anyway.

Right. (My point here is that SQL Server is not a good guidance on what
the synonym system should do.)

> >> The list of objects for which they support synonyms is also
> >> interesting.
> >
> > The bit that allows a synonym to reference another synonym seems like
> > worth considering further (either reject them altogether, or have some
> > way to deal with possible cycles).
>
> It's pretty trivial to do cycle-detection at runtime.

No disagreement on that, but something needs to be decided.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Dec 3, 2010, at 2:17 AM, Alvaro Herrera wrote:

> Excerpts from Robert Haas's message of jue dic 02 21:10:48 -0300 2010:
>> On Thu, Dec 2, 2010 at 3:43 PM, Alvaro Herrera
>> <alvherre(at)commandprompt(dot)com> wrote:
>>> Excerpts from Robert Haas's message of jue dic 02 17:27:01 -0300 2010:
>
>>> Yeah, the Oracle system is a lot more complex than SQL Server's, but I
>>> was only talking about the latter, for which see here:
>>>
>>> http://www.databasejournal.com/features/mssql/article.php/3635426/SYNONYM-in-SQL-Server-2005.htm
>>
>> Well, that seems primarily designed to cut down on three and four part
>> names. We don't have that problem anyway.
>
> Right. (My point here is that SQL Server is not a good guidance on what
> the synonym system should do.)
>
>>>> The list of objects for which they support synonyms is also
>>>> interesting.
>>>
>>> The bit that allows a synonym to reference another synonym seems like
>>> worth considering further (either reject them altogether, or have some
>>> way to deal with possible cycles).
>>
>> It's pretty trivial to do cycle-detection at runtime.
>
> No disagreement on that, but something needs to be decided.

I don't think it makes sense to allow synonyms for synonyms. It would make
resolution code slower, and I don't see any situation where they make sense.
The original proposal didn't mention them, but limited the list of initially
supported objects to those to tables, views and sequences, implicitly
excluding synonyms referring to another synonyms.

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