ERROR: relation "idx_*" does not exist

Summary
ERROR: relation “idx_*” does not exist

Expected behavior
No errors in my logs

Observed behavior
I ran a (flawless) update from v5.36 to v5.38 today and had a quick look at the logs afterwards when I noticed a couple of SQL errors which make me a bit nervous. The errors don’t seem to be related to the upgrade though, I can see from the logs that they were showing already before the upgrade.

mattermost-db     | 2021-08-03 10:59:02.258 CEST [1] LOG:  database system is ready to accept connections
mattermost-db     | 2021-08-03 10:59:03.126 CEST [28] ERROR:  relation "idx_posts_channel_id" does not exist
mattermost-db     | 2021-08-03 10:59:03.126 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.127 CEST [28] ERROR:  relation "idx_channels_name" does not exist
mattermost-db     | 2021-08-03 10:59:03.127 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.127 CEST [28] ERROR:  relation "idx_publicchannels_name" does not exist
mattermost-db     | 2021-08-03 10:59:03.127 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.128 CEST [28] ERROR:  relation "idx_channelmembers_channel_id" does not exist
mattermost-db     | 2021-08-03 10:59:03.128 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.128 CEST [28] ERROR:  relation "idx_emoji_name" does not exist
mattermost-db     | 2021-08-03 10:59:03.128 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.129 CEST [28] ERROR:  relation "idx_oauthaccessdata_client_id" does not exist
mattermost-db     | 2021-08-03 10:59:03.129 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.129 CEST [28] ERROR:  relation "idx_oauthauthdata_client_id" does not exist
mattermost-db     | 2021-08-03 10:59:03.129 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.130 CEST [28] ERROR:  relation "idx_preferences_user_id" does not exist
mattermost-db     | 2021-08-03 10:59:03.130 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.130 CEST [28] ERROR:  relation "idx_notice_views_user_id" does not exist
mattermost-db     | 2021-08-03 10:59:03.130 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.130 CEST [28] ERROR:  relation "idx_notice_views_user_notice" does not exist
mattermost-db     | 2021-08-03 10:59:03.130 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.131 CEST [28] ERROR:  relation "idx_status_user_id" does not exist
mattermost-db     | 2021-08-03 10:59:03.131 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.131 CEST [28] ERROR:  relation "idx_teammembers_team_id" does not exist
mattermost-db     | 2021-08-03 10:59:03.131 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.131 CEST [28] ERROR:  relation "idx_teams_name" does not exist
mattermost-db     | 2021-08-03 10:59:03.131 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.132 CEST [28] ERROR:  relation "idx_user_access_tokens_token" does not exist
mattermost-db     | 2021-08-03 10:59:03.132 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.132 CEST [28] ERROR:  relation "idx_user_terms_of_service_user_id" does not exist
mattermost-db     | 2021-08-03 10:59:03.132 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.133 CEST [28] ERROR:  relation "idx_users_email" does not exist
mattermost-db     | 2021-08-03 10:59:03.133 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.133 CEST [28] ERROR:  relation "idx_sharedchannelusers_user_id" does not exist
mattermost-db     | 2021-08-03 10:59:03.133 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.133 CEST [28] ERROR:  relation "idx_retentionpolicies_displayname_id" does not exist
mattermost-db     | 2021-08-03 10:59:03.133 CEST [28] STATEMENT:  SELECT $1::regclass
mattermost-db     | 2021-08-03 10:59:03.142 CEST [28] ERROR:  constraint "fk_retentionpolicieschannels_retentionpolicies" for relation "retentionpolicieschannels" already exists
mattermost-db     | 2021-08-03 10:59:03.142 CEST [28] STATEMENT:  
mattermost-db     |             ALTER TABLE RetentionPoliciesChannels
mattermost-db     |             ADD CONSTRAINT FK_RetentionPoliciesChannels_RetentionPolicies
mattermost-db     |             FOREIGN KEY (PolicyId) REFERENCES RetentionPolicies (Id)
mattermost-db     |             ON DELETE CASCADE;
mattermost-db     | 2021-08-03 10:59:03.142 CEST [28] ERROR:  constraint "fk_retentionpoliciesteams_retentionpolicies" for relation "retentionpoliciesteams" already exists
mattermost-db     | 2021-08-03 10:59:03.142 CEST [28] STATEMENT:  
mattermost-db     |             ALTER TABLE RetentionPoliciesTeams
mattermost-db     |             ADD CONSTRAINT FK_RetentionPoliciesTeams_RetentionPolicies
mattermost-db     |             FOREIGN KEY (PolicyId) REFERENCES RetentionPolicies (Id)
mattermost-db     |             ON DELETE CASCADE;
mattermost-app    | {"level":"info","ts":1627981145.7062602,"caller":"mlog/sugar.go:21","msg":"Ensuring Surveybot exists","plugin_id":"com.mattermost.nps"}
mattermost-db     | 2021-08-03 10:59:05.710 CEST [31] ERROR:  duplicate key value violates unique constraint "users_username_key"
mattermost-db     | 2021-08-03 10:59:05.710 CEST [31] DETAIL:  Key (username)=(surveybot) already exists.
mattermost-db     | 2021-08-03 10:59:05.710 CEST [31] STATEMENT:  insert into "users" ("id","createat","updateat","deleteat","username","password","authdata","authservice","email","emailverified","nickname","firstname","lastname","position","roles","allowmarketing","props","notifyprops","lastpasswordupdate","lastpictureupdate","failedattempts","locale","timezone","mfaactive","mfasecret","remoteid") values ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26);
mattermost-app    | {"level":"info","ts":1627981145.7717543,"caller":"app/server.go:1179","msg":"Starting Server..."}
mattermost-app    | {"level":"info","ts":1627981145.7721815,"caller":"app/server.go:1255","msg":"Server is listening on [::]:8065","address":"[::]:8065"}
mattermost-app    | {"level":"error","ts":1627981149.3193753,"caller":"log/log.go:181","msg":"[ERR] plugin: plugin acceptAndServe error: timeout waiting for accept","source":"stdlog"}
mattermost-app    | {"level":"error","ts":1627981150.6999316,"caller":"log/log.go:181","msg":"[ERR] plugin: plugin acceptAndServe error: timeout waiting for accept","source":"stdlog"}

I then checked another instance I am looking after and see similar idx_* missing errors?
I can find some posts here related to missing idx relations, but they look outdated to me. My two instances are 3 and less than one year old.

Anyone got an idea what might be going on? Is there an easy way to recreate those missing relations?
Can I ignore the constraint exists and adding surveybot errors? Maybe Mattermost is trying to recreate those items whenever the server restarts, no matter if they are there already?

Both Environments

  • Mattermost Version: 5.38.0-rc1
  • Database Schema Version: 5.38.0
  • Database: postgres
  • Docker on Ubuntu
1 Like

cc @agnivade @streamer45 checking if you’re familiar with this issue ^

@tomz

We’ve recently deleted some redundant indexes to improve space and performance. Those errors are expected at the db level (most of the log lines seem to come from there) since we check for existence of the indexes before deleting them during the migrations and only act if no error is returned.

1 Like

@streamer45, @amy.blais, thanks for picking this up, sounds like there is no reason to worry. Thanks for confirming.
Maybe there is a possibility to clean this up in a later release. False errors make it harder for a system admin to automatically trace log files fishing for errors…and they do make me nervous :slight_smile:

2 Likes