Missing PK in status table

Troubleshooting
1

Hi,

A warning message is frequently present in our logs:

warn  [2022-01-22 14:22:24.128 +01:00] Failed to save status                         caller="app/status.go:217" user_id=XXXXXX error="failed to upsert Status: pq: there is no unique or exclusion constraint matching the ON CONFLICT specification" user_id=XXXXXX

We are using Mattermost 6.2.2.

I compared a fresh install of Mattermost:

                          Table "public.status"
     Column     |         Type          | Collation | Nullable | Default 
----------------+-----------------------+-----------+----------+---------
 userid         | character varying(26) |           | not null | 
 status         | character varying(32) |           |          | 
 manual         | boolean               |           |          | 
 lastactivityat | bigint                |           |          | 
 dndendtime     | bigint                |           |          | 
 prevstatus     | character varying(32) |           |          | 
Indexes:
    "status_pkey" PRIMARY KEY, btree (userid)
    "idx_status_status_dndendtime" btree (status, dndendtime)

And our production database:

                          Table "public.status"
     Column     |         Type          | Collation | Nullable | Default 
----------------+-----------------------+-----------+----------+---------
 userid         | character varying(26) |           | not null | 
 status         | character varying(32) |           |          | 
 manual         | boolean               |           |          | 
 lastactivityat | bigint                |           |          | 
 dndendtime     | bigint                |           |          | 
 prevstatus     | character varying(32) |           |          | 
Indexes:
    "idx_status_status_dndendtime" btree (status, dndendtime)

I believe, this is due to a failed migration.

I can’t just add the missing PK because there are a lot of duplicated value of userid.
Is it safe to just delete the content of the table, then add the PK ?

2

@agnivade @streamer45 Please take a look if you have thoughts on this report.

3

@rdelaage One option to avoid complete data loss on the table would be to keep only records whose LastActivityAt field is higher for a given UserID. That should usually be enough to deduplicate. After that it should be trivial to make the change. Example:

DELETE FROM status WHERE (userid, lastactivityat) NOT IN (SELECT userid, MAX(lastactivityat) FROM status GROUP BY userid);

ALTER TABLE status ADD CONSTRAINT status_pkey PRIMARY KEY (userid);
1 Like