Thread: 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@commandprompt.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@commandprompt.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@commandprompt.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
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@agliodbs.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@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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 Wed, Dec 1, 2010 at 3:55 PM, Alvaro Herrera <alvherre@commandprompt.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@commandprompt.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@commandprompt.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@commandprompt.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@commandprompt.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@commandprompt.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@commandprompt.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@commandprompt.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