Re: [HACKERS] Creating temp tables inside read only transactions - Mailing list pgsql-general
From | Gavin Flower |
---|---|
Subject | Re: [HACKERS] Creating temp tables inside read only transactions |
Date | |
Msg-id | 4E16A603.3030504@archidevsys.co.nz Whole thread Raw |
In response to | Re: [HACKERS] Creating temp tables inside read only transactions (Darren Duncan <darren@darrenduncan.net>) |
List | pgsql-general |
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.
pgsql-general by date: