sql: mattermost support

[sokoow]

BUG REPORT

1. CockroachDB 1.1.0

2. Please describe the issue you observed:

• What did you do? Tried to install Mattermost 4.3.0 onto cockroach

• What did you expect to see?

Working DB and product

• What did you see instead?

./bin/platform
[2017/10/19 15:23:46 UTC] [INFO] Loaded system translations for 'en' from '/home/tensible/mattermost/i18n/en.json'
[2017/10/19 15:23:46 UTC] [INFO] Current version is 4.3.0 (4.3.0/Fri Oct 13 22:06:53 UTC 2017/8966452d1183e94fecc373b9d08c65a0573cbbc6/729b149c2a5c82c230e302f0963f6161ca7c652e)
[2017/10/19 15:23:46 UTC] [INFO] Enterprise Enabled: true
[2017/10/19 15:23:46 UTC] [INFO] Current working directory is /home/tensible/mattermost
[2017/10/19 15:23:46 UTC] [INFO] Loaded config file from /home/tensible/mattermost/config/config.json
[2017/10/19 15:23:46 UTC] [INFO] Able to write files to local storage.
[2017/10/19 15:23:46 UTC] [INFO] Server is initializing...
[2017/10/19 15:23:46 UTC] [INFO] Pinging SQL master database
[2017/10/19 15:23:47 UTC] [INFO] The database schema has been set to version 4.3.0
[2017/10/19 15:23:49 UTC] [CRIT] Failed to create index pq: error in argument for $1: unsupported OID 2205 with format code formatText
[2017/10/19 15:23:49 UTC] [CRIT] Failed to create index pq: syntax error at or near "using"

[lgo]

From the postgres reference, 2205 is regclass, which is just a type of postgres oid: https://www.postgresql.org/docs/8.1/static/datatype-oid.html.

The error came from this query.

It looks like all we might need to do for the one error is alias regclass to oid (and possibly do other aliases, listed in the postgres spec). Do we have any mechanism for aliasing types @knz?

[knz]

We already have oid type aliases (regclass, regprocedure, etc). What's missing with them?

[lgo]

Ah, yep looks like the query with the oid alias is fine now :).

The second query that wasn't working has the using keyword in a delete query, which I double checked and we still don't support. It seems to be this this query

DELETE FROM Sessions s USING UserAccessTokens o WHERE o.Token = s.Token AND o.Id = :Id

[sokoow]

Any takers ? I've just checked new version 4.8.0 and it's still not working, when is cockroachdb be a bit more compatible with postgres?

[2018/03/31 18:30:39 IST] [INFO] Loaded system translations for 'en' from '/home/sokoow/Desktop/mattermost/i18n/en.json'
[2018/03/31 18:30:39 IST] [INFO] Server is initializing...
[2018/03/31 18:30:39 IST] [INFO] Pinging SQL master database
[2018/03/31 18:30:40 IST] [INFO] The database schema has been set to version 4.8.0
[2018/03/31 18:30:44 IST] [CRIT] Failed to create index pq: error in argument for $1: unsupported OID 2205 with format code formatText
[2018/03/31 18:30:44 IST] [CRIT] Failed to create index pq: unimplemented at or near ")"

[sokoow]

So, over half a year after, CockroachDB got to version 2.1.1, Mattermost to 5.5.0 so I redid the test and:

$ ./mattermost
{"level":"info","ts":1543447136.8121371,"caller":"utils/i18n.go:78","msg":"Loaded system translations for 'en' from '/home/mattermost/i18n/en.json'"}
{"level":"info","ts":1543447136.812263,"caller":"app/app.go:204","msg":"Server is initializing..."}
{"level":"info","ts":1543447136.815445,"caller":"sqlstore/supplier.go:209","msg":"Pinging SQL master database"}
{"level":"info","ts":1543447136.8191838,"caller":"sqlstore/channel_store_experimental.go:47","msg":"Enabling experimental public channels materialization"}
{"level":"info","ts":1543447147.1298215,"caller":"sqlstore/upgrade.go:101","msg":"The database schema has been set to version 5.5.0"}
{"level":"error","ts":1543447164.8400216,"caller":"sqlstore/supplier.go:727","msg":"Failed to create index pq: error in argument for $1: unsupported OID 2205 with format code FormatText, pq: unimplemented at or near \")\""}

Is this ever going to be fixed ? :D

[knz]

@sokoow we have engaged a new process to manage compatibility problems. In that process we really want to focus on SQL problems and not just symptoms from a higher level in the software stack.

Therefore I would kindly request that you help us help you, by accompanying your test report with the specific SQL and parameters that were sent to CockroachDB.

This can now be obtained with the cluster setting sql.trace.log_statement_execute: set this to true, run your test, collect the exec log, share that with us.

[sokoow]

Excellent! :D Will have it done then

[sokoow]

Attached is the log from my erroneous run

mattermost-cockroach211.tar.gz

One interesting fact: first of all, looking at either these logs and or/UI, your DB doesn't really tell that anything is wrong, this by design ?

[sokoow]

any update ?

[jordanlewis]

Looking at the mattermost source code, looks like it uses at least a couple features we don't support, like GIN full text indexes. We don't have plans to make full text indexing in the near future, unfortunately.

[jordanlewis]

(c.f. https://github.com/mattermost/mattermost-server/blob/master/store/sqlstore/supplier.go)

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 5 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!

[knz]

Let's reopen this, because otherwise folk are going to continue re-opening new issues (e.g https://github.com/cockroachdb/cockroach/issues/86853)

[jordanlewis]

Primarily blocked on PL/PGSQL support it seems. https://github.com/mattermost/mattermost-server/blob/master/db/migrations/postgres/000090_create_enums.up.sql

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!