Re: Changing the concept of a DATABASE

On 22/05/12 11:46, Simon Riggs wrote:
> On 21 May 2012 20:40, Stephen Frost<sfrost(at)snowman(dot)net> wrote:
>>> This is important. I like the idea of breaking down the barriers
>>> between databases to allow it to be an option for one backend to
>>> access tables in multiple databases. The current mechanism doesn't
>>> actually prevent looking at data from other databases using internal
>>> APIs, so full security doesn't exist. It's a very common user
>>> requirement to wish to join tables stored in different databases,
>>> which ought to be possible more cleanly with correct privileges.
>> That's really a whole different ball of wax and I don't believe what
>> Robert was proposing would actually allow that to happen due to the
>> other database-level things which are needed to keep everything
>> consistent... That's my understanding, anyway. I'd be happy as anyone
>> if we could actually make it work, but isn't like the SysCache stuff per
>> database? Also, cross-database queries would actually make it more
>> difficult to have per-database roles, which is one thing that I was
>> hoping we might be able to work into this, though perhaps we could have
>> a shared roles table and a per-database roles table and only 'global'
>> roles would be able to issue cross-database queries..

IMVHO: s/database/schema/g does resolve many of the problems that you
were referring to... and 'dblink' should solve the rest, right?
Please, feel free to point out what I am (most probably) not considering
-- not experienced enough yet :)

On the other hand, the separation of databases allows what otherwise
would only be possible by using multiple instances of the database
server (à la Oracle, AFAIK ) -- save for resource management, but that
is another question whatsoever.

> So collecting a few requirements from various places:
>
> * Ability to have a Role that can only access one Database

Yes, please

> * Allow user info to be dumped with a database, to make a db
> completely self-consistent
+1

> * Allow databases to be transportable
+1. Ideally, the binary format could be make platform-independent, so
that a snapshot/rsync of the cluster can span architectures easily.
AFAIK, endianness-change is relatively cheap on current processors [1
ASM instruction?] and it's not like we are memory-mapping tuples anyway
(TOASTed values can certainly not be mapped), so it shouldn't be
noticeable performance-wise.

> * Allow users to access tables in>1 database easily, with appropriate rights.

See above, but I am probably wrong ...

> I don't see any reasons why these things would be against each other.

Look quite orthogonal to me.

> The main objectives are to make a Database a more easily used
> administrative grouping. At present, people who use multiple Databases
> face many problems - they aren't as separate as you'd like, but
> neither can they be ignored when required.
>
> The idea of "one main database per session" is fine, but wiring it so
> closely into the backend has a few disadvantages, many of them weird
> internal things.
>
> Are there arguments against those requirements before we spend time on
> design/thinking?

OTOH, the postmaster/cluster - session/database coupling looks to me
clean, simple... and seems to make the code simpler. This is can only be
good (but again, I don't know enough yet to be sure)

Regards,

Jose Luis Tallon