Fix duplicated rows in Synapse PostgreSQL database

synapse-db-fix-duplicates.sql

# 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