Re: CREATE SYNONYM ...

On 3/8/06, Neil Conway <neilc(at)samurai(dot)com> wrote:
>
> I'm inclined to agree. The points raised about the difficulties of
> managing large numbers of schemas are legitimate, but I don't see that
> synonyms are a very effective solution. If we're going to make it less
> painful to work on applications with many tens of schemas, that's a
> worthwhile project, but I think we should take a fresh look at the
> problem rather than just blindly copying a construct from Oracle.

I agree wholeheartedly.

As for the idea that because we're not supporting database links (which
synonyms are used for a lot) we shouldn't support synonyms, now that we have
2PC, it's now possible to reimplement the dblink contrib module into
something which supported nicer database linking. Don't get me wrong, it
certainly would be a task, but it's doable and I know a number of people who
use the dblink contrib module on a daily basis for data copying and remote
querying.

I don't know anyone that really likes typing:

SELECT dblink('host=somehost dbname=remotedb ...', 'SELECT
emp_id,first_name,last_name,middle_name,birth_dt,ssn,... FROM EMPLOYEE WHERE
last_name = ''Blow''') AS t1(emp_id BIGINT, first_name VARCHAR, last_name
VARCHAR, middle_name VARCHAR, birth_dt DATE, ssn NUMERIC, ...)

instead of:

SELECT * FROM EMPLOYEE(at)remotedb WHERE last_name = 'Blow';

If that's not bad enough, just try to do a lot of dynamic work using
database links using the contrib module... it's not easy or efficient having
to create types, functions, views, and rules to do dynamic work.

This is another discussion in and of itself, but I don't think supporting
nicer database links is a discussion that's too far off either. I'm glad we
have the contrib module, but there's a lot of nicer things we could do there
as well. Not because it's an Oracle thing, but because it's great
functionality to have.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324