Lots of psql duplicate key errors for device_uniqueness and users_name_key

[richvdh]

We get thousands of these a day:

2019-09-02 00:00:03.009 UTC [matrix main] ERROR:  duplicate key value violates unique constraint "device_uniqueness"
2019-09-02 00:00:04.622 UTC [matrix main] ERROR:  duplicate key value violates unique constraint "users_name_key"

They are due (I think) to our poor man's upsert impl, which we should replace with a proper upsert.

[Valodim]

Just a heads up for folks who find this issue because they are facing weird unique constraint violation errors: I've had similar errors after an upgrade to debian buster, due to https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html. This happens when you don't correctly set the collation of all tables to "C" as requested in the documentation.

[Ezwen]

We have this problem (only with device_uniqueness) on our homeserver but we did not update to debian buster, any idea? Should we be worried?

[richvdh]

Should we be worried?

No. The errors are annoying but not otherwise a problem.

[MurzNN]

See in logs same errors when configuring application services:

2021-07-21 18:15:53.321 MSK [2739047] matrix_synapse@matrix_synapse ERROR:  duplicate key value violates unique constraint "users_name_key"
2021-07-21 18:15:53.321 MSK [2739047] matrix_synapse@matrix_synapse DETAIL:  Key (name)=(@facebook:homeserver.org) already exists.
2021-07-21 18:15:53.321 MSK [2739047] matrix_synapse@matrix_synapse STATEMENT:  INSERT INTO users (name, password_hash, creation_ts, is_guest, appservice_id, admin, user_type, shadow_banned) V
ALUES('@facebook:homeserver.org', '', 1626880553, 0, 'mautrix_facebook', 0, NULL, false)