SEO

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

SYNONYMS are symbolinks in database?
CREATE SYNONYMS bar FOR foo;
DROP TABLE foo;
Now bar point to an invalid object. Or should we let
DROP TABLE foo CASCADE;
to drop the SYNONYMS depended on the table?

Also need to add \d support for psql.

Regards,
William ZHANG

On 3/9/06, William ZHANG <uniware@zedware.org> wrote:
>
> Or should we let
> DROP TABLE foo CASCADE;
> to drop the SYNONYMS depended on the table?


Yes, I don't see any reason not to allow a cascading table drop include
synonyms that reference them.

Also need to add \d support for psql.


Yes. Thanks for adding that.


--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

* Jonah H. Harris (jonah.harris@gmail.com) wrote:
> In addition, SYNONYMS do participate in ACLs and support GRANT/REVOKE for
> table privileges. DROP TABLE and TRUNCATE cannot be used with synonyms.

I assume you actually mean "owner-level rights cannot be used with
synonyms".

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

Need to be careful here and also make sure schema-level permissions
aren't able to be circumvented.

Sounds good to me in general though.

Thanks!

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFEEGK4rzgMPqB3kigRAtefAJ94TbeKutSl8ksyZrEcNX Hookp/GQCfX+5Y
YFrjsaxbOJiYmxuSzf8nfE4=
=BHLW
-----END PGP SIGNATURE-----

* Jonah H. Harris (jonah.harris@gmail.com) wrote:
> On 3/9/06, William ZHANG <uniware@zedware.org> wrote:
> > Or should we let
> > DROP TABLE foo CASCADE;
> > to drop the SYNONYMS depended on the table?
>
> Yes, I don't see any reason not to allow a cascading table drop include
> synonyms that reference them.

Should a non-cascade drop fail or just implicitly drop the synonyms?
I'm not sure which way I feel about this... Users with only 'select'
permissions on a given object can't currently create objects which
depend on that object (such that dropping the object would then require
'cascade'), can they?

I'd tend to think the synonyms should just be implicitly dropped. The
creator of the table doesn't necessairly have any knowledge (or care)
about synonyms which anyone with access to the table could have
created...

Thanks,

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFEEGNjrzgMPqB3kigRAoudAJ4pYTy5Ivc7LlFpfwAQ0C 05Ki5P5QCfUm2c
BAZuhlqqY7cNa0FXZeoNmvQ=
=vf2f
-----END PGP SIGNATURE-----

Jonah H. Harris wrote:
>
>
> 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
>


hi jonah ...

the main problem i can see here is that it is strictly limited to
objects stored in pg_class.
however, support for stored procedures would be cool as well. what do
you suggest for those?

best regards,

hans


--
Cybertec Geschwinde & Schönig GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Thu, 9 Mar 2006, Stephen Frost wrote:

> * Jonah H. Harris (jonah.harris@gmail.com) wrote:
> > On 3/9/06, William ZHANG <uniware@zedware.org> wrote:
> > > Or should we let
> > > DROP TABLE foo CASCADE;
> > > to drop the SYNONYMS depended on the table?
> >
> > Yes, I don't see any reason not to allow a cascading table drop include
> > synonyms that reference them.
>
> Should a non-cascade drop fail or just implicitly drop the synonyms?
> I'm not sure which way I feel about this... Users with only 'select'
> permissions on a given object can't currently create objects which
> depend on that object (such that dropping the object would then require
> 'cascade'), can they?

I think a user can create a view to a table they only have select on right
now and that should prevent non-cascade drops as well.


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

* Stephan Szabo (sszabo@megazone.bigpanda.com) wrote:
> On Thu, 9 Mar 2006, Stephen Frost wrote:
> > Should a non-cascade drop fail or just implicitly drop the synonyms?
> > I'm not sure which way I feel about this... Users with only 'select'
> > permissions on a given object can't currently create objects which
> > depend on that object (such that dropping the object would then require
> > 'cascade'), can they?
>
> I think a user can create a view to a table they only have select on right
> now and that should prevent non-cascade drops as well.

Hmm, alright, fair enough.

Thanks,

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFEEHFFrzgMPqB3kigRAiJGAJwPgHJPX/leXnVDaVV7vFn0OKm6kwCfQDbO
vvRUXV6s9OEXYez1gSZzcYU=
=9K20
-----END PGP SIGNATURE-----

Jonah,

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

1) Is there a SQL standard for this?

2) For my comprehension, what's the difference between a SYNONYM and a
single-object (possibly updatable) view?

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On 3/9/06, Josh Berkus <josh@agliodbs.com> wrote:
>
> 1) Is there a SQL standard for this?


Nope.

2) For my comprehension, what's the difference between a SYNONYM and a
> single-object (possibly updatable) view?


Not a whole lot actually. If we had updateable views, I'd suggest that
people change their create synonym syntax to create view. However, it would
take substantially more work to implement updatable views than synonyms and
the functionality of updatable views is substantially different than the use
of synonyms alone. If/when updatable views are implemented, I wouldn't have
a problem switching create synonym to actually create a view.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324