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
On 1/6/2015 1:56 PM, Tim Smith wrote:> 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
>
Please don't top post.
With and without indexes is a completely different answer.
See the performance section at:
https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
