Re: [HACKERS] Another proposal for table synonyms
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 alvhe...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another proposal for table synonyms
On Wed, Dec 1, 2010 at 3:55 PM, Alvaro Herrera alvhe...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another proposal for table synonyms
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 alvhe...@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 alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another proposal for table synonyms
On Thu, Dec 2, 2010 at 3:43 PM, Alvaro Herrera alvhe...@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 alvhe...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another proposal for table synonyms
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 alvhe...@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 alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another proposal for table synonyms
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another proposal for table synonyms
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another proposal for table synonyms
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another proposal for table 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. Have you had an employment change I didn't know about, JD? ;-) -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another proposal for table synonyms
On Wed, Dec 1, 2010 at 8:46 PM, Josh Berkus j...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another proposal for table synonyms
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another proposal for table synonyms
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 alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another proposal for table synonyms
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another proposal for table synonyms
Alexey Klyukin al...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another proposal for table synonyms
On Nov 30, 2010, at 6:28 PM, Tom Lane wrote: Alexey Klyukin al...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another proposal for table synonyms
Alexey Klyukin al...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another proposal for table synonyms
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
