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

From: Darren Duncan
Subject: Re: [HACKERS] Creating temp tables inside read only transactions
Date: ,
Msg-id: 4E175BB0.1050800@darrenduncan.net
(view: Whole thread, Raw)
In response to: Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis)
Responses: Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis)
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, )

Jeff Davis wrote:
> On Thu, 2011-07-07 at 23:21 -0700, Darren Duncan wrote:
>> 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.
<snip>
>> 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.
>
> One challenge that jumps to mind here is that an Oid would need to
> become a pair (catalog, oid). Even if the end result isn't much more
> complex, getting there is not trivial.

Yes, but that would just be in-memory or in temporary places external to every
database.  On disk internal to a database there would just be the oid.  In fact,
another aspect of the database model I defined is that each "database" is
entirely self-contained; while you can do cross-database queries, you don't have
cross-database constraints, in the general case.

>> See also how SQLite works; this "mount" being analogous to their "attach".
>
> I'm not sure SQLite is the best example. It has a radically different
> architecture.

Still, its an example I know of where you can access several clearly separable
databases at once through a common namespace.  While one might argue this is a
substitute for multiple schema support, I don't because with multiple schemas
you can have integrity constraints that cross schemas.  The namespaces issue is
largely orthogonal to self-containment or integrity in my model.

But look at Oracle too, at least how I understand it.

Oracle supports "CONNECT TO ... AUTHORIZE ..."/etc SQL, meaning you can define
what databases you are accessing within the SQL session, rather than having to
do it externally.  I assume that Oracle's features correspond somewhat to my
proposal, and so enable cross-database queries in the illusion that several
databases are one.

Suffice it to say, I have thought through my proposed model for years, with one
of its (and Muldis D's) express purposes in providing a common normalized
paradigm that all the existing SQL DBMSs can map to with consistent behavior
whether Oracle or SQLite, and I haven't stated all of it here (a lot more is in
my published language spec).  Key mapping points are the boundaries of a
database's self-definability.  And namespace nesting is actually
arbitrary-depth, so accounting for everything from no native schema support to
schema plus "package" namespace support.

-- Darren Duncan


pgsql-general by date:

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