Mark Sapiro wrote:
Hello, we seem to have the same problem right now. We are still on python3.6 and postgresql 12.9 I looked at our Table pendedkeyvalue and we have roughly 35k rows in that table, is that normal or shouldn't those get cleaned up after a while? Yes they should. This is https://gitlab.com/mailman/mailman/-/issues/257 fixed in Mailman core 3.3.5. Also in Mailman 3.3.5 is a new Task runner
On 1/29/22 05:48, Jacob Sievert via Mailman-users wrote: that runs periodic tasks, one of which is to remove orphaned pendings. This may also be the issue for the OP in this thread if that pendedkeyvalue table is also large.
I can confirm this. We have a total of ~580000 Rows in the pendedkeyvalue table. Since last wednsday even the hourly job quits (Out of memory exception)
Here is a mailman shell script that will clean that up. # Prior to Mailman 3.3.5, some tokens for user confirmations were pended with # too long a lifetime. This script removes those pendings based on when they # were pended and the configured pending_request_life rather than their # expiration.
# Also prior to Mailman 3.3.5, pended held_message tokens for email handling # of the message were not removed when the message was handled via REST. This # script removes those pendings too.
# This is run with # mailman shell -r delete_orphans_expireds # after saving it as # /opt/mailman/mm/venv/bin/delete_orphans_expireds.py
from datetime import datetime from lazr.config import as_timedelta from mailman.config import config from mailman.database.transaction import transactional from mailman.interfaces.pending import IPendings from zope.component import getUtility
pendings = getUtility(IPendings)
def is_request(id): if config.db.store.execute( 'SELECT * FROM _request WHERE id = {};'.format(id)).rowcount > 0: return True return False
then = datetime.now() - as_timedelta(config.mailman.pending_request_life) thenm = datetime.now() - as_timedelta(config.mailman.moderator_request_life)
def fromisoformat(x): if hasattr(datetime, 'fromisoformat'): return datetime.fromisoformat(x) try: return datetime.strptime(x, '%Y-%m-%dT%H:%M:%S.%f') except ValueError: return datetime.strptime(x, '%Y-%m-%dT%H:%M:%S')
@transactional def delete_orphans_expireds(): count = 0 for token, data in pendings.find(pend_type='held message'): if data and not is_request(data['id']): result = pendings.confirm(token, expunge=True) count += 1 print(f'expunged {count} orphaned pended held messages')
count = 0 for token, data in pendings.find(pend_type='data'): if data and data['_mod_hold_date']: when = data['_mod_hold_date'] if isinstance(when, str): when = fromisoformat(when) if when < thenm: result = pendings.confirm(token, expunge=True) count += 1 print(f'expunged {count} expired held messages') pends = list(pendings.find(pend_type='subscription')) pends += list(pendings.find(pend_type='unsubscription')) count = 0 for token, values in pends: if values and values['token_owner'] == 'subscriber': when = values['when'] if isinstance(when, str): when = fromisoformat(when) if when < then: result = pendings.confirm(token, expunge=True) count += 1 print(f'expunged {count} expired (un)subscription confirmations')The above says to save the script at /opt/mailman/mm/venv/bin/delete_orphans_expireds.py but that path may need to be adjusted based in where Mailman's bin/ directory is in your installation.
Thanks for the Script Mark! WIth the Script, the result is:
mailman shell -r delete_orphans_expireds
Jan 31 09:15:46 2022 (7980) Database url: postgres://mailman:XXXXXXXX@127.0.0.1/YYYYYYY
expunged 95918 orphaned pended held messages
expunged 7958 expired held messages
expunged 22372 expired (un)subscription confirmationsAnd the pendedkeyvalue table has significant lower entries (~ 92000) and now the process to accept or decline a held message ist a lot of faster. Maybe we will take the database to a seperate server to get more speed - but for the moment it is fast enough.
Thanks again and thanks to Jacob Sievert for the pointer to the size of the pendedkeyvalue table.