Thread: Proposal for SYNONYMS

<p style="margin-bottom: 0in;">This email is a preliminary design for the implementation of synonyms in PostgreSQL. 
Commentsand suggestions are welcomed.<br /><p style="margin-bottom: 0in;">BACKGROUND<p style="margin-bottom:
0in;">Synonymsare database objects which can be used in place of their referenced object in SELECT, INSERT, UPDATE, and
DELETESQL statements.<p style="margin-bottom: 0in;">There are two reasons to use synonyms which include:<p
style="margin-bottom:0in;">- Abstraction from changes made to the name or location of database objects<br /> -
Alternativenaming for another database object<p style="margin-bottom: 0in;">Similarly, RDBMS support for synonyms
existsin Oracle, SQL Server, DB2, SAP DB/MAX DB, and Mimer. <p style="margin-bottom: 0in;">PROPOSED SQL ADDITIONS<p
style="margin-bottom:0in;">CREATE SYNONYM qualified_name FOR qualified_name<br /> DROP SYNONYM qualified_name<p
style="margin-bottom:0in;">In addition, SYNONYMS do participate in ACLs and support GRANT/REVOKE for table privileges.
DROPTABLE and TRUNCATE cannot be used with synonyms.<p style="margin-bottom: 0in;">DESCRIPTION<p style="margin-bottom:
0in;">-A synonym can be created for a table, view, or synonym.<br /> - Synonyms can reference objects in any schema<p
style="margin-bottom:0in;">RESTRICTIONS<p style="margin-bottom: 0in;">- A synonym may only be created if the creator
hassome access privilege on the referenced object.<br /> - A synonym can only be created for an existing table, view or
synonym.<br/> - A synonym name cannot be the same as the name of any other table, view or synonym which exists in the
schemawhere the synonym is to be created. <p style="margin-bottom: 0in;">PROPOSED IMPLEMENTATION<p
style="margin-bottom:0in;">- Introduce a new relkind for synonyms<br /> - Synonyms only act as pointers to a real
objectby oid<br /> - Permission on a synonym does not override the permission on the referenced object<br /> -
Referencedobjects becomes dependencies of the synonyms that reference them<br /> - Synonyms follow PostgreSQL's current
search_pathbehavior<p style="margin-bottom: 0in;">RUNTIME COST<p style="margin-bottom: 0in;">- Dependent on database
user/administrator<br/> - In catalog searches which do not reference a synonym, the only cost incurred is that of
searchingthe additional number of synonym objects in the catalog<br /> - In catalog searches which use a synonym, an
additionalcost is incurred to reference the real object<br /> - If no synonyms are created, no additional costs are
incurred<br/><br />-- <br />Jonah H. Harris, Database Internals Architect<br />EnterpriseDB Corporation<br
/>732.331.1324<br/> 

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

* 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

* 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

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

* 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

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

Stephan Szabo <sszabo@megazone.bigpanda.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@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.

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

--elein
elein@varlena.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@varlena.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@megazone.bigpanda.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@megazone.bigpanda.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@agliodbs.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 Donnerstag, März 09, 2006 17:23:11 -0500 Tom Lane <tgl@sss.pgh.pa.us>
wrote:

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

It has stalled because I want more than SQL92 to be implemented...but
that's another story. I've sent my latest patches against HEAD to -hackers
for discussion.

       Bernd

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