Allow me to bounce some thoughts.
Working on the looping imap daemon I ran into a known problem: keeping
the cached mailbox state in sync with the database.
http://www.dbmail.org/mantis/bug_view_advanced_page.php?bug_id=0000122
:) Thanks for looking at my traces to nail this down, the
functionality works now...
I'm looking into making db_getmailbox as cheap and fast as possible
while still remaining reliable, so I can throw it in where-ever the
mailbox state is accessed, without having to worry about performance
too much.
Yeah, on a tricked out amd64 class system w/ a fast 3ware 9K card, I'm
only able to upload one message every two seconds.... performance is in
dire need of attention.
Thus, we can avoid the 'SELECT COUNT()..' and 'SELECT message_idnr...'
parts unless the cached timestamp for the mailbox is less than what
came out just now.
Is this what's causing the gazillion SELECT queries for each message
upload?
but for postgresql a trigger setup would be sufficient, right?
Yup. If you want help crafting this, let me know... I think it can all
be done with pl/sql, and not pl/pgsql. Send me the final versions of
the mysql queries and its relevant triggering conditions and I'll send
you the triggers/sql functions. -sc
--
Sean Chittenden
