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 be up-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 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.
--
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 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.
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.