On 3/7/06, Tom Lane <[EMAIL PROTECTED]> wrote:
(Actually, I don't think the case for table synonyms has been made
adequately either; "Oracle has it" is *not* enough reason to take on
another feature that we'll have to maintain forever, especially given
that we're being told that one of the major use-cases for synonyms
isn't going to be supported.  AFAICS this patch does nothing you
couldn't do much better with a quick search-and-replace over your
application code.  In short, I remain unsold.)

I agree with this to some extent.

The main use case, aside from database link objects, is really for generally large applications such as a large ERP system.  Most ERP systems have a general or foundation-like schema where common objects lie and each module is separated using schemas.

As an example, you would have HR, AP, AR, GL, FA, COMMON, ... schemas which encapsulate the functionality of their respective modules whether it be procedures, functions, views, tables, etc.  For each module to be able to access, for example, the HR.EMPLOYEE table, they generally refer to just EMPLOYEE which is a synonym to HR.EMPLOYEE.

Now, one may argue that it's incorrect/bad application-design to not use fully qualified names, however, there are cases (especially in VERY large database applications) where you do not want to use fully qualified naming.  In PostgreSQL, the alternative to synonyms is to have a monstrous search path $user, public, HR, AP, AR, GL, FA, COMMON...  Not that we have Oracle Applications running on PostgreSQL, but 11i has something like 130+? schemas which would be pretty nasty and semi-unprofessional as a search_path rather than as something defined similar to synonyms.  Another consideration is poor application design which uses the same named table in one schema which acts differently than the same named table in another schema... synonyms resolve this issue which could be problematic if not impossible to solve using search_path alone.

Without the database link case, the functional reason for not using search_path is surely reduced but it is in no way wholly eliminated either.  Some users don't have the ability to choose how vendors/developers write their software and they can't easily just convert an entire application to use search_path where they once had synonyms (especially if the application is fairly sizable).

Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation

Reply via email to