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

On 08/07/11 18:21, Darren Duncan wrote:
> 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
>
I would suggest that the default action for psql would be as now,
associate the session with a database in the name of the current O/S user.

However, use a new psql flag, such as '-unattached' or '-N', to indicate
that no database is to be attached when psql starts up.

While I don't have a current need for what you propose, it does look
interesting and potentially useful to me.