Errors from send_notification/get_messages

[alexcottner]

We're seeing an error from send_notification/get_messages

Error: ValueError("expected txn status 'Active' or 'Doomed', but it's 'Committed'")
Message: Unable to notify
Stack trace:

  File "/opt/.venv/lib/python3.12/site-packages/kinto/core/events.py", line 190, in _notify_resource_events_after
    request.registry.notify(event)
  File "/opt/.venv/lib/python3.12/site-packages/pyramid/registry.py", line 103, in notify
    [_ for _ in self.subscribers(events, None)]
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/.venv/lib/python3.12/site-packages/zope/interface/registry.py", line 446, in subscribers
    return self.adapters.subscribers(objects, provided)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/.venv/lib/python3.12/site-packages/zope/interface/adapter.py", line 896, in subscribers
    subscription(*objects)
  File "/opt/.venv/lib/python3.12/site-packages/pyramid/config/adapters.py", line 125, in subscriber_wrapper
    return derived_subscriber(*arg)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/.venv/lib/python3.12/site-packages/pyramid/config/adapters.py", line 97, in derived_subscriber
    return subscriber(arg[0])
           ^^^^^^^^^^^^^^^^^^
  File "/opt/.venv/lib/python3.12/site-packages/kinto_emailer/__init__.py", line 62, in send_notification
    messages += get_messages(storage, _context)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/.venv/lib/python3.12/site-packages/kinto_emailer/__init__.py", line 117, in get_messages
    hooks = _get_emailer_hooks(storage, context)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/.venv/lib/python3.12/site-packages/kinto_emailer/__init__.py", line 80, in _get_emailer_hooks
    metadata = storage.get(
               ^^^^^^^^^^^^
  File "/opt/.venv/lib/python3.12/site-packages/statsd/client/timer.py", line 41, in _wrapped
    return f(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^
  File "/opt/.venv/lib/python3.12/site-packages/kinto/core/decorators.py", line 70, in wrapper
    return func(*args, **new_kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/.venv/lib/python3.12/site-packages/kinto/core/storage/postgresql/__init__.py", line 341, in get
    result = conn.execute(sa.text(query), placeholders)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2306, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2181, in _execute_internal
    conn = self._connection_for_bind(bind)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2050, in _connection_for_bind
    return trans._connection_for_bind(engine, execution_options)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<string>", line 2, in _connection_for_bind
  File "/opt/.venv/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/opt/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1202, in _connection_for_bind
    self.session.dispatch.after_begin(self.session, self, conn)
  File "/opt/.venv/lib/python3.12/site-packages/sqlalchemy/event/attr.py", line 378, in __call__
    fn(*args, **kw)
  File "/opt/.venv/lib/python3.12/site-packages/zope/sqlalchemy/datamanager.py", line 302, in after_begin
    join_transaction(
  File "/opt/.venv/lib/python3.12/site-packages/zope/sqlalchemy/datamanager.py", line 265, in join_transaction
    DataManager(
  File "/opt/.venv/lib/python3.12/site-packages/zope/sqlalchemy/datamanager.py", line 117, in __init__
    transaction_manager.get().join(self)
  File "/opt/.venv/lib/python3.12/site-packages/transaction/_transaction.py", line 191, in join
    raise ValueError(
<class 'ValueError'>
ValueError("expected txn status 'Active' or 'Doomed', but it's 'Committed'")

[alexcottner]

This is happening intermittently, but it seems like we can trigger it (someitmes) with the __heartbeat__ endpoint for testing. It looks like the issue isn't in kinto-emailer but is a problem with how we're handling the database sessions. It seems like some sessions are being left in unexpected states.

Trying to write up a test that can produce this reliably.