So, after fixing the bugs (that I found) in the db_returning() patch,
and porting the tuple patch to it (only one hunk needed porting), and
after doing a full backup of my postgresql/data/base, I decided to try
out contiugous IMAP UIDs again.
With the db_returning() patch, the problem I had before with injection
goes away. (At least for pgsql users.) And I still have the trigger
and extra columns from my attempt at contiguous UIDs several months ago.
(More on that below.)
So I changed the column name in the trigger from message_uid to
message_idnr and did this:
BEGIN;
ALTER TABLE dbmail_messages drop CONSTRAINT dbmail_messages_pkey;
ALTER TABLE dbmail_messages ALTER message_idnr SET DEFAULT 0;
UPDATE dbmail_messages SET message_idnr = message_uid;
ALTER TABLE dbmail_messages DROP COLUMN message_uid;
ALTER TABLE dbmail_messages ADD PRIMARY KEY (message_idnr,mailbox_idnr);
DROP SEQUENCE dbmail_message_idnr_seq;
COMMIT;
Obviously this isn't a final solution for dbmail. It is pgsql specfic
and probably falls over when clustering.
But it sure scratches my itch!
I'm running it now.
And it sure is purty!
Gnus is *much* faster, since it doesn't spin dealing with a million
missing UIDs. Not quite as fast as when reading nnmh groups (the db
takes a bit more time to FETCH the messages), but startup is only 15
to 25 minutes rather than the two to three hours it was taking before
I switched to dbmail. So it is finally reasonable.
And the group buffer now shows the correct counts of unread messages.
To follow along takes a bit of effort.
The trigger functions are:
--
-- Name: dbmail_get_or_set_mailbox_next_uid(integer, integer); Type: FUNCTION; Schema: public; Owner: dbmail
--
CREATE OR REPLACE FUNCTION dbmail_get_or_set_mailbox_next_uid(idnr integer, uid integer) RETURNS integer
AS $$
declare
old_next_uid int4;
begin
SELECT INTO old_next_uid next_uid FROM dbmail_mailboxes WHERE mailbox_idnr = idnr FOR UPDATE;
UPDATE dbmail_mailboxes SET next_uid = GREATEST(uid, old_next_uid) + 1 WHERE mailbox_idnr = idnr;
RETURN GREATEST(uid, old_next_uid);
end;
$$
LANGUAGE plpgsql;
ALTER FUNCTION public.dbmail_get_or_set_mailbox_next_uid(idnr integer, uid integer) OWNER TO dbmail;
--
-- Name: dbmail_messages_set_message_uid(); Type: FUNCTION; Schema: public; Owner: dbmail
--
CREATE OR REPLACE FUNCTION dbmail_messages_set_message_uid() RETURNS "trigger"
AS $$
BEGIN
SELECT INTO NEW.message_idnr dbmail_get_or_set_mailbox_next_uid(NEW.mailbox_idnr, NEW.message_idnr);
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
ALTER FUNCTION public.dbmail_messages_set_message_uid() OWNER TO dbmail;
Also, you need to:
BEGIN;
ALTER TABLE dbmail_mailboxes ADD COLUMN next_uid INT4 NOT NULL DEFAULT 1;
CREATE INDEX dbmail_mailboxes_id_uid_idx ON dbmail_mailboxes (mailbox_idnr ,
next_uid);
COMMIT;
And, before making the change I did to messages above, also this:
BEGIN;
ALTER TABLE dbmail_messages ADD COLUMN imap_uid INT4 NOT NULL DEFAULT 0;
CREATE TRIGGER dbmail_messages_uid_trigger
BEFORE INSERT ON dbmail_messages
FOR EACH ROW
EXECUTE PROCEDURE dbmail_messages_set_message_uid();
COMMIT;
Then, before adding the functions above, edit a copy of
dbmail_messages_set_message_uid() replacing message_idnr with imap_uid.
At that point you'll have the schema I had until I ran that first bit of
sql above, but the mailboxes.next_uid and messages.imap_uid columns
still need to be populated.
It has been SO long since I did that I do not remember how I did it.
But I probably wrote some Perl using DBI to do this pseudocode:
get list of mailbox_idnrs via:
'select mailbox_idnr from dbmail_mailboxes order by mailbox_idnr asc'
for each of those, get the list of relevant message_idnrs:
'select message_idnr from dbmail_messages where mailbox_idnr=%u
order by mailbox_idnr asc', mailboxId
now, for each of those, set the imap_uid column:
'update dbmail_messages set imap_uid =
dbmail_messages_set_message_uid(%u, 0) where
mailbox_idnr=%u and message_idnr=%u',
mailboxId, mailboxId, messageId
where mailboxId and messageId are iterators thru the lists retrieved at
each stage.
Then re-add the two funtions (the originals as inlined above) and run
the block at the top of this message.
Then do a 'vacuum analyze' or a 'vacuum full analyze'.
And then you can restart imapd and mail delivery. (Yes, both have to be
shut down during the conversion. :)
A somewhat cumbersome changeover, but it does work.
And it is sweet.
And I'm rather embarrased by how little work it ended up being, that I
didn't end up with this when I first started on this quest. :^/
I'll still work on patches for trunk which are usable for the sqlite and
mysql users, but I expect I'll stick to what I have for now for my own use.
-JimC
--
James Cloos <[EMAIL PROTECTED]> OpenPGP: 1024D/ED7DAEA6
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
