remove_deleted_email_pushers background job fails with psycopg2.ProgrammingError when unlinked addresses exist

[anoadragon453]

We found the following traceback when testing develop this morning. This is related to #10581:

Synapse traceback logs

2021-08-31 13:39:38,502 - synapse.storage.background_updates - 117 - ERROR - background_updates-0 - Error doing update
Capture point (most recent call last):
  File "/usr/local/lib/python3.7/runpy.py", line 193, in _run_module_as_main
    "__main__", mod_spec)
  File "/usr/local/lib/python3.7/runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "/home/synapse/src/synapse/app/homeserver.py", line 462, in <module>
    main()
  File "/home/synapse/src/synapse/app/homeserver.py", line 458, in main
    run(hs)
  File "/home/synapse/src/synapse/app/homeserver.py", line 444, in run
    logger=logger,
  File "/home/synapse/src/synapse/app/_base.py", line 145, in start_reactor
    run()
  File "/home/synapse/src/synapse/app/_base.py", line 129, in run
    run_command()
  File "/home/synapse/env-py37/lib/python3.7/site-packages/twisted/internet/base.py", line 1318, in run
    self.mainLoop()
  File "/home/synapse/env-py37/lib/python3.7/site-packages/twisted/internet/base.py", line 1328, in mainLoop
    reactorBaseSelf.runUntilCurrent()
  File "/home/synapse/src/synapse/metrics/__init__.py", line 565, in f
    ret = func(*args, **kwargs)
  File "/home/synapse/env-py37/lib/python3.7/site-packages/twisted/internet/base.py", line 967, in runUntilCurrent
    f(*a, **kw)
  File "/home/synapse/env-py37/lib/python3.7/site-packages/twisted/internet/defer.py", line 701, in errback
    self._startRunCallbacks(fail)
  File "/home/synapse/env-py37/lib/python3.7/site-packages/twisted/internet/defer.py", line 764, in _startRunCallbacks
    self._runCallbacks()
  File "/home/synapse/env-py37/lib/python3.7/site-packages/twisted/internet/defer.py", line 859, in _runCallbacks
    current.result, *args, **kwargs
  File "/home/synapse/env-py37/lib/python3.7/site-packages/twisted/internet/defer.py", line 1751, in gotResult
    current_context.run(_inlineCallbacks, r, gen, status)
  File "/home/synapse/env-py37/lib/python3.7/site-packages/twisted/internet/defer.py", line 1658, in _inlineCallbacks
    cast(Failure, result).throwExceptionIntoGenerator, gen
  File "/home/synapse/env-py37/lib/python3.7/site-packages/twisted/python/failure.py", line 500, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
Traceback (most recent call last):
  File "/home/synapse/src/synapse/storage/background_updates.py", line 114, in run_background_updates
    self.BACKGROUND_UPDATE_DURATION_MS
  File "/home/synapse/src/synapse/storage/background_updates.py", line 224, in do_next_background_update
    await self._do_background_update(desired_duration_ms)
  File "/home/synapse/src/synapse/storage/background_updates.py", line 261, in _do_background_update
    items_updated = await update_handler(progress, batch_size)
  File "/home/synapse/src/synapse/storage/databases/main/pusher.py", line 453, in _remove_deleted_email_pushers
    "_remove_deleted_email_pushers", _delete_pushers
  File "/home/synapse/src/synapse/storage/database.py", line 694, in runInteraction
    **kwargs,
  File "/home/synapse/src/synapse/storage/database.py", line 792, in runWithConnection
    self._db_pool.runWithConnection(inner_func, *args, **kwargs)
  File "/home/synapse/env-py37/lib/python3.7/site-packages/twisted/python/threadpool.py", line 238, in inContext
    result = inContext.theWork()  # type: ignore[attr-defined]
  File "/home/synapse/env-py37/lib/python3.7/site-packages/twisted/python/threadpool.py", line 255, in <lambda>
    ctx, func, *args, **kw
  File "/home/synapse/env-py37/lib/python3.7/site-packages/twisted/python/context.py", line 118, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/home/synapse/env-py37/lib/python3.7/site-packages/twisted/python/context.py", line 83, in callWithContext
    return func(*args, **kw)
  File "/home/synapse/env-py37/lib/python3.7/site-packages/twisted/enterprise/adbapi.py", line 293, in _runWithConnection
    compat.reraise(excValue, excTraceback)
  File "/home/synapse/env-py37/lib/python3.7/site-packages/twisted/python/deprecate.py", line 298, in deprecatedFunction
    return function(*args, **kwargs)
  File "/home/synapse/env-py37/lib/python3.7/site-packages/twisted/python/compat.py", line 404, in reraise
    raise exception.with_traceback(traceback)
  File "/home/synapse/env-py37/lib/python3.7/site-packages/twisted/enterprise/adbapi.py", line 284, in _runWithConnection
    result = func(conn, *args, **kw)
  File "/home/synapse/src/synapse/storage/database.py", line 786, in inner_func
    return func(db_conn, *args, **kwargs)
  File "/home/synapse/src/synapse/storage/database.py", line 554, in new_transaction
    r = func(cursor, *args, **kwargs)
  File "/home/synapse/src/synapse/storage/databases/main/pusher.py", line 436, in _delete_pushers
    for row in txn:
psycopg2.ProgrammingError: no results to fetch

This error refers to this code:

synapse/synapse/storage/databases/main/pusher.py

Lines 432 to 443 in ad17fbd

	txn.execute(sql, (last_pusher, batch_size))
	last = None
	num_deleted = 0
	for row in txn:
	last = row[0]
	num_deleted += 1
	self.db_pool.simple_delete_txn(
	txn,
	"pushers",
	{"user_name": row[1], "app_id": row[2], "pushkey": row[3]},
	)

for row in txn: would work, except that in the inner loop then reuses txn to execute a delete. for row in txn is then executing on the DELETE statement, which of course returns no rows. Thus we end up with psycopg2.ProgrammingError: no results to fetch.

This can be fixed by simply doing the following instead.

txn.execute(sql, (last_pusher, batch_size))
rows = txn.fetchall()

for row in rows:
    # ...

The server seems to otherwise start OK, but this background job should be fixed before we release v1.42.0rc1.