Skip site navigation (1) Skip section navigation (2)

Re: mailing list archiver chewing patches

From: Magnus Hagander <magnus(at)hagander(dot)net>
To: Matteo Beccati <php(at)beccati(dot)com>
Cc: Dimitri Fontaine <dfontaine(at)hi-media(dot)com>, Aidan Van Dyk <aidan(at)highrise(dot)ca>, Dave Page <dpage(at)pgadmin(dot)org>, Abhijit Menon-Sen <ams(at)toroid(dot)org>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Tim Bunce <Tim(dot)Bunce(at)pobox(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: mailing list archiver chewing patches
Date: 2010-01-14 15:09:43
Message-ID: 9837222c1001140709n1b4cf564s2c90d7f266d56e33@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-www
On Thu, Jan 14, 2010 at 16:06, Matteo Beccati <php(at)beccati(dot)com> wrote:
> Il 14/01/2010 15:47, Dimitri Fontaine ha scritto:
>>
>> Matteo Beccati<php(at)beccati(dot)com>  writes:
>>>
>>> WITH RECURSIVE t (mailbox, uid, date, subject, sender, has_attachments,
>>> parent_uid, idx, depth) AS (
>>>   SELECT mailbox, uid, date, subject, sender, has_attachments,
>>> parent_uid,
>>> uid::text, 1
>>>   FROM arc_messages
>>>   WHERE parent_uid IS NULL AND mailbox = 15
>>>   UNION ALL
>>>   SELECT a.mailbox, a.uid, a.date, a.subject, a.sender,
>>> a.has_attachments,
>>> a.parent_uid, t.idx || '.' || a.uid::text, t.depth + 1
>>>   FROM t JOIN arc_messages a USING (mailbox)
>>>   WHERE t.uid = a.parent_uid
>>> ) SELECT * FROM t ORDER BY idx
>>>
>>> Any improvements to sorting are welcome :)
>>
>> What I'd like would be to have it sorted by activity, showing first the
>> thread which received the later messages. I'm yet to play with CTE and
>> window function myself so without a database example to play with I
>> won't come up with a nice query, but I guess a more educated reader will
>> solve this without a sweat, as it looks easier than sudoku-solving,
>> which has been done already :)
>
> Eheh, that was my first try as well. CTEs look very nice even though I'm not
> yet very comfortable with the syntax. Anyway both for date and thread
> indexes sort is the other way around, with newer posts/threads at the
> bottom. Again I'll give it a try as soon as I find time to work again on it.

Three tips around this,

1) don't be constrained by how things look now. Make something that's useful.

2) don't be constrained  by the fact that we have two ways to view it
now (thread + date). we can easily do three, if different people like
different ways. As long as it's not so much it becomes a maintenance
burden

3) Remember to run your tests with lots of emails, some designs just
tend to fall apart over that (say a thread with 200+ emails in it)


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

In response to

pgsql-www by date

Next:From: David FetterDate: 2010-01-14 18:36:09
Subject: Re: mailing list archiver chewing patches
Previous:From: Matteo BeccatiDate: 2010-01-14 15:06:33
Subject: Re: mailing list archiver chewing patches

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2010-01-14 15:23:26
Subject: Re: Streaming replication, retrying from archive
Previous:From: Matteo BeccatiDate: 2010-01-14 15:06:33
Subject: Re: mailing list archiver chewing patches

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group