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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
