Re: [GENERAL] Creating temp tables inside read only transactions - Mailing list pgsql-hackers

On Mon, Jul 11, 2011 at 10:12 AM, Florian Pflug <fgp@phlo.org> wrote:
> On Jul11, 2011, at 07:08 , Darren Duncan wrote:
>> Christopher Browne wrote:
>>> Vis-a-vis the attempt to do nested naming, that is "ns1.ns2.table1",
>>> there's a pretty good reason NOT to support that, namely that this
>>> breaks relational handling of tables.  PostgreSQL is a *relational*
>>> database system, hence it's preferable for structures to be
>>> relational, as opposed to hierarchical, which is what any of the
>>> suggested nestings are.

>>Rather, the argument is that "it was intentional for the structuring of
table naming to, itself, be relational," and changing that definitely has
some undesirable characteristics.

>>The need for recursive queries is the most obvious "undesirable", but it's
not the only undesirable thing, by any means.

I do not see how recursive queries (really iteration of records) even enters
the picture...

Right now I can emulate a hierarchical schema structure via a naming scheme
- for example  "schemabase_sub1_sub2_etc".  I am simply looking for a formal
way to do the above AND also tell the system that I want all schemas under
"schemabase" to be in the search path.  Heck, I guess just allowing for
simply pattern matching in "search_path" would be useful in this case
regardless of the presence of an actual schema hierarchy.  Using "LIKE"
syntax say: "SET search_path TO schemabase_sub1_%" or something similar.
The only missing ability becomes a way for graphical tools to represent the
schema "hierarchy" using a tree-structure with multiple depths.

I can see how adding "." and ".." and relative paths would confuse the issue
those are not necessary features of a multi-level schema depth.

The above, combined with a different separator for intra-level
namespace/schema delineation, would allow for an unambiguous way to define
and use a hierarchical schema with seemingly minimal invasion into the
current way of doing things. You could almost implement it just by requiring
a specific character to act as the separator and then construct the actual
schema using single-level literals and supporting functions that can convert
them into an hierarchy.  In other words, the schema table would still only
contain one field with the full "parent!child" as opposed to (schema,
parent) with (VALUES('parent',null),('child','parent')).

In other words, if we use "!" as the separator, any schema named
"parent!child"  could be stored and referenced as such but then if you run a
"getChildren(parent)" function it would return "child" along with any other
schemas of the form "parent!%".  In this case the "%" sign could maybe only
match everything except "!" and the "*" symbol could be used to match "!" as
well.

I could give more examples but I hope the basic idea is obvious.  The main
thing is that the namespace hierarchy usage is standardized in such a way
that pgAdmin and other GUI tools can reliably use for display purposes and
that "search_path" can be constructed in a more compact format so that every
schema and sub-schema is still absolutely referenced (you can even have the
SET command resolve search_path at execution time and then remain static
just like "CREATE VIEW SELECT * FROM table".

David J.