Re: Using tables in other PostGreSQL database

On Fri, Mar 28, 2008 at 8:44 PM, Brent Wood <b(dot)wood(at)niwa(dot)co(dot)nz> wrote:
> I have mixed feelings,
>
> I agree that pretty much equivalent functionality CAN be delivered using schemas, but some RDBMS's do not have this restriction. Business cases & preferences do not necessarily follow database design preferences or capabilities, so irrespective of whether a schema approach CAN work, any user is entitled to ask whether an alternative approach is possible. Enough such users & the capability may well be implemented.

Sadly, the way postgresql is built, this is not a simple addition of a
few lines of code. How do you do cross db access within a
transaction? You can't, and it's not likely that any code will be put
in place to do this.

> I am looking to convince a business which does not use schemas, but does use separate databases to move to Postgres & having to shift from this paradigm is an issue for them. They are perfectly entitled to require such an approach, if we want to increase the user base of Postgres, we need to meet more users needs..... (simplistic I know, but I think a valid concern).

You're energy would be better spent showing them why they're wrong.
Every major db I've worked with supported schemas in one form or
another, and it's THE answer to this type of problem. If they want
their data in separate databases, then they need to know the
consequences. Even in Oracle you don't have cross db queries. You
use schemas there.

>
> For example, any user may need to be able to create databases, add data & use referentials in a corporate database of lookup records. Using schemas, everyone needs to have create table privileges to the database by default, then this needs to be denied by schema (is this possible???- never tried yet). Cross db queries allow read only access to corporate metadata for data integrity rules to be applied to any database any user creates. Agreed, not a common requirement, but one where schemas are less flexible & less secure.

There IS a solution. The correct one is to use schemas. The less
correct one is to use dblink across separate databases.

I challenge you to show me how schemas are less secure than cross db
work with dblink, because I do not believe that to be true.