On Sunday 05 August 2007 13:49:48 Benjamin Montgomery wrote:
> On Sun, 2007-08-05 at 12:55 -0300, Jorge Godoy wrote:
> > I have a PostgreSQL plpgsql function that cleans that up and also
> > collects some information on a table for statistics. This way, I can
> > know how long lasted the longest and shortest sessions and when a user
> > last accessed the system. It also adds a row count so that I have some
> > basis for other kinds of statistics.
> >
> > My "statistics" table has the following columns:
> >
> > - id (to be used with SQL Object)
> > - user id (to correlate with the user's table)
> > - date when the user first accessed the system
> > - date of his / her last access
> > - duration of shortest access
> > - duration of longest access
> > - row count so that I know how many rows where analyzed and led to some
> > update for that particular user
> >
> > This function is called from crontab every day to update statistics.
> >
> > If you're interested in it, I can share... It was just something that I
> > came up with to study some access patterns... Nothing too elaborated, as
> > you could see.
>
> I'd definitely be interested in the queries you are using for cleanup.
> I won't be collecting statistics like you are, but I'm thinking this
> would be good information to add to the documentation wiki for identity.
Here's the function. As I'm interested on the timings and I use the standard
20 minutes timeout, I'm removing them before storing data. (Please, bear in
mind this was in Portuguese and I quick translated on the email composer, so
if I missed something just ask again :-))
CREATE OR REPLACE FUNCTION f_access_statatics_clean_user_table(
OUT o_processed_records INTEGER)
AS $_$
DECLARE
w_record RECORD;
w_acess RECORD;
w_statistics INTEGER;
BEGIN
o_processed_records:=count(*) FROM tg_visit
WHERE expiry < now();
FOR w_record IN
SELECT DISTINCT ON (vi.user_id)
MIN(v.created) AS created, MAX(v.expiry) AS expiry, vi.user_id,
(MIN(v.expiry - v.created) - '20 minutes'::interval) AS
shortest_access,
(MAX(v.expiry - v.created) - '20 minutes'::interval) AS
longest_access
FROM tg_visit v
JOIN tg_visit_identity vi
ON v.visit_key=vi.visit_key
WHERE v.expiry < now()
GROUP BY vi.user_id
LOOP
-- Has the user been here before?
w_estatistics:=id FROM access_statistics
WHERE user_id=w_record.user_id;
-- If so, just update...
IF w_statistics IS NOT NULL THEN
UPDATE access_statistics SET last_access=w_record.expiry,
processed_records=processed_records + o_processed_records
WHERE user_id=w_record.user_id;
UPDATE access_statistics SET
shortest_access=w_record.shortest_access
WHERE user_id=w_record.user_id
AND shortest_access > w_record.shortest_access;
UPDATE access_statistics SET
longest_access=w_record.longest_access
WHERE user_id=w_record.user_id
AND longest_access < w_record.longest_access;
-- If not, add a new row for that user
ELSE
INSERT INTO access_statistics (user_id, first_access,
last_access, shortest_access, longest_access,
processed_records)
VALUES
(w_record.user_id, w_record.created, w_record.expiry,
w_record.shortest_access, w_record.longest_access,
o_processed_records);
END IF;
END LOOP;
DELETE FROM tg_visit_identity WHERE visit_key IN (
SELECT visit_key FROM tg_visit WHERE expiry < now());
DELETE FROM tg_visit WHERE expiry < now();
END;
$_$ LANGUAGE plpgsql VOLATILE;
If you're not collecting statistics for anything, then the last two DELETEs
might be enought for you. Also, the "now()" used there is frozen to when the
function started running, so all of them will result in the exact same time
this way I won't loose any record or erase more than what was analyzed. If
your database won't do that you might consider creating a new variable to
have the same value used on all queries.
Suggestions to improve that are welcome :-)
--
Jorge Godoy <[EMAIL PROTECTED]>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/turbogears?hl=en
-~----------~----~----~----~------~----~------~--~---
