As strange as this may sound, I had to do a cross database query at my
last job. It involved moving information from our e-commerce database
into the accounting system database. While there may have been another
way of handling it, this did allow me to handle the move solely within
MSSQL and not have to schedule something like a seperate program to
run. (Which I'd have to do to accomplish this in PGSQL currently.)
Stephan Szabo wrote:
>
> > >* You can access many databases from the same connection (depending of
> > >course on your privileges).
> > >
> > >=> PostgreSQL does not allow you to access more than one database per
> > >connection. This makes the system much safer and allows for more robust
> > >design.
> >
> > How does that makes things safer etc etc? I believe that this is a genuine
> > limitation.
> >
> > I hope the developers are honest why this limitation exists. There are
> > probably valid and good reasons for this limitation but I don't think
> > "safer and more robust" is a good one. If it really is, then it reduces my
> > confidence level in Postgresql's access control design/internals.
>
> There are actually good reasons, the first being that the
> postgresql view is that databases are distinct entities (as opposed to
> schemas which will hopefully get there eventually) and as such it makes no
> sense to cross-db query. Right now, it's more of a limitation due to the
> fact that permissions to prevent object creation aren't there and that
> schemas aren't there, but once that's done I don't think this is much
> of a limitation anymore.
>
> In addition, there are questions about authentication that I think are
> tricky here, such as, we query a view, it uses another db, what
> user/password should be used? should we query the user? does the user
> even *know* what other db this view uses and why he's being prompted for
> a password? Every client program (even random libpq ones) potentially
> need to be able to handle an authentication request in response to a
> query? It's not a clear cut win.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
