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
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
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