I have a need for relation synonyms in PostgreSQL. I don't see it in 
7.2.1 but the catalog seems to be able to support it more or less.

Here's what I intend to do:

1) Create a duplicate record in pg_class for the base table information 
but with the relname set to the synonym name.

2) Duplicate the attribute information in pg_attribute for the base 
table but with the attrelid set to the synonym oid.

(see test SQL below)

Is there anything fundamentally wrong with this approach? In particular, 
could this concievably break anything. I do understand that it's not a 
perfect approach since the attributes are not dynamic in so far as any 
changes made to the base table. However, it does appear to provide a 
superior solution than using a view with a full set of rules. That said, 
is there a safe way of creating a "true" duplicate record in pg_class 
(including the oid) so that a "true" synonym could be created?


Here's the testing I did:

insert into pg_class
select 'syn_test', reltype, relowner, relam, relfilenode, relpages, 
reltuples, reltoastrelid, reltoastidxid,
relhasindex, relisshared, relkind, relnatts, relchecks, reltriggers, 
relukeys, relfkeys,
relrefs, relhasoids, relhaspkey, relhasrules, relhassubclass, relacl
from pg_class where lower(relname) = lower('tbl_test')
;

insert into pg_attribute
select c2.oid, attname, atttypid, attstattarget, attlen, attnum, 
attndims, attcacheoff, atttypmod, attbyval,
attstorage, attisset, attalign, attnotnull, atthasdef
from pg_class c1, pg_class c2, pg_attribute a1
where attrelid = c1.oid
and lower(c1.relname) = lower('tbl_test')
and lower(c2.relname) = lower('syn_test')
;

select * from tbl_test; (no problems)
select * from syn_test; (no problems)

delete from pg_attribute
where attrelid = (select oid from pg_class where lower(relname) = 
lower('syn_test'))
;

delete from pg_class
where lower(relname) = lower('syn_test')
;

Thanks!

Marc L.


Wed, 24 Jul 2002 02:22:39 -0400
Marc Lavergne <mlavergne-pub@richlava.com>
CREATE SYNONYM suggestions
On Wed, 2002-07-24 at 02:22, Marc Lavergne wrote:
> I have a need for relation synonyms in PostgreSQL. I don't see it in 
> 7.2.1 but the catalog seems to be able to support it more or less.
> 
> Here's what I intend to do:
> 
> 1) Create a duplicate record in pg_class for the base table information 
> but with the relname set to the synonym name.

This will eventually cause a problem when the file oid changes and the
old one gets removed.  Cluster is one of those commands that will do
that.

Other than that, any table changes won't be propagated -- but you
already mentioned that.


24 Jul 2002 07:23:36 -0400
Rod Taylor <rbt@zort.ca>
Re: CREATE SYNONYM suggestions
Marc Lavergne <mlavergne-pub@richlava.com> writes:
> I have a need for relation synonyms in PostgreSQL. I don't see it in 
> 7.2.1 but the catalog seems to be able to support it more or less.

> Here's what I intend to do:

> 1) Create a duplicate record in pg_class for the base table information 
> but with the relname set to the synonym name.

> 2) Duplicate the attribute information in pg_attribute for the base 
> table but with the attrelid set to the synonym oid.

> Is there anything fundamentally wrong with this approach?

YES.   You just broke relation locking (a lock by OID will only lock
one access path to the table).  Any sort of ALTER seems quite
problematical as well; how will it know to update both sets of catalog
entries?

A view seems like a better idea, especially since you can do it without
any backend changes.

			regards, tom lane

Wed, 24 Jul 2002 11:43:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: CREATE SYNONYM suggestions 
I thought that it might involve more than met the eye. I'm resisting the 
"view" approach since, like my bad kludge, it locks down the table 
definition and as a result doesn't provide a very effective synonym 
mechanism.

I'm looking into the commands/view.c as a basis for introducing the 
concept of synonyms. Based on what I see, it looks like implementing it 
should be too terrible. Sadly, it looks a lot like this would require 
introducing a new relation type.

I'll have to investigate and possibly submit the patch(es) later. The 
question is, since CREATE SYNONYM appears to be a SQL extension, is this 
something the group would want to incorporate?

Tom Lane wrote:
> Marc Lavergne <mlavergne-pub@richlava.com> writes:
> 
>>I have a need for relation synonyms in PostgreSQL. I don't see it in 
>>7.2.1 but the catalog seems to be able to support it more or less.
> 
> 
>>Here's what I intend to do:
> 
> 
>>1) Create a duplicate record in pg_class for the base table information 
>>but with the relname set to the synonym name.
> 
> 
>>2) Duplicate the attribute information in pg_attribute for the base 
>>table but with the attrelid set to the synonym oid.
> 
> 
>>Is there anything fundamentally wrong with this approach?
> 
> 
> YES.   You just broke relation locking (a lock by OID will only lock
> one access path to the table).  Any sort of ALTER seems quite
> problematical as well; how will it know to update both sets of catalog
> entries?
> 
> A view seems like a better idea, especially since you can do it without
> any backend changes.
> 
> 			regards, tom lane
> 



Wed, 24 Jul 2002 17:27:25 -0400
Marc Lavergne <mlavergne-pub@richlava.com>
Re: CREATE SYNONYM suggestions
Marc Lavergne writes:

> The question is, since CREATE SYNONYM appears to be a SQL extension, is
> this something the group would want to incorporate?

Well, you could start by explaining what exactly this concept is and what
it would be useful for.  I imagine that it is mostly equivalent to
symlinks.  If so, I can see a use for it, but it would probably have to be
a separate type of object altogether, so you could also create
synonyms/symlinks to functions, types, etc.

-- 
Peter Eisentraut   peter_e@gmx.net


Thu, 25 Jul 2002 22:54:53 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: CREATE SYNONYM suggestions
On Fri, 2002-07-26 at 02:54, Marc Lavergne wrote:
> Like you said, it's really just a symlink for db objects. If memory 
> serves me right, synonyms can only refer to tables and views in Oracle. 
> The most common use is for simplifying access to objects outside your 
> schema (eg. create synonym TABLEX for JOHN.TABLEX) or for simplifying 
> access to objects across database links (eg. create synonym TABLEX for 
> TABLEX@DBY).

For quick answers on SYNONYM use in DB2 see

http://members.aol.com/cronid/db2.htm 

(search for synonym)


I found no SYNONYM in SQL99 but for a similar construct ALIAS, there is
a reserved word in SQL99, but I could find no definition using it.

----------------
Hannu


26 Jul 2002 02:16:10 +0500
Hannu Krosing <hannu@tm.ee>
Re: CREATE SYNONYM suggestions
Like you said, it's really just a symlink for db objects. If memory 
serves me right, synonyms can only refer to tables and views in Oracle. 
The most common use is for simplifying access to objects outside your 
schema (eg. create synonym TABLEX for JOHN.TABLEX) or for simplifying 
access to objects across database links (eg. create synonym TABLEX for 
TABLEX@DBY).

Quick note: I know that PostgreSQL doesn't have the same concept of 
schemas, this is just an example.

There are two types of synonyms, private and public. Public synonyms 
only exist within the current user's schema while public synonyms apply 
system wide to all users.

 From a PostgreSQL perspective, there is little *current* value that 
synonyms bring to the table, save for reducing an object name like 
THIS_IS_WAY_TOO_LONG to TOO_LONG. That said, it's still a very commonly 
used construct in other commercial DBMSs.

I guess the place to implement this would be somewhere shortly after the 
parser has done it's thing, probably as part of the OID resolution. I 
should make this my standard disclaimer ;-) but again ... my goal with 
all this Oracle compatibility stuff is the SQL*Net compatibility listed 
in the TODO.


Peter Eisentraut wrote:
> Marc Lavergne writes:
> 
> 
>>The question is, since CREATE SYNONYM appears to be a SQL extension, is
>>this something the group would want to incorporate?
> 
> 
> Well, you could start by explaining what exactly this concept is and what
> it would be useful for.  I imagine that it is mostly equivalent to
> symlinks.  If so, I can see a use for it, but it would probably have to be
> a separate type of object altogether, so you could also create
> synonyms/symlinks to functions, types, etc.
> 



Thu, 25 Jul 2002 17:54:22 -0400
Marc Lavergne <mlavergne-pub@richlava.com>
Re: CREATE SYNONYM suggestions
On Fri, 26 Jul 2002 07:55:47 +1000, Marc Lavergne wrote:

> Like you said, it's really just a symlink for db objects. If memory serves me
> right, synonyms can only refer to tables and views in Oracle. 

In oracle (8i) you can create synonyms for tables, views, sequences, procedures,
functions, packages, materialised views, java class objects and other synonyms.



Fri, 26 Jul 2002 13:21:50 +1000
Cameron Hutchison <camh+un@xdna.net>
Re: CREATE SYNONYM suggestions