Re: [GENERAL] Creating temp tables inside read only transactions - Mailing list pgsql-hackers

Jeff Davis wrote:
> On Thu, 2011-07-07 at 20:56 -0700, Darren Duncan wrote:
>>> When you create a temporary table, PostgreSQL needs to add rows in
>>> pg_class, pg_attribute, and probably other system catalogs. So there are
>>> writes, which aren't possible in a read-only transaction. Hence the
>>> error. And no, there is no workaround.
>> That sounds like a deficiency to overcome.
>>
>> It should be possible for those system catalogs to be virtual, defined like
>> union views over similar immutable tables for the read-only database plus
>> mutable in-memory ones for the temporary tables.
>
> Ideally, yes, from a logical standpoint there are catalog entries that
> are only interesting to one backend.
>
> But that doesn't mean it's easy to do. Remember that catalog lookups
> (even though most go through a cache) are a path that is important to
> performance. Also, more complex catalog interpretations may introduce
> some extra bootstrapping challenges.
>
>> Are there any plans in the works to do this?
>
> I don't think so. It sounds like some fairly major work for a
> comparatively minor benefit.
>
> Suggestions welcome, of course, to either make the work look more minor
> or the benefits look more major ;)

What I said before was a simplification; below I present my real proposal.

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.

Furthermore, federating databases is done with the same interface, by adding
remote/foreign databases as extra databases at the top level namespace.

Fundamentally, a SQL session would be associated with a Pg server, not a
database managed by such.  When one starts a SQL session, there are initially no
databases visible to them, and the top-level namespace is empty.

They then "mount" a database, similarly to how one mounts an OS filesystem, by
providing appropriate connection info, either just the database name or also
user/pass or also remote host etc as is applicable, these details being the
difference between using a local/same-Pg-cluster db or a remote/federated one,
and the details also say whether it is temporary or initially read-only etc.

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

Such a paradigm is also how my Muldis D language interfaces databases; this is
the most flexible, portable, extensible, optimizable, and elegant approach I can
think of.

-- Darren Duncan