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

> Date: Fri, 8 Jul 2011 18:38:59 +1200
> From: GavinFlower@archidevsys.co.nz
> To: darren@darrenduncan.net
> CC: pgsql@j-davis.com; guillaume@lelarge.info; mbeeper@hotmail.com; pgsql-general@postgresql.org; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] [GENERAL] 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.
>