[Seaside] Re: Application/Session Objects

[Richard Huxton]

Yanni Chiu wrote:
> Richard Huxton wrote:
>> Hmm - that's useful, but what if I want to re-use the connection after 
>> the request?
> 
> I'm not sure I understand you. The connection is still open,
> and would be reachable via the session. Note that a WASession is
> longer lived than a single http request/response cycle.

Exactly - my database connection is sat there idle while the user reads 
the page and decides what to click on. Now sometimes I'm going to want 
the same connection over multiple requests, but most of the time I don't 
care. What I don't want to do is reconnect every time, hence the pool. I 
also don't necessarily want one DB connection for every possibly active 
session and it doesn't seem worth using pgpool when I've already got 
persistence.

>  > That is, I'd like to be able to do:
>> 1. hook a request start, claim a DB connection from the pool
>> 2. do some database queries
>> 3. hook the request end, return connection to the pool
>>
>> Crucially, #3 might restrict that connection for this session only or 
>> allow any session to re-use it.
> 
> Currently, for postgres, I connect with fixed (by configuration)
> username/password. Then, on the connection, I send:
> 
>   SET SESSION AUTHORIZATION username
> 
> But I'm not entirely sure this the "right" thing, since I've
> not fully tested things with multiple users.

That's what I'm planning, but in 8.1 with "SET ROLE username". With the 
optional wrinkle perhaps of connecting to the database as a NORMAL or 
SYSADMIN role at startup - safety in depth.

>> I'd hate to have an open transaction floating around for an hour while 
>> I wait for the session to expire.
> 
> Why would a transaction be open, unless you designed your
> application flow in that way.

Because mistakes happen, and one of the advantages of having this simple 
persistence of state is that I can start to span a transaction over 
multiple request/response if desirable.

 > Normally, you'd accumulated
> user responses in your application model, through multiple
> interactions with the user. Then when they hit "Save", the
> transaction would be started, the database changes sent,
> and the transaction commited.

And if I've been clumsy enough to have a stray BEGIN then I can end up 
blocking other transactions until the session expires. However, if I can 
attach to the end-of-request-response point in the code then I can issue 
a ROLLBACK and pretty quickly spot if I've made a mistake. I can also 
return the connection to the pool if I don't need it and use it for the 
next request to come in without the overhead of reconnecting/having lots 
of idle connections.

-- 
   Richard Huxton
   Archonet Ltd