Proposal for SYNONYMS

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(at)zedware(dot)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(dot)harris(at)gmail(dot)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

* Jonah H. Harris (jonah(dot)harris(at)gmail(dot)com) wrote:
> On 3/9/06, William ZHANG <uniware(at)zedware(dot)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

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

On Thu, 9 Mar 2006, Stephen Frost wrote:

> * Jonah H. Harris (jonah(dot)harris(at)gmail(dot)com) wrote:
> > On 3/9/06, William ZHANG <uniware(at)zedware(dot)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.

* Stephan Szabo (sszabo(at)megazone(dot)bigpanda(dot)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

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

On 3/9/06, Josh Berkus <josh(at)agliodbs(dot)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

On Thu, 9 Mar 2006, Josh Berkus wrote:

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

I think with the plan as described, the permissions handling is slightly
different from how we handle views. As I understood the synonym plan, a
person with select on the synonym but not on the referenced table wouldn't
be able to select through the synonym, while if the view was created by
someone with select a person with select on the view could select through
the view.

On 3/9/06, Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
>
> As I understood the synonym plan, a
> person with select on the synonym but not on the referenced table wouldn't
> be able to select through the synonym, while if the view was created by
> someone with select a person with select on the view could select through
> the view.

In this respect, synonyms are surely different from views. Due to this, I
was pondering whether synonyms should have ACLs or whether they just pointed
to the object and ACLs were handled as they currently are. I didn't think
of a use case for them being different, but I know three of the RDBMS
vendors did implement them to have their own permissions, so there's gotta
be some reason for it. I'm guessing the reason is for accessing remote
database tables which isn't part of this proposal, however, it's generally
easier to add it now than later. I'm not averse to removing ACLs from
synonyms right now at all as we'd still benefit from the same functionality.

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

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> On Thu, 9 Mar 2006, Josh Berkus wrote:
>> 2) For my comprehension, what's the difference between a SYNONYM and a
>> single-object (possibly updatable) view?

> I think with the plan as described, the permissions handling is slightly
> different from how we handle views. As I understood the synonym plan, a
> person with select on the synonym but not on the referenced table wouldn't
> be able to select through the synonym, while if the view was created by
> someone with select a person with select on the view could select through
> the view.

I was under the impression that privileges on the synonym wouldn't mean
anything at all, with the exception that we'd track its ownership to
determine who is allowed to drop the synonym.

The point about views is a good one. I don't buy the argument that
"we should do synonyms instead of updatable views because it's easier".
We *will* do updatable views at some point because (a) the spec requires
it and (b) it's clearly useful. I'm not eager to be stuck with synonyms
forever because somebody thought they could implement one and not the
other.

(BTW, there was some work being done on updatable views, but I think
it's stalled. I suspect the reason is that our current rule system
is just too odd to support updatable views reasonably. I've been
wondering if an implementation based on allowing triggers on views
would be any more manageable.)

regards, tom lane

On Thu, Mar 09, 2006 at 04:42:43PM -0500, Jonah H. Harris wrote:
> On 3/9/06, Josh Berkus <josh(at)agliodbs(dot)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.

Since updateable views are relatively easy to construct using rules
I'm not sure an entire new syntax is necessary.

--elein
elein(at)varlena(dot)com

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

Ü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

* elein (elein(at)varlena(dot)com) wrote:
> On Thu, Mar 09, 2006 at 04:42:43PM -0500, Jonah H. Harris wrote:
> > 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.
>
> Since updateable views are relatively easy to construct using rules
> I'm not sure an entire new syntax is necessary.

They're not all that easy to construct and they require constant
maintenance. If they're not maintained and the underlying table changes
in some way they can end up doing the wrong thing and causing suprises.

Thanks,

Stephen

On Thu, 9 Mar 2006, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> > On Thu, 9 Mar 2006, Josh Berkus wrote:
> >> 2) For my comprehension, what's the difference between a SYNONYM and a
> >> single-object (possibly updatable) view?
>
> > I think with the plan as described, the permissions handling is slightly
> > different from how we handle views. As I understood the synonym plan, a
> > person with select on the synonym but not on the referenced table wouldn't
> > be able to select through the synonym, while if the view was created by
> > someone with select a person with select on the view could select through
> > the view.
>
> I was under the impression that privileges on the synonym wouldn't mean
> anything at all, with the exception that we'd track its ownership to
> determine who is allowed to drop the synonym.
>
> The point about views is a good one. I don't buy the argument that
> "we should do synonyms instead of updatable views because it's easier".
> We *will* do updatable views at some point because (a) the spec requires
> it and (b) it's clearly useful. I'm not eager to be stuck with synonyms
> forever because somebody thought they could implement one and not the
> other.

Well, the permissions handling would still be different between a view and
a synonym AFAICS even if we dropped separate permissions on synonyms, so I
don't think they're drop in replacements for each other even after
updatable views.

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> Well, the permissions handling would still be different between a view and
> a synonym AFAICS even if we dropped separate permissions on synonyms, so I
> don't think they're drop in replacements for each other even after
> updatable views.

Agreed, but given the fact that we seem to be inventing permissions
behavior for synonyms on the spur of the moment, I'm not convinced that
there's anything there that anyone should put great credence in. The
permissions behavior for views is at least standardized ...

regards, tom lane

Tom,

> (BTW, there was some work being done on updatable views, but I think
> it's stalled. I suspect the reason is that our current rule system
> is just too odd to support updatable views reasonably. I've been
> wondering if an implementation based on allowing triggers on views
> would be any more manageable.)

Eh? I thought that it was just syntatic sugar that was missing. I've
built lots of updatable views manually; I don't see what's difficult about
it.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Eh? I thought that it was just syntatic sugar that was missing. I've
> built lots of updatable views manually; I don't see what's difficult about
> it.

I think you'll find that corner cases like inserts involving nextval()
don't work real well with a rule-based updatable view. But perhaps I'm
just scarred by the many complaints we've had about rules. With a plain
unconditional DO INSTEAD rule it might be OK ...

regards, tom lane

On Thu, 9 Mar 2006, Jonah H. Harris wrote:

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

One key difference would be that synonyms track schema updates, like
adding a column, to the referenced object that a view would not.

Kris Jurka

Kris Jurka <books(at)ejurka(dot)com> writes:
> One key difference would be that synonyms track schema updates, like
> adding a column, to the referenced object that a view would not.

That raises a fairly interesting point, actually. What would you expect
to happen here:

CREATE TABLE foo ...;
CREATE SYNONYM bar FOR foo;
CREATE VIEW v AS SELECT * FROM bar;
DROP SYNONYM bar;

With the implementations being proposed, v would effectively be stored
as "SELECT * FROM foo" and thus would be unaffected by the DROP SYNONYM.
Is that what people will expect? Is it what happens in Oracle?

regards, tom lane

On fös, 2006-03-10 at 16:51 -0500, Tom Lane wrote:
> Kris Jurka <books(at)ejurka(dot)com> writes:
> > One key difference would be that synonyms track schema updates, like
> > adding a column, to the referenced object that a view would not.
>
> That raises a fairly interesting point, actually. What would you expect
> to happen here:
>
> CREATE TABLE foo ...;
> CREATE SYNONYM bar FOR foo;
> CREATE VIEW v AS SELECT * FROM bar;
> DROP SYNONYM bar;
>
> With the implementations being proposed, v would effectively be stored
> as "SELECT * FROM foo" and thus would be unaffected by the DROP SYNONYM.
> Is that what people will expect? Is it what happens in Oracle?

At least on Oracle8, you could create a synonym on a
non-existing table, so if table FOO does not exist:

CREATE SYNONYM BAR FOR FOO; -- no error
SELECT * FROM BAR; -- error "synonym translation is no longuer valid"
CREATE TABLE FOO (a varchar2(10));
INSERT INTO FOO VALUES ('a');
SELECT * FROM BAR; -- no error
CREATE VIEW X AS SELECT * FROM BAR;
SELECT * FROM X; -- no error
DROP SYNONYM X; -- no error
SELECT * FROM BAR; -- error

gnari