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

On 12 Jul 2011, at 3:25, Chris Travers wrote:

>> 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.
>
> Right. Semantically myapp_schemaname_subschemaname is no less
> hierarchical than myapp.schemaname.subschemaname.

It is. To the database, your first example is a single identifier, while your second example is a sequential combination of three identifiers. The second one contains explicit hierarchy, the first one does not.

It's quite possible that the fact that these identifiers have a sequence is the biggest problem for implementing this in a relational database. Relational databases work with sets after all, which have no explicit sequence. With the introduction of recursive queries that's _possible_, but as claimed earlier (and I tend to agree), for performance reasons it is undesirable to apply this to system tables.

If we were talking about a _set_ of identifiers instead, without the requirement of a hierarchy (eg. myapp.schemaname.subschemaname = subschemaname.myapp.schemaname), implementation would probably be easier/perform better.

That does have some interesting implications for incompletely specified sets of namespaces, I'm not sure how desirable they are.
What's cool is that you can specify just a server hostname and a table-name and (as long as there's no ambiguity) that's sufficient.
Not so cool, if you use the above and someone clones the database on said host, you've suddenly introduced ambiguity.

Problems like that are likely to turn up with incomplete identifier specifications though, just a bit more likely to happen if you take the meaning of the sequence of the identifiers out. Just a bit.

> The larger issue is that of potential ambiguity wrt cross-database references.

Not necessarily, if the reference to a remote database is unambiguously recognisable as such, for example by using some kind of URI notation (eg. dsn://user(at)remote-database1).

I'm also wondering how to handle this for multi-master replicated environments, in view of load-balancing. Those remote database references probably need to reference different databases depending on which master they're running on?

From a security point-of-view I'd probably require a list of accessible remote databases per server (so that people cannot just query any database of their choice). That could also serve the load-balancing scenario.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4e1c1e1012091390850944!