Re: [Gnumed-devel] pooling connections

[Horst Herb]

On Sun, 1 Sep 2002 06:22 pm, Ian Haywood wrote:
> On Mon, Sep 02, 2002 at 03:56:56AM +1000, Horst Herb wrote:

> I suggest an abstracted API for the database, so there is no reliance on
> developer 'diligience'

We have the abstracted API already (gmPG.GetConnection()).
We could add a flag "readonly", set it to true by default so that a
writeable connection has to be requested explicitly.

The next problem would be how to ensure that read-only queries are really only 
used for read-only.

We could create a pseudo-user for each user, where permissions are set to 
"read only" for each table; when the user connects, one connection per 
physical database would be made for read-only purposes, and a pool of 5-10 
connections pre-connected and pooled for each physical database for write 
purposes.

When the readonly flag is unset in "GetConnetion()", a physical read-only 
connection is then returned.

Problem with this is that we would either have two nominally different users 
on the audit trail, or we would need yet another mapping of the two pseudo 
users (read-user and write-user) to a single user id. This results in an 
extra query within all the audit-trailing trigger functions (select userid 
from user where username like (select CURRENT_USER))

I'd wish there was a read-only flag settable when requesting a physical 
postgres connection. But much better it would it be if cursors themselves 
were commitable from DB-API level. Or are they and I misinterpret the scarce 
documentation?

Horst