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

From Alban Hertroys
Subject Re: [GENERAL] Creating temp tables inside read only transactions
Date
Msg-id 5D576BEA-C565-4274-808F-DC4969808B76@solfertje.student.utwente.nl
Whole thread Raw
In response to Re: [GENERAL] Creating temp tables inside read only transactions  (Chris Travers <chris.travers@gmail.com>)
List pgsql-hackers
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
combinationof 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
ina relational database. Relational databases work with sets after all, which have no explicit sequence. With the
introductionof recursive queries that's _possible_, but as claimed earlier (and I tend to agree), for performance
reasonsit 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
theyare. 
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'ssufficient. 
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
happenif 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
kindof URI notation (eg. dsn://user@remote-database1). 

I'm also wondering how to handle this for multi-master replicated environments, in view of load-balancing. Those remote
databasereferences 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
cannotjust 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!



pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: [v9.2] DROP Reworks Part.1 - Consolidate routines to handle DropStmt
Next
From: Radosław Smogura
Date:
Subject: Re: Patch Review: Bugfix for XPATH() if text or attribute nodes are selected