SYNONYMs revisited

On Wed, Mar 4, 2009 at 9:34 AM, Joshua Tolley <eggyknap@gmail.com> 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.

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

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

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