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

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