Re: Another proposal for table synonyms

On Nov 30, 2010, at 6:28 PM, Tom Lane wrote:

> Alexey Klyukin <alexk(at)commandprompt(dot)com> writes:
>> To support addition of new database objects types that can be referenced by
>> synonyms a new system catalog, pg_synonym, is to be added, with an oid to
>> support comments on synonym, and the following schema:
>
> This is not going to work, at least not without making every type of
> lookup consult pg_synonym too, which I think can be considered DOA
> because of its performance impact on people who aren't even using the
> feature.

For those not using synonyms it would result in an extra syscache lookup for
each schema from the search_path that doesn't contain the table with the
specified name. If the table is specified with A FQN or contained in the first
schema from the search_path no extra lookup would occur. Is it considered a
big impact? The number of such lookups can be reduced if we traverse the
search_path for the tables first, and then look for the synonyms, although
that would change the lookup rules stated in this proposal

> It's also quite unclear how you prevent duplicate names
> if the synonyms are in their own catalog. (And no, the part of your
> proposal that says you're not preventing that isn't acceptable from
> a usability standpoint.)

What's wrong with the usability of that feature? The fact that the table with
the same FQN as a synonym masks the latter can be clearly stated in the
documentation. Are you expecting lots of people to name the synonym exactly
the same as one of the database tables and wonder why is the table and not the
synonym gets accessed? As an alternative, a warning during table/synonym
creation/renaming can be emitted if the name clash occurs.

>
> You could reasonably support synonyms for tables/views by storing them
> in pg_class with a new relkind. This doesn't cover synonyms for other
> object types, but since the total world demand for such a feature is
> approximately zero, that's not really a problem.

I think that would almost guarantee that synonyms for other kinds of objects
(i.e. databases, such kind of synonyms were requested in the past) would never
be added.

--
Alexey Klyukin http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc