Re: Correct/optimal DML query for application session management ?

Hi Andy,

Yeah, the table scan was what worried me.

As for no indexes ? I just didn't put the "create index" statements
in my post ... ;-)

Tim

On 6 January 2015 at 18:35, Andy Colson <andy(at)squeakycode(dot)net> wrote:
> On 1/6/2015 12:02 PM, Tim Smith wrote:
>>
>> Hi,
>>
>> I'm probably being incredibly stupid and missing something incredibly
>> simple but I've got a bit of query-writers block here !
>>
>> create table app_sessions(
>> session_id char(64) unique not null,
>> user_id char(32) unique not null,
>> session_start bigint not null,
>> session_lastactive bigint not null
>> );
>>
>>
>> The rules are :
>> Enforced session timeout after 86400 seconds (1 day)
>> Last active less than 1 hour ago.
>>
>> My idea to clean out stale sessions :
>> delete from app_sessions where extract (epoch from
>> now())-session_start>86400 or session_lastactive<=extract (epoch from
>> now())-3600;
>>
>> But of course that's going to be a nasty query, so that's why I think
>> I'm missing something and need a fresh pair of eyes (or a whole
>> mailing list's worth of eyes !!).
>>
>> Thanks guys !
>>
>>
>
> I don't see any other way. Why do you think it'll be so nasty? Cuz it'll
> table scan? You have no indexes so it doesn't matter what you write, it'll
> have to scan all rows. How many rows do you expect to have? 500? 1000?
> Table scan will be fine.
>
> If you wanted to make it more readable .. but work the same, you could use
> timestamptz instead of bigint, and then write:
>
> where current_timestamp - '1 day'::interval < session_start
>
> -Andy
>