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

From: mike beeper
Subject: Re: [HACKERS] Creating temp tables inside read only transactions
Date: ,
Msg-id: COL103-W49F33E1E092BF226AF8FA0A7400@phx.gbl
(view: Whole thread, Raw)
In response to: Creating temp tables inside read only transactions  (mike beeper)
List: pgsql-general

Tree view

Creating temp tables inside read only transactions  (mike beeper, )
 Re: Creating temp tables inside read only transactions  (Guillaume Lelarge, )
  Re: Creating temp tables inside read only transactions  (Darren Duncan, )
   Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis, )
    Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
     Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis, )
      Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
       Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis, )
     Re: [HACKERS] Creating temp tables inside read only transactions  (Gavin Flower, )
     Re: [HACKERS] Creating temp tables inside read only transactions  (Robert Haas, )
      Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
       Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis, )
      Re: [HACKERS] Creating temp tables inside read only transactions  (Tom Lane, )
       Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
        Re: [HACKERS] Creating temp tables inside read only transactions  (Craig Ringer, )
        Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis, )
         Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
      Re: [HACKERS] Creating temp tables inside read only transactions  (Craig Ringer, )
      Re: [HACKERS] Creating temp tables inside read only transactions  (Michael Nolan, )
       Re: [HACKERS] Creating temp tables inside read only transactions  (John R Pierce, )
       Re: [HACKERS] Creating temp tables inside read only transactions  (Christopher Browne, )
        Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
         Re: [HACKERS] Creating temp tables inside read only transactions  ("David Johnston", )
         Re: [HACKERS] Creating temp tables inside read only transactions  (Florian Pflug, )
          Re: [HACKERS] Creating temp tables inside read only transactions  (Christopher Browne, )
           Re: [HACKERS] Creating temp tables inside read only transactions  ("David Johnston", )
            Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
            Re: [HACKERS] Creating temp tables inside read only transactions  (Chris Travers, )
             Re: [HACKERS] Creating temp tables inside read only transactions  (Alban Hertroys, )
            Re: [HACKERS] Creating temp tables inside read only transactions  (Florian Pflug, )
     Re: [HACKERS] Creating temp tables inside read only transactions  (Florian Pflug, )
 Re: [HACKERS] Creating temp tables inside read only transactions  (mike beeper, )

I like Darren's proposal.  It is elegant.

> Date: Fri, 8 Jul 2011 18:38:59 +1200
> From:
> To:
> CC: ; ; ; ;
> 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.
>

pgsql-general by date:

From: Darren Duncan
Date:
Subject: Re: [HACKERS] Creating temp tables inside read only transactions
From: A.M.
Date:
Subject: long names get truncated