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

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