Re: Inexplicable duplicate rows with unique constraint

Richard van der Hoff wrote:

> synapse=> select count(*), room_id, type, state_key from
> current_state_events group by 2,3,4 order by count(*) DESC LIMIT 2;
> count | room_id | type | state_key
> -------+-----------------------------------+---------------+-------------------------------------
> 3 | !ueLfVrSWYGMnFnoCbT:darkfasel.net | m.room.member |
> @irc_ebi_:darkfasel.net
> 3 | !HwocBmCtBcHQhILtYQ:matrix.org | m.room.member |
> @freenode_AlmarShenwan_:matrix.org

Looking at these columns which are of type text but do not
contain words of any particular language, there's probably
no point in using a linguistic-aware collation for them.

If you maintain the database schema, what you could do to avoid
the dependency on the OS collation and stay clear of the particular
upgrade difficulty of collations is to use COLLATE "C" for this kind of
field, as opposed to the default collation of the database.
As a bonus, operations with the "C" collations tend to be faster,
sometimes even much faster.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite