flagging first row inserted for each "group" of key

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: flagging first row inserted for each "group" of key
Date: 2009-12-17 09:28:45
Message-ID: 20091217102845.27de2983@dawn.webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've a web application.

I'm logging data related to the landing page and subsequent selected
hits.

create table track_hit (
hitid serial not null,
/* pk? I don't mind if serial wrap around
pk could be (hitid, tracking_time) */
esid varchar(32), -- related to session
tracking_time timestamp not null default now(),
-- some other stats
-- first_hit boolean not null default false, -- ???
);

I'd like to be sure I just count one "first hit" in a session (same
esid) in an efficient way that means I'd like to mark them with a
flag and avoid a group by, min subquery cycle when I'm doing
reporting.
I can't trust the browser and I just want one first hit for each
esid, no matter if they have the same tracking_time.

Of course the problem is concurrency, but maybe I missed some pg
trick that could help me.

I'm on 8.3 and no plan to move to 8.4 shortly, so no windowing
functions that will make reporting easier/faster.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Responses

Browse pgsql-general by date

  From Date Subject
Next Message A. Kretschmer 2009-12-17 09:38:32 Re: flagging first row inserted for each "group" of key
Previous Message Picavet Vincent 2009-12-17 08:52:03 Re: Training and open source