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

On Thu, 2011-07-07 at 23:21 -0700, Darren Duncan 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.
>
> 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.

One challenge that jumps to mind here is that an Oid would need to
become a pair (catalog, oid). Even if the end result isn't much more
complex, getting there is not trivial.

> See also how SQLite works; this "mount" being analogous to their "attach".

I'm not sure SQLite is the best example. It has a radically different
architecture.

Regards,
Jeff Davis