Federation not working properly with PostgreSQL (can't send messages)

marks will be invisible in the report. -->

Description

I set up PostgreSQL on my server, created a user and database as instructed in the documentation and set the homeserver config to use those credentials. This is a fresh, non-migrated database (because the migration script is currently broken, and anyway I had no local message data in my homeserver yet).

After starting synapse, clients can use the server and exchange messages without issue. They can also receive messages originating from remote servers. However, messages they send are never received by clients in remote servers. No error is displayed on the client (riot), but you can tell the messages aren't being sent because there are no read confirmations in the interface.

Steps to reproduce

Listed above. I'm not sure if this is a bug or misconfiguration, but I was told to report here.

The following types of error recur in my log:

2019-02-27 12:53:05,642 - synapse.storage.client_ips - 367 - ERROR - update_client_ips-0 - Failed to insert client IP (('@protected:discworld.eu', '...', '127.0.0.1'), ('Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Riot/1.0.1 Chrome/69.0.3497.106 Electron/4.0.4 Safari/537.36', '...', 1551271982547)): ProgrammingError('there is no unique or exclusion constraint matching the ON CONFLICT specification\n',)

See also: #4628

2019-02-27 03:26:12,069 - synapse.metrics.background_process_metrics - 203 - ERROR - process_event_queue_for_federation-0 - Background process 'process_event_queue_for_federation' threw an exception
Traceback (most recent call last):
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/metrics/background_process_metrics.py", line 201, in run
    yield func(*args, **kwargs)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/federation/transaction_queue.py", line 164, in _process_event_queue_loop
    last_token = yield self.store.get_federation_out_pos("events")
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/storage/_base.py", line 393, in runInteraction
    *args, **kwargs
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/storage/_base.py", line 442, in runWithConnection
    inner_func, *args, **kwargs
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/threadpool.py", line 250, in inContext
    result = inContext.theWork()
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/threadpool.py", line 266, in <lambda>
    inContext.theWork = lambda: context.call(ctx, func, *args, **kw)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/context.py", line 122, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/context.py", line 85, in callWithContext
    return func(*args,**kw)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/enterprise/adbapi.py", line 306, in _runWithConnection
    compat.reraise(excValue, excTraceback)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/compat.py", line 464, in reraise
    raise exception.with_traceback(traceback)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/enterprise/adbapi.py", line 297, in _runWithConnection
    result = func(conn, *args, **kw)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/storage/_base.py", line 438, in inner_func
    return func(conn, *args, **kwargs)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/storage/_base.py", line 314, in _new_transaction
    r = func(txn, *args, **kwargs)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/storage/_base.py", line 808, in _simple_select_one_onecol_txn
    raise StoreError(404, "No row found")
synapse.api.errors.StoreError: 404: No row found

Note: I have tried to turn metrics on and off; It doesn't seem to have made any difference.

2019-02-27 15:07:06,693 - synapse.federation.federation_server - 848 - ERROR - PUT-31528 - Failed to handle edu 'm.device_list_update'
Traceback (most recent call last):
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/federation/federation_server.py", line 844, in on_edu
    yield handler(origin, content)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/handlers/device.py", line 485, in incoming_device_list_update
    yield self._handle_device_updates(user_id)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/util/metrics.py", line 63, in measured_func
    r = yield func(self, *args, **kwargs)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/handlers/device.py", line 555, in _handle_device_updates
    user_id, devices, stream_id,
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/storage/_base.py", line 393, in runInteraction
    *args, **kwargs
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/storage/_base.py", line 442, in runWithConnection
    inner_func, *args, **kwargs
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/threadpool.py", line 250, in inContext
    result = inContext.theWork()
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/threadpool.py", line 266, in <lambda>
    inContext.theWork = lambda: context.call(ctx, func, *args, **kw)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/context.py", line 122, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/context.py", line 85, in callWithContext
    return func(*args,**kw)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/enterprise/adbapi.py", line 306, in _runWithConnection
    compat.reraise(excValue, excTraceback)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/python/compat.py", line 464, in reraise
    raise exception.with_traceback(traceback)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/twisted/enterprise/adbapi.py", line 297, in _runWithConnection
    result = func(conn, *args, **kw)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/storage/_base.py", line 438, in inner_func
    return func(conn, *args, **kwargs)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/storage/_base.py", line 314, in _new_transaction
    r = func(txn, *args, **kwargs)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/storage/devices.py", line 405, in _update_remote_device_list_cache_txn
    lock=False,
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/storage/_base.py", line 657, in _simple_upsert_txn
    insertion_values=insertion_values,
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/storage/_base.py", line 754, in _simple_upsert_txn_native_upsert
    txn.execute(sql, list(allvalues.values()))
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/storage/_base.py", line 104, in execute
    self._do_execute(self.txn.execute, sql, *args)
  File "/home/matrix/synapse/env/lib/python3.5/site-packages/synapse/storage/_base.py", line 134, in _do_execute
    sql, *args
psycopg2.ProgrammingError: there is no unique or exclusion constraint matching the ON CONFLICT specification

Version information

Homeserver: discworld.eu

If not matrix.org:

Version: Synapse/0.99.1.1
Install method: pip
Platform: Debian Stretch; Intel Xeon E5-1650v4; 32gb RAM; Database partition on SSD with 199gb free space; Home partition on SSD with 918gb free space, running as a user.

matrix-org / synapse