Thread: SYNONYMs revisited
Way back in this thread[1] one of the arguments against allowing some version of CREATE SYNONYM was that we couldn't create a synonym for an object in a remote database. Will the SQL/MED work make this sort of thing a possibility? I realize since it's not standard anyway, there's still a discussion or two to be had about how precisely it should work, but thought I'd raise the possibility. - Josh / eggyknap [1] http://archives.postgresql.org/pgsql-patches/2006-03/msg00085.php
Joshua Tolley wrote: > Way back in this thread[1] one of the arguments against allowing > some version of CREATE SYNONYM was that we couldn't create a synonym for > an object in a remote database. Will the SQL/MED work make this sort of > thing a possibility? I realize since it's not standard anyway, there's > still a discussion or two to be had about how precisely it should work, > but thought I'd raise the possibility. > > - Josh / eggyknap > > [1] http://archives.postgresql.org/pgsql-patches/2006-03/msg00085.php > I still think that this is a useful feature. If i remember correctly there were two killer arguments against this: - it encourages people to do "lousy development" - extra overhead personally i think that this won't be revisted - i cannot see a real connection to SQL/MED here. best regards, hans -- Cybertec Schönig & Schönig GmbH PostgreSQL Support, Consulting, Training www.postgresql-support.de
On Wed, Mar 04, 2009 at 10:14:41AM -0500, Jonah H. Harris wrote: > SQL/MED does support foreign tables, which are basically synonyms for > remote tables. Other than that, it has no real similarity to synonym > behavior for other database objects such as views, functions, or local > tables. I didn't mean to suggest that SQL/MED on its own could be used to make SYNONYMs, but rather that given SQL/MED, perhaps we could reconsider some sort of CREATE SYNONYM functionality to go along with it. A major argument against CREATE SYNONYM in the past was that we wouldn't be able to create synonyms representing remote objects because we couldn't access remote objects. With SQL/MED that's no longer the case, so perhaps that argument no longer applies. - Josh / eggyknap
Joshua Tolley <eggyknap@gmail.com> writes:
> I didn't mean to suggest that SQL/MED on its own could be used to make
> SYNONYMs, but rather that given SQL/MED, perhaps we could reconsider
> some sort of CREATE SYNONYM functionality to go along with it. A major
> argument against CREATE SYNONYM in the past was that we wouldn't be able
> to create synonyms representing remote objects because we couldn't
> access remote objects. With SQL/MED that's no longer the case, so
> perhaps that argument no longer applies.
Well, we're still a long way from having SQL/MED ;-). In particular,
one of the elements of that spec is CREATE FOREIGN TABLE, which I think
basically *is* a synonym for a table on a remote server.
regards, tom lane
On Wednesday 04 March 2009 16:34:54 Joshua Tolley wrote: > Way back in this thread[1] one of the arguments against allowing > some version of CREATE SYNONYM was that we couldn't create a synonym for > an object in a remote database. Will the SQL/MED work make this sort of > thing a possibility? The idea is that SQL/MED will create objects known as foreign tables that represent remote resources but work locally like a normal table. So in some sense, SQL/MED already creates a local synonym-type thing for remote resources anyway, so there is no need to create a separate synonym in the Oracle sense on top of that.
On Wed, Mar 04, 2009 at 03:15:23PM -0500, Tom Lane wrote: > Joshua Tolley <eggyknap@gmail.com> writes: > > I didn't mean to suggest that SQL/MED on its own could be used to make > > SYNONYMs, but rather that given SQL/MED, perhaps we could reconsider > > some sort of CREATE SYNONYM functionality to go along with it. A major > > argument against CREATE SYNONYM in the past was that we wouldn't be able > > to create synonyms representing remote objects because we couldn't > > access remote objects. With SQL/MED that's no longer the case, so > > perhaps that argument no longer applies. > > Well, we're still a long way from having SQL/MED ;-). In particular, > one of the elements of that spec is CREATE FOREIGN TABLE, which I think > basically *is* a synonym for a table on a remote server. I hadn't followed SQL/MED to really see where we were; I just know that being able to create a synonym for a function, a table, a view, etc. seems like it would be neat (though I can't admit to having a list of use cases, or a good argument for any particular interpretation of its correct behavior). Since one concern expressed was that people might expect to be able to create synonyms of foreign objects, and dismayed to find they couldn't, perhaps having SQL/MED (one day) would remove concerns about building some form of CREATE SYNONYM. - Josh
On Wed, Mar 4, 2009 at 9:34 AM, Joshua Tolley <eggyknap@gmail.com> wrote:
While shaking my head In that movie-like slow-motion used as a precursor to an almost disastrous event, I see myself saying, "nooooooooooooo..."
Way back in this thread[1] one of the arguments against allowing
some version of CREATE SYNONYM was that we couldn't create a synonym for
an object in a remote database. Will the SQL/MED work make this sort of
thing a possibility? I realize since it's not standard anyway, there's
still a discussion or two to be had about how precisely it should work,
but thought I'd raise the possibility.
While shaking my head In that movie-like slow-motion used as a precursor to an almost disastrous event, I see myself saying, "nooooooooooooo..."
OK, back to reality.
SQL/MED does support foreign tables, which are basically synonyms for remote tables. Other than that, it has no real similarity to synonym behavior for other database objects such as views, functions, or local tables.
--
Jonah H. Harris, Senior DBA
myYearbook.com