Re: CREATE SYNONYM ... - Mailing list pgsql-patches

Yes, however there are two slightly separate discussions going on and I
think you're taking them as a single discussion.

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.