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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On 3/9/06, Stephan Szabo <sszabo@megazone.bigpanda.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@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

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

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Ü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




---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

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

iD8DBQFEEMB+rzgMPqB3kigRAn7KAJ94Tp7Jd4Brhsu3oUZSlY qkWark4wCdGemt
UHsRAh6Do3vlHOQkQi1B0u8=
=G5wX
-----END PGP SIGNATURE-----

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.

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

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org