A NOTE has been added to this issue. ====================================================================== http://www.dbmail.org/mantis/view.php?id=1008 ====================================================================== Reported By: rmoesbergen Assigned To: ====================================================================== Project: DBMail Issue ID: 1008 Category: Command-Line programs (dbmail-users, dbmail-util) Reproducibility: always Severity: major Priority: normal Status: new target: ====================================================================== Date Submitted: 31-May-13 10:17 CEST Last Modified: 30-Oct-13 17:21 CET ====================================================================== Summary: dbmail-util -ay does not clean headervalue table Description: It seems dbmail-util does not clean the headervalue table. My database is empty (it contains no messages for any user), but the headervalue table still contains http://www.dbmail.org/mantis/view.php?id=33#c70.000 rows. Running dbmail-util -ay does not clean this table. ======================================================================
---------------------------------------------------------------------- (0003523) rmoesbergen (reporter) - 04-Jun-13 10:53 http://www.dbmail.org/mantis/view.php?id=1008#c3523 ---------------------------------------------------------------------- This query cleans things up manually: delete from dbmail_headervalue where not exists (select headervalue_id from dbmail_header where headervalue_id = dbmail_headervalue.id); ---------------------------------------------------------------------- (0003567) santa (reporter) - 28-Aug-13 16:13 http://www.dbmail.org/mantis/view.php?id=1008#c3567 ---------------------------------------------------------------------- I can confirm that. Both dbmail_headervalue and dbmail_headername keep orphaned records. ---------------------------------------------------------------------- (0003568) Bloody (reporter) - 30-Aug-13 09:21 http://www.dbmail.org/mantis/view.php?id=1008#c3568 ---------------------------------------------------------------------- confirm, dbmail=# select count(*) from dbmail_headervalue left outer join dbmail_header on dbmail_header.headervalue_id=dbmail_headervalue.id where dbmail_header.headervalue_id is null; count --------- 5673957 (1 row) dbmail=# select count(*) from dbmail_headervalue; count --------- 5794361 (1 row) ---------------------------------------------------------------------- (0003600) Bloody (reporter) - 15-Oct-13 08:48 http://www.dbmail.org/mantis/view.php?id=1008#c3600 ---------------------------------------------------------------------- It must be cleaned up by PostgreSQL, but not working, any idea? dbmail=# \d dbmail_header Table "public.dbmail_header" Column | Type | Modifiers ----------------+--------+----------- physmessage_id | bigint | not null headername_id | bigint | not null headervalue_id | bigint | not null Indexes: "dbmail_header_pkey" PRIMARY KEY, btree (physmessage_id, headername_id, headervalue_id) Foreign-key constraints: "dbmail_header_headername_id_fkey" FOREIGN KEY (headername_id) REFERENCES dbmail_headername(id) ON UPDATE CASCADE ON DELETE CASCADE "dbmail_header_headervalue_id_fkey" FOREIGN KEY (headervalue_id) REFERENCES dbmail_headervalue(id) ON UPDATE CASCADE ON DELETE CASCADE "dbmail_header_physmessage_id_fkey" FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE ---------------------------------------------------------------------- (0003601) Bloody (reporter) - 29-Oct-13 18:44 http://www.dbmail.org/mantis/view.php?id=1008#c3601 ---------------------------------------------------------------------- Version 3.1.7 fixes this issue, but please add SET session_replication_role = replica; into transaction script for postgres that deletes a orphaned rows to disable triggers, because enabled triggers (on delete) dramatically slows down database maintenance with dbmail-util and SET session_replication_role = DEFAULT; to enable triggers ---------------------------------------------------------------------- (0003602) Bloody (reporter) - 30-Oct-13 17:21 http://www.dbmail.org/mantis/view.php?id=1008#c3602 ---------------------------------------------------------------------- Sorry for last comment, adding 3 indexes to database from last GIT version improve performance and disabling triggers not needed. Issue History Date Modified Username Field Change ====================================================================== 31-May-13 10:17 rmoesbergen New Issue 04-Jun-13 10:53 rmoesbergen Note Added: 0003523 28-Aug-13 16:13 santa Note Added: 0003567 30-Aug-13 09:21 Bloody Note Added: 0003568 15-Oct-13 08:48 Bloody Note Added: 0003600 29-Oct-13 18:43 Bloody Note Added: 0003601 29-Oct-13 18:44 Bloody Note Edited: 0003601 30-Oct-13 17:21 Bloody Note Added: 0003602 ====================================================================== _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
