Re: Inexplicable duplicate rows with unique constraint

On 1/16/20 8:50 AM, Richard van der Hoff wrote:
> I'm trying to track down the cause of some duplicate rows in a table
> which I would expect to be impossible due to a unique constraint. I'm
> hoping that somebody here will be able to suggest something I might have
> missed.
>
> The problem relates to a bug filed against our application
> (https://github.com/matrix-org/synapse/issues/6696) At first I put this
> down to random data corruption on a single user's postgres instance, but
> I've now seen three separate reports in as many days and am wondering if
> there is more to it.
>
> We have a table whose schema is as follows:
>
> synapse=# \d current_state_events
> Table "public.current_state_events"
>    Column   | Type | Modifiers
> ------------+------+-----------
>  event_id   | text | not null
>  room_id    | text | not null
>  type       | text | not null
>  state_key  | text | not null
>  membership | text |
> Indexes:
>     "current_state_events_event_id_key" UNIQUE CONSTRAINT, btree
> (event_id)
>     "current_state_events_room_id_type_state_key_key" UNIQUE
> CONSTRAINT, btree (room_id, type, state_key)
>     "current_state_events_member_index" btree (state_key) WHERE type =
> 'm.room.member'::text
>
> Despite the presence of the
> current_state_events_room_id_type_state_key_key constraint, several
> users have reported seeing errors which suggest that their tables have
> duplicate rows for the same (room_id, type, state_key) triplet and
> indeed querying confirms that to be the case:
>
> 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
> (2 rows)
>

I'm assuming the above are obfuscated?

>
> Further investigation suggests that these are genuinely separate rows
> rather than duplicate entries in an index.

If you use length() on the values are they the same?

>
> The index appears to consider itself valid:
>
> synapse=> select i.* from pg_class c join pg_index i on
> i.indexrelid=c.oid  where
> relname='current_state_events_room_id_type_state_key_key';
>  indexrelid | indrelid | indnatts | indisunique | indisprimary |
> indisexclusion | indimmediate | indisclustered | indisvalid |
> indcheckxmin | indisready | indislive | indisreplident | indkey |
> indcollation |    indclass    | indoption | indexprs | indpred
> ------------+----------+----------+-------------+--------------+----------------+--------------+----------------+------------+--------------+------------+-----------+----------------+--------+--------------+----------------+-----------+----------+---------
>
>       17023 |    16456 |        3 | t           | f            | f
>         | t            | f              | t          | f            | t
>          | t         | f              | 2 3 4  | 100 100 100  | 3126
> 3126 3126 | 0 0 0     |          |
> (1 row)
>
> So, question: what could we be doing wrong to get ourselves into this
> situation?
>
> Some other datapoints which may be relevant:
>  * this has been reported by one user on postgres 9.6.15 and one on
> 10.10, though it's hard to be certain of the version that was running
> when the duplication occurred
>  * the constraint is added when the table is first created (before any
> data is added)
>  * At least one user reports that he has recently migrated his database
> from one server to another via a `pg_dump -C` and later piping into psql.
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com