Skip to content

Instantly share code, notes, and snippets.

@MurzNN
Last active January 11, 2021 06:44
Show Gist options
  • Save MurzNN/fa8f3536be21426c55307761d8dcbc90 to your computer and use it in GitHub Desktop.
Save MurzNN/fa8f3536be21426c55307761d8dcbc90 to your computer and use it in GitHub Desktop.
Fix duplicated rows in Synapse PostgreSQL database
# ERROR:  could not create unique index "current_state_events_room_id_type_state_key_key"
# DETAIL:  Key (room_id, type, state_key)=(!JoceisEAQRNMtpWVGm:matrix.org, m.room.member, @_oftc_haagch:matrix.org) is duplicated.
SELECT
room_id, type, state_key,
COUNT( * )
FROM
current_state_events
GROUP BY
room_id, type, state_key
HAVING
COUNT( * ) > 1
ORDER BY
room_id, type, state_key;
DELETE FROM
current_state_events a
USING current_state_events b
WHERE
a.ctid < b.ctid
AND a.room_id = b.room_id
AND a.type = b.type
AND a.state_key = b.state_key
;
# ERROR:  could not create unique index "destinations_pkey"
# DETAIL:  Key (destination)=(matrix.org) is duplicated.
SELECT
destination,
COUNT( * )
FROM
destinations
GROUP BY
destination
HAVING
COUNT( * ) > 1
ORDER BY
destination;
DELETE FROM
destinations a
USING destinations b
WHERE
a.ctid < b.ctid
AND a.destination = b.destination
;
# ERROR:  could not create unique index "receipts_graph_uniqueness"
# DETAIL:  Key (room_id, receipt_type, user_id)=(!jJmpwspyTtbvTSfcck:matrix.org, m.read, @n-13:matrix.org) is duplicated.
SELECT
room_id, receipt_type, user_id,
COUNT( * )
FROM
receipts_graph
GROUP BY
room_id, receipt_type, user_id
HAVING
COUNT( * ) > 1
ORDER BY
room_id, receipt_type, user_id;
DELETE FROM
receipts_graph a
USING receipts_graph b
WHERE
a.ctid < b.ctid
AND a.room_id = b.room_id
AND a.receipt_type = b.receipt_type
AND a.user_id = b.user_id
;
# ERROR:  could not create unique index "receipts_linearized_uniqueness"
# DETAIL:  Key (room_id, receipt_type, user_id)=(!zXfJBqSUvXySmsZMtB:jki.re, m.read, @tomtau.:matrix.org) is duplicated.
SELECT
room_id, receipt_type, user_id,
COUNT( * )
FROM
receipts_linearized
GROUP BY
room_id, receipt_type, user_id
HAVING
COUNT( * ) > 1
ORDER BY
room_id, receipt_type, user_id;
DELETE FROM
receipts_linearized a
USING receipts_linearized b
WHERE
a.ctid < b.ctid
AND a.room_id = b.room_id
AND a.receipt_type = b.receipt_type
AND a.user_id = b.user_id
;
# ERROR:  could not create unique index "device_lists_remote_cache_unique_id"
# DETAIL:  Key (user_id, device_id)=(@gauge:matrix.org, ABYXOHMFSM) is duplicated.
SELECT
user_id, device_id,
COUNT( * )
FROM
device_lists_remote_cache
GROUP BY
user_id, device_id
HAVING
COUNT( * ) > 1
ORDER BY
user_id, device_id;
DELETE FROM
device_lists_remote_cache a
USING device_lists_remote_cache b
WHERE
a.ctid < b.ctid
AND a.user_id = b.user_id
AND a.device_id = b.device_id
;
# ERROR:  could not create unique index "device_lists_remote_extremeties_unique_idx"
# DETAIL:  Key (user_id)=(@bewe_:gnuradio.org) is duplicated.
SELECT
user_id,
COUNT( * )
FROM
device_lists_remote_extremeties
GROUP BY
user_id
HAVING
COUNT( * ) > 1
ORDER BY
user_id;
DELETE FROM
device_lists_remote_extremeties a
USING device_lists_remote_extremeties b
WHERE
a.ctid < b.ctid
AND a.user_id = b.user_id
;
# ERROR:  could not create unique index "user_directory_search_user_idx"
# DETAIL:  Key (user_id)=(@_snoonet_TheSilentLink:matrix.org) is duplicated.
SELECT
user_id,
COUNT( * )
FROM
user_directory_search
GROUP BY
user_id
HAVING
COUNT( * ) > 1
ORDER BY
user_id;
DELETE FROM
user_directory_search a
USING user_directory_search b
WHERE
a.ctid < b.ctid
AND a.user_id = b.user_id
;
# ERROR:  could not create unique index "user_directory_user_idx"
# DETAIL:  Key (user_id)=(@zenn:matrix.org) is duplicated.
SELECT
user_id,
COUNT( * )
FROM
user_directory
GROUP BY
user_id
HAVING
COUNT( * ) > 1
ORDER BY
user_id;
DELETE FROM
user_directory a
USING user_directory b
WHERE
a.ctid < b.ctid
AND a.user_id = b.user_id
;
# ERROR:  could not create unique index "users_in_public_rooms_u_idx"
# DETAIL:  Key (user_id, room_id)=(@./:matrix.org, !GibBpYxFGNraRsZOyl:matrix.org) is duplicated.
SELECT
user_id, room_id,
COUNT( * )
FROM
users_in_public_rooms
GROUP BY
user_id, room_id
HAVING
COUNT( * ) > 1
ORDER BY
user_id, room_id;
DELETE FROM
users_in_public_rooms a
USING users_in_public_rooms b
WHERE
a.ctid < b.ctid
AND a.user_id = b.user_id
AND a.room_id = b.room_id
;
# ERROR: there is no unique constraint matching given keys for referenced table "destinations"
# > Fixed via previous queries
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment