Another proposal for table synonyms

Hello,

Here is the proposal to add synonyms to PostgreSQL. Initial goal is to add synonyms
for relations (tables, views, sequences) and an infrastructure to allow synonyms
for other database objects in the future.

A thread with discussion of an old proposal by Jonah Harris is here:
http://archives.postgresql.org/pgsql-hackers/2006-03/msg00519.php

-----
Synonyms are database objects, which act as an alias for other objects. In
this proposal the synonyms for tables, views and sequences will be considered.

The new command, CREATE SYNONYM, defines a synonym. The syntax is:

CREATE SYNONYM synonym_name FOR object_type object_name
where
synonym_name: fully-qualified name (FQN) of the synonym
object_type: {TABLE | VIEW | SEQUENCE}. In the future, new object_types, such as
functions, can be added.
object_name:  FQN of a database table, view or sequence.

Another new command, DROP SYNONYM, is used for deleting an already existing
synonym without removing the object the synonym references. The syntax is:

DROP SYNONYM synonym_name
where synonym_name is a FQN of a synonym.

Comments will be supported on synonyms with the following command:
COMMENT ON SYNONYM synonym_name IS comment_text
where synonym_name is a FQN of a synonym.

To support addition of new database objects types that can be referenced by
synonyms a new system catalog, pg_synonym, is to be added, with an oid to
support comments on synonym, and the following schema:

synname  name  name of the synonym
synnamespace  oid  OID of the namespace that contains the synonym
synclassid  oid  OID of the system catalog that contains the  referenced object
synobjid   oid  OID of the referenced object

When resolving the synonym name, the usual search_path lookup rules apply,
i.e. first, the object of the appropriate type is looked into the schema, then
the synonym, afterwards the process iterates with the next schema from the
search_path. Note that the table synonym with the same FQN as an existing
table will be masked by that table.

To speedup the synonym name resolution a new syscache, SYNNAMENSPCLASS
{synname, synnamespace, synclassid} will be introduced. This cache will be
accessed if the query to the RELNAMENSP syscache will return no result, with
the DB object's catalog OID set to pg_class OID.

For table and view synonyms, INSERT/UPDATE/DELETE/SELECT will be supported.
For sequences SELECT will be supported. The commands will translate synonyms
to the referenced database objects on the parser stage.

All types of synonyms will be supported as table arguments/return value types,
as well as actual values (i.e. currval/nextval will accept a sequence
synonym).

The following DDL will work transparently with table synonyms (sequences and
views if the corresponding command applies to them):
COPY, LOCK, TRUNCATE, EXPLAIN, EXPLAIN ANALYZE.

The following DDL commands will cause an error when called for tables
(sequences, views) synonyms:
ALTER {TABLE|VIEW|SEQUENCE},
ANALYZE,
CLUSTER,
COMMENT ON {TABLE | VIEW | SEQUENCE} .. IS,
DROP {TABLE | VIEW | SEQUENCE},
GRANT,
REVOKE,
VACUUM.
For these commands additional checks for synonyms will be introduced on a
per-command basis.

A dependency of the referenced object on a synonym will be added when adding a
new synonym to forbid removing a referenced object without removing the
synonym first (without using CASCADE). On DROP SYNONYM the related dependency
will be removed.

--
Alexey Klyukin                    http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc