Re: Proposal for SYNONYMS

Ühel kenal päeval, N, 2006-03-09 kell 11:35, kirjutas Jonah H. Harris:
> 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

I would like to be able to also have synonyms for DATABASEs, that way
all kinds on online migration tasks should be easier.

so the syntax would be

CREATE SYNONYM qualified_name FOR {TABLE|DATABASE} qualified_name;

> DROP SYNONYM qualified_name
>
> In addition, SYNONYMS do participate in ACLs and support GRANT/REVOKE
> for table privileges.

Why separate ACL's for synonyms. I'd rather like them to be like unix
filenames - any change of permissions on synonym actually changes
permissions for underlying object. synonyms themselves should be
ACL-less.

> DROP TABLE and TRUNCATE cannot be used with synonyms.

I understand why no DROP TABLE, but why forbid TRUNCATE ?

> DESCRIPTION
>
> - A synonym can be created for a table, view, or synonym.

will as synonym created on antother synonym internally reference that
other synonym, or directly the final object. I'd prefer the latter, as
this will be cheaper when accessing the object throug synonym, and also
(arguably) clearer/cleaner.

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

Aha, so they act like links, not like symlinks

> - Permission on a synonym does not override the permission on the
> referenced object

So there is no need for separate permissions on synonym. Or is there
some use-case for it ?

> - Referenced objects becomes dependencies of the synonyms that
> reference them
> - Synonyms follow PostgreSQL's current search_path behavior

---------------
Hannu