Mailing List Archive

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