This email is a preliminary design for
the implementation of synonyms in PostgreSQL. Comments and suggestions are welcomed.
BACKGROUND
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.
PROPOSED SQL ADDITIONS
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.
DESCRIPTION
- A synonym can be created for a table,
view, or synonym.
- Synonyms can reference objects in any
schema
RESTRICTIONS
- 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.
PROPOSED IMPLEMENTATION
- 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 object
- Referenced objects becomes
dependencies of the synonyms that reference them
- Synonyms follow PostgreSQL's current
search_path behavior
RUNTIME COST
- 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
732.331.1324
