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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
