Date: Sat, 29 Nov 2003 14:34:20 -0500
From: Jamie Lawrence <postgres@jal.org>
To: Andreas Heissenberger <aheissen@a1.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Updating session id based on accesstimeout
Message-ID: <20031129193420.GA32196@clueinc.net>
Mail-Followup-To: Andreas Heissenberger <aheissen@a1.net>,
	pgsql-sql@postgresql.org
References: <002201c3b6a5$3b30aa50$0e0aa8c0@ogwnbia005>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <002201c3b6a5$3b30aa50$0e0aa8c0@ogwnbia005>
User-Agent: Mutt/1.3.28i
Organization: clue inc

On Sat, 29 Nov 2003, Andreas Heissenberger wrote:

> Hi,
> 
> I want to set a session id based on a session timeout per user. I have a very large logfile with more than 1.3 Mio records and look for a very efficient solution - maybe with PL/pgSQL .
> 
> The important columns in the table are: access timestamp and userid sorted by userid and access time.
> The logic for the session id is: when ever the userid changes or the time between two following records is greater than 60 sec create a new session id and update the record with this id.
> 
> I tried to solve this problem with Perl and the DBI interface - but it took about 12 hours to update all records.
> The fastes solution I found until now was not to use any data base, sort the data with the unix command sort and process the logfile text directly with perl.

You won't find a method faster than updating text files, until you stop
brute forcing the data.

Databases don't provide speed, at least in this case. They provide 
ACID compliance, and other features.

Perhaps, if you revisit your assumptions, you might find that your need
is solved by a different notion of the problem.

-j


-- 
Jamie Lawrence                                        jal@jal.org
"God created the integers, all else is the work of man."
   - Kronecker