Re: Can we get SQL Server-like cross database queries

On Thu, Jun 4, 2020 at 4:26 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
> On 6/3/20 2:57 PM, Rob Sargent wrote:
> >> On Jun 3, 2020, at 1:46 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> Guyren Howe <guyren(at)gmail(dot)com> writes:
> >>> Is it practical to provide the SQL Server-like feature in Postgres?
> >> No.
> > That got me chuckling.
> > I had just decided not to bother posting, but well, here goes.
> >
> > I call bs on the “cross db query” notion of tsql - but I admit I haven’t used it since it was a Sybase thing.
> >
> > Is db.owner.table (iirc) is really anything more than nuance on schema.table. Does a db for automotive parts need to be up-close-and-personal with a db payroll?
>
> Those aren't the only two databases that exist. Think of a federated system
> where you've got a "reference" database full of lookup tables, and one
> database for every 10,000 customers. For 45,000 customers you've got five
> databases, and they all need to access the reference database, plus "all
> customers" queries need to access all five databases.

There's no doubt it's useful, and it's also part of the SQL spec,
which says you can do catalog.schema.table. I would guess that we
might get that as a byproduct of any project to make PostgreSQL
multithreaded. That mountain moving operation will require us to get
rid of all the global state that currently ties a whole process to one
session and one database, and replace it with heap objects with names
like Session and Database that can be passed around between worker
threads.