Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux