Re: [HACKERS] Creating temp tables inside read only transactions

On 9/07/2011 11:27 AM, Robert Haas wrote:
> On Fri, Jul 8, 2011 at 2:21 AM, Darren Duncan<darren(at)darrenduncan(dot)net> 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/