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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@squeakycode.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

On 6 January 2015 at 18:35, Andy Colson <andy@squeakycode.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

Oh, more important, if its only gonna be 1000 rows or less, then I'd say
drop the indexes and let it table scan. I bet it'll be faster.

-Andy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 06 Jan 2015, at 19:02, Tim Smith <randomdev4+postgres@gmail.com> wrote:
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
);

Just an observation: Are you sure that you don’t want to allow your users to have multiple sessions? You made users unique across the entire table, which mean there can only ever be one session for a user. Unless you delete ‘expired’ sessions ASAP, users won’t be able to start a new session again after their original session expired.
That may be desirable for your application and thus intended, but perhaps it was not?

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general