Re: [HACKERS] Creating temp tables inside read only transactions - Mailing list pgsql-general

On Fri, Jul 8, 2011 at 2:21 AM, Darren Duncan <darren@darrenduncan.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.

If for some reason we needed to have tables that happened to be called
x.y.z and a.b.c accessible from a single SQL session, we could allow
that much more simply by allowing schemas to be nested.  Then we could
allow arbitrary numbers of levels, not just three.  The whole point of
having databases and schemas as separate objects is that they do
different things: schemas are just containers for names, allowing
common access to data, and databases are completely separate entities,
allowing privilege separation for (say) a multi-tenant hosting
environment.  We're not going to throw out the latter concept just so
people can use two dots in their table names instead of one.

> Under this scenario, we make the property of a database being read-only or
> read-write for the current SQL session associated with a database rather
> than the whole SQL session.  A given transaction can read from any database
> but can only make changes to the ones not read-only.
>
> Also, the proper way to do temporary tables would be to put them in another
> database than the main one, where the whole other database has the property
> of being temporary.
>
> Under this scenario, there would be separate system catalogs for each
> database, and so the ones for read-only databases are read-only, and the
> ones for other databases aren't.
>
> Then the system catalog itself fundamentally isn't more complicated, per
> database, and anything extra to handle cross-database queries or whatever,
> if anything, is a separate layer.  Code that only deals with a single
> database at once would be an optimized situation and perform no worse than
> it does now.

I think you should make more of an effort to understand how the system
works now, and why, before proposing radical redesigns.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company