A NOTE has been added to this issue. ====================================================================== http://dbmail.org/mantis/view.php?id=673 ====================================================================== Reported By: cmayo Assigned To: ====================================================================== Project: DBMail Issue ID: 673 Category: Command-Line programs (dbmail-users, dbmail-util) Reproducibility: always Severity: minor Priority: normal Status: new target: ====================================================================== Date Submitted: 28-Jan-08 19:31 CET Last Modified: 31-Jan-08 22:46 CET ====================================================================== Summary: dbmail-util -a hangs on physmessage integrity check Description: After upgrading from 2.2.5 to 2.2.8 (PostgreSQL 8.2.6) dbmail-util -ay hangs on:
Repairing DBMAIL messageblocks integrity... Ok. Found [0] unconnected messageblks. Repairing DBMAIL physmessage integrity... "SELECT * FROM pg_stat_activity" revealed the culprit to be: "SELECT COUNT(*) FROM dbmail_physmessage WHERE id NOT IN (SELECT physmessage_id FROM dbmail_messages)" Testing the query on its own it does indeed run on and on. Replacing the IN with a LEFT JOIN seems to fix the problem for me, returning in a few hundred ms. I've attached a patch - also for the cleanup operation which I haven't tested yet (the test has revealed 4 orphan physmessages so I'm glad the check is there and I may try and recreate some messages entries for them). ====================================================================== ---------------------------------------------------------------------- cmayo - 28-Jan-08 19:41 ---------------------------------------------------------------------- PS here's a trivial patch to put a full stop and newline on the end of the output to match the other reports. ---------------------------------------------------------------------- cmayo - 31-Jan-08 22:46 ---------------------------------------------------------------------- I see this has been discussed on the mailing list: http://www.mail-archive.com/dbmail-dev%40dbmail.org/msg10026.html (I only looked at the latest trees but didn't see anything) Not sure the DELETE in the patch on the post will work in PostgreSQL though - don't think JOIN is available to DELETE. PostgreSQL does have USING so something like: DELETE FROM dbmail_physmessage pm USING dbmail_messages m WHERE pm.id = m.physmessage_id AND m.physmessage_id IS NULL should work, but this is not standard SQL. (On a quick look it is supported by MySQL but not SQLite) Issue History Date Modified Username Field Change ====================================================================== 28-Jan-08 19:31 cmayo New Issue 28-Jan-08 19:31 cmayo File Added: dbmail-2.2.8-check_physmessages.patch 28-Jan-08 19:41 cmayo Note Added: 0002473 28-Jan-08 19:41 cmayo File Added: dbmail-2.2.8-maintenance.patch 31-Jan-08 22:46 cmayo Note Added: 0002474 ====================================================================== _______________________________________________ Dbmail-dev mailing list [email protected] http://twister.fastxs.net/mailman/listinfo/dbmail-dev
