On 9/07/2011 11:27 AM, Robert Haas wrote:
On Fri, Jul 8, 2011 at 2:21 AM, Darren Duncan<darren@xxxxxxxxxxxxxxxx> wrote:
I think an even better way to support this is would be based on Postgres
having support for directly using multiple databases within the same SQL
session at once, as if namespaces were another level deep, the first level
being the databases, the second level the schemas, and the third level the
schema objects.
Kind of like what the SQL standard defines its catalog/schema/object
namespaces.
This instead of needing to use federating or that contrib module to use
multiple Pg databases of the same cluster at once.
But if that's what you want, just don't put your data in different
databases in the first place. That's what schemas are for.
I think the part missing from that is that Pg does not currently provide
a mechanism to "connect" directly to a schema within a particular
database. You can log in and set search_path, of course, but it's a wee
bit clumsy and I suspect lots of people just don't get that.
pg_hba.conf cannot control schema access, either, so access control
based on IP address range or allowing different kinds of auth for
different users cannot be controlled on a schema level.
Being able to "connect" to a "database"."schema" location and have Pg
connect to the database then auto-set the search_path would address many
if not all of the use cases for cross-database queries.
That said, if there's ever a facility to WAL certain databases
separately and/or have different replication for different databases
within the same cluster, I can easily see the need coming up for
"big-unimportant-unreplicated-database" needing to query stuff from
"small-vital-replicated-database". By then, though, SQL-MED should fill
that need quite well enough.
Being able to:
psql dbname.schemaname
or
jdbc:postgresql://localhost/dbname.schemaname/
would probably address most of the other use cases, and make it much
easier for people migrating from databases that support cross-DB
queries. Thoughts?
--
Craig Ringer
POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088 Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
