Обсуждение: Can we get SQL Server-like cross database queries
One of the few really useful features of SQL Server that Postgres doesn’t have is straightforward cross-database queries.
You can reference any table in any database on the same server you’re on as database.schema.table.
With Postgres, it is necessary to set up a FDW connection between every database where you want to perform a query and the database you want to query. In an analytics environment where a single server might have a hundred or so smaller databases, this is a grossly inefficient proposition.
Is it practical to provide the SQL Server-like feature in Postgres?
Guyren Howe <guyren@gmail.com> writes:
> Is it practical to provide the SQL Server-like feature in Postgres?
No.
regards, tom lane
> On Jun 3, 2020, at 1:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Guyren Howe <guyren@gmail.com> writes: >> Is it practical to provide the SQL Server-like feature in Postgres? > > No. > > regards, tom lane > > 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 beup-close-and-personal with a db payroll?
On 6/3/20 2:57 PM, Rob Sargent wrote: >> On Jun 3, 2020, at 1:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >> Guyren Howe <guyren@gmail.com> writes: >>> Is it practical to provide the SQL Server-like feature in Postgres? >> No. >> >> regards, tom lane >> >> > 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 beup-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. -- Angular momentum makes the world go 'round.
On Thu, Jun 4, 2020 at 4:26 PM Ron <ronljohnsonjr@gmail.com> wrote: > On 6/3/20 2:57 PM, Rob Sargent wrote: > >> On Jun 3, 2020, at 1:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> Guyren Howe <guyren@gmail.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 tobe 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.
On Thu, 2020-06-04 at 16:41 +1200, Thomas Munro wrote: > 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. I am -1 on cross-database queries. I think it is a desirable feature to have databases isolated from each other, so you don't have to worry about a permission you forgot that allows somebody to access a different database. I think this is particularly relevant since all databases share the same users. I understand that sometimes the opposite would be desirable, but foreign data wrappers have alleviated that pain. Yours, Laurenz Albe
On Thu, Jun 4, 2020 at 12:56 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2020-06-04 at 16:41 +1200, Thomas Munro wrote:
> 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.
I am -1 on cross-database queries.
I think it is a desirable feature to have databases isolated from
each other, so you don't have to worry about a permission you forgot
that allows somebody to access a different database.
Perhaps make it a new right that can be granted and it is disabled by default.
Superusers could have it by default.
ALTER USER foo WITH CROSSDB | NOCROSSDB
Obviously there is much more to flesh out than this.
-m
On Wednesday, June 3, 2020, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2020-06-04 at 16:41 +1200, Thomas Munro wrote:
> 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.
I am -1 on cross-database queries.
I think it is a desirable feature to have databases isolated from
each other, so you don't have to worry about a permission you forgot
that allows somebody to access a different database.
I think this is particularly relevant since all databases share the
same users.
I understand that sometimes the opposite would be desirable, but
foreign data wrappers have alleviated that pain.
I agree with the conclusion but not so much with the premise. Even with global users you still need to grant permissions to individual databases and its debatable whether its “more safe” to prevent a user from directly accessing a database in the “catalog.schema” reference manner if they can do so with a direct login.
I agree with the general premise that modularity and isolation are generally positive qualities, especially as scale grows, and that expending considerable resources strictly for the goal of adding this capability to the system is not a direction that I would be in favor of. Now, if the prereqs for this feature also have other concrete benefits that are worth working toward, and in the end the sum of those makes cross-database queries a relatively simple matter, I would entertain putting in the last 10% of effort to become standard compliant.
David J.