Re: CREATE SYNONYM ...

On 3/7/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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
732.331.1324