Re: CREATE SYNONYM ...

On 3/8/06, Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
>
> Yes, however there are two slightly separate discussions going on and I
> think you're taking them as a single discussion.

I agree that there are two discussions happening in this thread, but I don't
think anyone has agreed at all that this patch as it is would be acceptable
for various reasons. There are a couple things that Hans and I will discuss
about the patch assuming we decide this is a feature that would be nice for
PostgreSQL.

If your search path is A,B and there is a B.EMPLOYEE table and an
> A.EMPLOYEE synonym to HR.EMPLOYEE, which table does select * from EMPLOYEE
> read?

The one first in your search path. You could not, for example, create a
SYNONYM called EMPLOYEE in the HR schema as it would conflict with the
EMPLOYEE table. Synonyms act like the objects they represent in term of
namespace searches.

ASSUME:
CREATE USER joe;
CREATE SCHEMA AUTHORIZATION joe;

Joe's search_path is $user,public

CREATE SCHEMA hr;
CREATE TABLE hr.employee (emp_id, ...)
CREATE TABLE hr.payroll (emp_id, ...)
CREATE TABLE hr.commissions;

For Joe to see this, they either have to add HR to their search_path or
fully qualify it. Let's assume they use current PostgreSQL behavior:

SET search_path TO ..., HR

Now they can SELECT * FROM EMPLOYEE where EMPLOYEE is HR.EMPLOYEE

Now assume:

CREATE SCHEMA crm;
CREATE TABLE crm.employee;
CREATE TABLE crm.customer;
CREATE TABLE crm.commissions;

Now, joe needs to query customer and employee without qualification...
HR.EMPLOYEE is the common table that, with the exception of the CRM module,
the application refers to simply as EMPLOYEE. Now what does Joe do:

SET search_path TO ..., HR, CRM;

OK, they still have the tables named correctly but they have to manually
make sure they order search_path. Now, you tell me (without qualification)
how Joe can access the CRM commissions table? They can't.

With synonyms, the search path for Joe would remain $user, public and one
could easily do
CREATE SYNONYM public.employee FOR hr.employee;
CREATE SYNONYM public.commissions FOR crm.commissions;

As Joe: SELECT * FROM EMPLOYEE becomes SELECT * FROM HR.EMPLOYEE
As Joe: SELECT * FROM COMMISSIONS becomes SELECT * FROM CRM.COMMISSIONS

I guess synonym searching could be done iff no object were found in the
current search. I don't know why I thought it would be just as costly
(perhaps too much Sam Adams). The worst-case scenario would be an
additional search only if an object weren't found in a catalog search,
basically this would be the cost of using synonyms and wouldn't affect
performance for everyone else. Oracle does have a small cost only when
using synonyms as well.

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