Cockroach: genofire's service checklist

[genofire]

Here a list of services and the state of support by cockroach

• ejabberd (untested : 20.01, 20.03)

  • [ ] setup/schema (with fix family)
  • [ ] usage/queries

• grafana log

  • [ ] setup/schema
  • [ ] usage/queries

• powerdns

  • [ ] schema (text_pattern_ops support needed #18566)

• postfix

  • [ ] usage/queries (encoding - #20317)

• roundcube (not tested yet)

• gitlab log
  
  
  
  • [ ] setup/schema (issue on gitlab)
  
  
  • [ ] usage/queries
  
  
• nextcloud log
  
  
  
  • [ ] setup/schema
  
  
  • [ ] usage/queries
  
  
• pretix / django log
  
  
  
  • [ ] setup/schema
    
    

    - [ ] usage/queries

  
  

hope it helps to take a look at state of cockroach in productive use (beside of golang gorm applications)

[Bessonov]

maybe interesting: https://gitlab.com/gitlab-org/gitlab-ce/issues/51431

[genofire]

Nextcloud still has schema problems:

SQLSTATE[XX000]: Internal error: 7 ERROR:  index "oauth2_client_id_idx" being dropped, try again later
SQLSTATE[XX000]: Internal error: 7 ERROR:  index "calobjects_index" is in use as unique constraint (use CASCADE if you really want to drop it)  
SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR:  lastval(): lastval is not yet defined in this session

[genofire]

Grafana _Version 6.3.5 (commit: 67bad726f1)_ still has schema problems:

Sep 25 01:28:20 db grafana-server[5373]: t=2019-09-25T01:28:20+0200 lvl=eror msg="Executing migration failed" logger=migrator id="Update dashboard title length" error="pq: unimplemented: type conversion not yet implemented"
Sep 25 01:28:20 db grafana-server[5373]: t=2019-09-25T01:28:20+0200 lvl=eror msg="Exec failed" logger=migrator error="pq: unimplemented: type conversion not yet implemented" sql="ALTER TABLE \"dashboard\" ALTER \"title\" TYPE VARCHAR(189);"

[genofire]

Gitlab _version12.3.1_ still has setup problems:

Dropped database 'gitlab'                                                                                                                                                                                                         
Created database 'gitlab'                                                                                                                                                                                                         
-- enable_extension("pg_trgm")                                                                                                                                                                                                    
rake aborted!                                                                                                                                                                                                                     
ActiveRecord::StatementInvalid: PG::FeatureNotSupported: ERROR:  syntax error: unimplemented at or near "EOF"                                                                                                                     
DETAIL:  source SQL:                                                                                                                                                                                                              
CREATE EXTENSION IF NOT EXISTS "pg_trgm"                                                                                                                                                                                          
                                        ^                                                                                                                                                                                         
HINT:  This feature is not yet implemented in CockroachDB.                                                                                                                                                                        

Please check https://github.com/cockroachdb/cockroach/issues to check                                                                                                                                                             
whether this feature is already tracked. If you cannot find it there,                                                                                                                                                             
please report this error with reproduction steps at:                                                                                                                                                                              

    https://github.com/cockroachdb/cockroach/issues/new/choose                                                                                                                                                                    

If you would rather not post publicly, please contact us directly at:                                                                                                                                                             

    [email protected]                                                                                                                                                                                                     

The Cockroach Labs team appreciates your feedback.                                                                                                                                                                                

: CREATE EXTENSION IF NOT EXISTS "pg_trgm"   
...

[jordanlewis]

@genofire, CockroachDB will very likely never support CREATE EXTENSION, which is how PostgreSQL loads C extension modules.

[genofire]

okay, i just want to track here - why it is not possible to use cockroachDB for this application ATM

[genofire]

Pretix _version 3.2_ still has setup problems:

psycopg2.errors.FeatureNotSupported: syntax error: unimplemented: unimplemented at or near "deferred"
DETAIL:  source SQL:                                    
ALTER TABLE "auth_permission" ADD CONSTRAINT "auth_permission_content_type_id_2f476e4b_fk_django_co" FOREIGN KEY ("content_type_id") REFERENCES "django_content_type" ("id") DEFERRABLE INITIALLY DEFERRED
                                                                                                                                                                                                  ^
HINT:  See: https://github.com/cockroachdb/cockroach/issues/31632

[deepeshanandparab]

django.db.utils.NotSupportedError: syntax error: unimplemented: unimplemented at or near "deferred"
DETAIL: source SQL:
ALTER TABLE "auth_permission" ADD CONSTRAINT "auth_permission_content_type_id_2f476e4b_fk_django_co" FOREIGN KEY ("content_type_id") REFERENCES "django_content_type" ("id") DEFERRABLE INITIALLY DEFERRED

[MEschenbacher]

ejabberd seems to have started implementing support for using cockroachdb:
https://www.process-one.net/blog/ejabberd-20-01/
https://www.process-one.net/blog/ejabberd-20-03/

[MEschenbacher]

I'm successfully running a very low volume ejabberd 20.04 (old database schema) on cockroachdb 20.1. I exported from a postgresql using pg_dump, modified the dump by removing sequences and doing the "family" dance

sed -e '/CREATE SEQUENCE/{:a;N;/;/!ba};/START WITH/d' -e 's/\bfamily\b/"family"/g' -e '/_id_seq/d' -e '/_stateid_seq/d' -e '/spool_seq_seq/d' -e '/nodeid_seq/d' ejabberd.sql > ejabberd1.sql

and imported to cockroachdb using

IMPORT PGDUMP 'nodelocal://1/ejabberd1.sql';

After the import, I needed to add some statements to reproduce the serial types:

alter table archive alter column id set default unique_rowid();
alter table pubsub_state alter column stateid set default unique_rowid();
alter table pubsub_node alter column nodeid set default unique_rowid();
alter table privacy_list alter column id set default unique_rowid();
alter table spool alter column seq set default unique_rowid();

I will switch to the new ejabberd database schema in a few days.

[fire]

Do you know the current status of nextcloud?