Proposal for SYNONYMS

From: "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Proposal for SYNONYMS
Date: 2006-03-09 16:35:55
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

This email is a preliminary design for the implementation of synonyms in
PostgreSQL. Comments and suggestions are welcomed.


Synonyms are database objects which can be used in place of their referenced
object in SELECT, INSERT, UPDATE, and DELETE SQL statements.

There are two reasons to use synonyms which include:

- Abstraction from changes made to the name or location of database objects
- Alternative naming for another database object

Similarly, RDBMS support for synonyms exists in Oracle, SQL Server, DB2, SAP
DB/MAX DB, and Mimer.


CREATE SYNONYM qualified_name FOR qualified_name
DROP SYNONYM qualified_name

In addition, SYNONYMS do participate in ACLs and support GRANT/REVOKE for
table privileges. DROP TABLE and TRUNCATE cannot be used with synonyms.


- A synonym can be created for a table, view, or synonym.
- Synonyms can reference objects in any schema


- A synonym may only be created if the creator has some access privilege on
the referenced object.
- A synonym can only be created for an existing table, view or synonym.
- A synonym name cannot be the same as the name of any other table, view or
synonym which exists in the schema where the synonym is to be created.


- Introduce a new relkind for synonyms
- Synonyms only act as pointers to a real object by oid
- Permission on a synonym does not override the permission on the referenced
- Referenced objects becomes dependencies of the synonyms that reference
- Synonyms follow PostgreSQL's current search_path behavior


- Dependent on database user/administrator
- In catalog searches which do not reference a synonym, the only cost
incurred is that of searching the additional number of synonym objects in
the catalog
- In catalog searches which use a synonym, an additional cost is incurred to
reference the real object
- If no synonyms are created, no additional costs are incurred

Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation


Browse pgsql-hackers by date

  From Date Subject
Next Message Stefan Kaltenbrunner 2006-03-09 16:36:32 Re: problem with large maintenance_work_mem settings and
Previous Message Jim C. Nasby 2006-03-09 16:35:52 Re: Merge algorithms for large numbers of "tapes"