Re: CREATE SYNONYM ...

On Wed, 8 Mar 2006, Jonah H. Harris wrote:

> On 3/8/06, Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
> >
> > Doesn't that pretty much go against the (I thought) outstanding behavioral
> > question of whether the synonyms are scoped and obey search path? If they
> > do, I don't see how the above rule can hold since finding the "real table"
> > is insufficient to know if there's an earlier synonym.
> >
>
> There is a cost for synonyms no matter how you look at it.

Yes, however there are two slightly separate discussions going on and I
think you're taking them as a single discussion. One is about the feature
in general. One is about this patch in particular and the approach taken
in it. I'm mostly talking about the latter and specifically, what are the
costs of this particular way of implementing synonyms for people not using
the feature.

Even if we want a feature, there's a particular patch (or particular
patches) that implement the feature that come for review. If one thinks
the approach taken by the patch gives costs that are greater than our
desire for the feature, then one argues against it. That's irrespective
of whether that person believes in the feature as a whole. If you notice,
AFAICS I haven't said, "we shouldn't implement synonyms" or "synonyms are
unnecessary", but instead effectively "what are the costs of implementing
synonyms" and "that analysis of the cost for this approach seems wrong".

> Assume your user has it's own schema, that there is a synonym in public for
> EMPLOYEE which pointed to HR.EMPLOYEE, and your search path is
> $user,public. If you do a SELECT * FROM EMPLOYEE, the search order is still
> the same as it is in PostgreSQL now, there's no EMPLOYEE table in the $user
> schema, so when it gets to searching public, it finds the synonym. The only
> alternative in this scenario is to create the EMPLOYEE table in public
> (which is pretty stupid in most cases), or to set the search path to
> $user,public,hr. Again, this doesn't cover the "same-named tables in
> multiple schemas" argument, but it does illustrate that PostgreSQL's
> namespace scoping remains the same.

IMHO, that's insufficient analysis, precisely for the reason it doesn't
cover multiple schemas with the same objct.

If your search path is A,B and there is a B.EMPLOYEE table and an
A.EMPLOYEE synonym to HR.EMPLOYEE, which table does select * from EMPLOYEE
read? If the behavior is find the table through the whole path, then find
the synonym, it's B.EMPLOYEE and there's only cost to people who aren't
using the feature for error cases. If the behavior is find either table or
synonym in each search path entry in order (thus HR.EMPLOYEE) and finding
synonyms requires a separate search of the catalogs, then it seems like
everyone pays whether or not they are using the feature.

So far, there have been statements made that the cost to people not using
the feature is minimal in this approach because the extra search only
happens if the table isn't found. However, I still am not seeing how that
approach gives the second behavior (assuming that's what we want). To
discuss how to implement a feature we need at least an understanding of
what the behavior an approach implements and the costs that approach
incurs.