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

Re: mailing list archiver chewing patches

From: Matteo Beccati <php(at)beccati(dot)com>
To: Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
Cc: Aidan Van Dyk <aidan(at)highrise(dot)ca>, Magnus Hagander <magnus(at)hagander(dot)net>, 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 14:08:22
Message-ID: 4B4F2556.80907@beccati.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-www
Il 14/01/2010 14:39, Dimitri Fontaine ha scritto:
> Matteo Beccati<php(at)beccati(dot)com>  writes:
>> I've extended AOX with a trigger that takes care of filling a separate table
>> that's used to display the index pages. The new table also stores threading
>> information (standard headers + Exchange headers support) and whether or not
>> the email has attachments.
>>
>> Please check the updated PoC: http://archives.beccati.org/
>
> Looks pretty good, even if some thread are still separated (this one for
> example), and the ordering looks strange.

This one is separated as the first one is not in the archive yet, thus 
to the system there are multiple parent messages. It shouldn't happen 
with full archives. About sorting, here's the query I've used (my first 
try with CTEs incidentally):

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 :)


Cheers
-- 
Matteo Beccati

Development & Consulting - http://www.beccati.com/

In response to

Responses

pgsql-www by date

Next:From: Matteo BeccatiDate: 2010-01-14 14:32:13
Subject: Re: mailing list archiver chewing patches
Previous:From: webmasterDate: 2010-01-14 14:00:00
Subject: PostgreSQL moderation report: 2010-1-14

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2010-01-14 14:15:14
Subject: Streaming replication, retrying from archive
Previous:From: Dimitri FontaineDate: 2010-01-14 13:51:37
Subject: Re: patch to implement ECPG side tracing / tracking ...

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