[horde] SQL Session Handler

[John C. Amodeo]

Greetings,

I've been working on updating the code for the 
lib/SessionHandler/pgsql.php module, and I'm running into a glitch 
somewhere in the system that I can't seem to get past.  I was hoping one 
of the developers (who knows how session management works better than I 
do) can assist me to find a solution to the problem.

Background: 

It seems the pgsql.php file included in Horde 2.2.5 used php functions 
that are obsolete in newer versions of php and the code needs to be 
updated.  In addition, session_data is stored in a 'text' type field, 
which seems to be problematic and causes strange behavior to occur while 
using IMP/Turba/Mnemo, etc...

Goal:

1) Update all PG functions to comply with changes in PHP standards
2) Change storage method to make use of the Postgres "BYTEA" data type 
to eliminate problems with session management when using the "TEXT" type 
field.
3) Fix a few problems with the code.
4) Supply a patch to the Horde list

The new Postgres database looks like this:

        Column        |         Type          | Modifiers
----------------------+-----------------------+-----------
 session_id           | character varying(32) | not null
 session_lastmodified | integer               | not null
 session_data         | bytea                 |
Indexes: horde_sessionhandler_pkey primary key btree (session_id)

Here's a partial list of deprecated PHP functions that were updated:

Old Function --> New Function
pg_exec --> pg_query
pg_numrows --> pg_num_rows
pg_freeresult --> pg_free_result
etc...

Results:

We've added a function to the code, using "pg_escape_bytea" that formats 
the session data for storage into the SQL server.  I've tested the new 
code for about 2 weeks now, and everything works beautifully *except* 
for one problem, which I have found exists even with the old code.

Basically, under a certain condition, the old Session ID fails to get 
deleted from the SQL table.  The end result to the user is he/she must 
log in twice *if* his/her session expires while using web mail.  With 
more detail, this is how you would reproduce the error:

1) User logs into web mail.  Everything works fine.  Session information 
is properly stored in the SQL tables.
2) If there is a significant amount of time where the user does nothing, 
the session expires.
3) When the user comes back to the system and tries to use any Horde 
application, the user is brought to the log in page with a message that 
the "Session Has Expired, bla bla bla"
4) The user must then enter credentials and log back into the system.
5) Login is successful (according to Horde log and imap server log) but 
the session is still expired.  The Horde sessionhandler throws errors to 
the log "Failed to delete session etc..."
6) User is brought back to the login page, enters credentials again, and 
this time the SessionHandler successfully deletes the expired Session 
ID, creates a new one, and the user can successfully use Horde applications.

This consistently happens *every* time.  (This is not a sporadic 
problem...)  I know this problem exists with the 2.2.5 Postgres module, 
but I looked at the code for MySQL and the logic is the same, so it may 
exist there as well...

I had thought for a while that there may be some error in the query that 
gets sent to the SQL server, but if I copy and paste the string from the 
error logs directly into the SQL server buffer, the command executes 
correctly and deletes the Session ID.

I've had 3 sets of eyes look at this (2 of whom are really good database 
programmers) and we cannot figure out where the problem is...

I was thinking the problem might lie in the logic sequence of checking 
for expired sessions, logging into the system, etc., but I don't know 
enough about the tie in between the session handler, imp/login.php, or 
lib/Horde.php to trace what's going on...

Nonetheless, once I get this little bug fixed (or worked around) we'll 
have an updated session handler for Postgres that works significantly 
better than the current one...

So, if anyone has any ideas, I'd love to know what you're thinking...

Thanks in advance.
-John