[ https://issues.apache.org/jira/browse/MAILBOX-229?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kohei Nozaki updated MAILBOX-229: --------------------------------- Attachment: MAILBOX-229.patch This patch makes that orphan rows will be deleted automatically. the SQL goes like this: {noformat} 2015-04-27 22:47:33 JST LOG: execute <unnamed>: BEGIN 2015-04-27 22:47:33 JST LOG: execute <unnamed>: UPDATE public.JAMES_MAIL_USERFLAG SET mailbox_id = $1, mail_uid = $2 WHERE mailbox_id = $3 AND mail_uid = $4 2015-04-27 22:47:33 JST DETAIL: parameters: $1 = NULL, $2 = NULL, $3 = '1', $4 = '1' 2015-04-27 22:47:33 JST LOG: execute <unnamed>: INSERT INTO public.JAMES_MAIL_USERFLAG (userflag_id, userflag_name, mailbox_id, mail_uid) VALUES ($1, $2, $3, $4) 2015-04-27 22:47:33 JST DETAIL: parameters: $1 = '356', $2 = 'NonJunk', $3 = '1', $4 = '1' 2015-04-27 22:47:33 JST LOG: execute <unnamed>: DELETE FROM public.JAMES_MAIL_USERFLAG WHERE userflag_id = $1 2015-04-27 22:47:33 JST DETAIL: parameters: $1 = '355' 2015-04-27 22:47:33 JST LOG: execute S_1: COMMIT {noformat} > Orphan JAMES_MAIL_USERFLAG rows should be deleted > ------------------------------------------------- > > Key: MAILBOX-229 > URL: https://issues.apache.org/jira/browse/MAILBOX-229 > Project: James Mailbox > Issue Type: Bug > Components: jpa > Affects Versions: 0.5 > Environment: James3 which acquired from SVN trunk on Feburuary 2015 > PostgreSQL 9.2.6 > Thunderbird 31.6.0 > Reporter: Kohei Nozaki > Priority: Minor > Attachments: MAILBOX-229.patch > > > Consider issuing following IMAP command while the mail which has uid 1, > already has a flag named {{NonJunk}}: > {noformat} > 7 store 1 +flags NonJunk > * 1 FETCH (FLAGS (\Seen NonJunk)) > 7 OK STORE completed. > {noformat} > Then following SQL will be executed: > {noformat} > 2015-04-27 22:21:13 JST LOG: execute <unnamed>: BEGIN > 2015-04-27 22:21:13 JST LOG: execute <unnamed>: UPDATE > public.JAMES_MAIL_USERFLAG SET mailbox_id = $1, mail_uid = $2 WHERE > mailbox_id = $3 AND mail_uid = $4 > 2015-04-27 22:21:13 JST DETAIL: parameters: $1 = NULL, $2 = NULL, $3 = '1', > $4 = '1' > 2015-04-27 22:21:13 JST LOG: execute <unnamed>: INSERT INTO > public.JAMES_MAIL_USERFLAG (userflag_id, userflag_name, mailbox_id, mail_uid) > VALUES ($1, $2, $3, $4) > 2015-04-27 22:21:13 JST DETAIL: parameters: $1 = '301', $2 = 'NonJunk', $3 = > '1', $4 = '1' > 2015-04-27 22:21:13 JST LOG: execute S_1: COMMIT > {noformat} > Currently AbstractJPAMessage#setFlags() is managing relation between > JAMES_MAIL and JAMES_MAIL_USERFLAG, it simply invokes {{userFlags.clear();}}, > then adds all of flags newly. this brings many orphan rows in > JAMES_MAIL_USERFLAG as follows: > {noformat} > james=# select * from james_mail_userflag where mailbox_id is null and > mail_uid is null; > userflag_id | userflag_name | mailbox_id | mail_uid > -------------+---------------+------------+---------- > 151 | NonJunk | | > 152 | NonJunk | | > 154 | NonJunk | | > 155 | NonJunk | | > 157 | NonJunk | | > ... > {noformat} > I think these orphan rows should be deleted because they are looks useless. -- This message was sent by Atlassian JIRA (v6.3.4#6332) --------------------------------------------------------------------- To unsubscribe, e-mail: server-dev-unsubscr...@james.apache.org For additional commands, e-mail: server-dev-h...@james.apache.org