Re: CREATE SYNONYM ...

On 3/8/06, Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
>
> Doesn't that pretty much go against the (I thought) outstanding behavioral
> question of whether the synonyms are scoped and obey search path? If they
> do, I don't see how the above rule can hold since finding the "real table"
> is insufficient to know if there's an earlier synonym.
>

There is a cost for synonyms no matter how you look at it.

Assume your user has it's own schema, that there is a synonym in public for
EMPLOYEE which pointed to HR.EMPLOYEE, and your search path is
$user,public. If you do a SELECT * FROM EMPLOYEE, the search order is still
the same as it is in PostgreSQL now, there's no EMPLOYEE table in the $user
schema, so when it gets to searching public, it finds the synonym. The only
alternative in this scenario is to create the EMPLOYEE table in public
(which is pretty stupid in most cases), or to set the search path to
$user,public,hr. Again, this doesn't cover the "same-named tables in
multiple schemas" argument, but it does illustrate that PostgreSQL's
namespace scoping remains the same.

The question is whether we want to offer the functionality and what the
least intrusive way to handle it is.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324