Cockroach DB support

[stwiname]

Finalize support for cockroach db:

• [ ] Determine what db type is being used, ideally automatically, otherwise add flags
• [ ] Improve error messages for unsupported features (subscriptions, historical?)
• [ ] use different postgraphile dependency with different db?
• [ ] future follow up task: update historical db structue in order to drop btree-gist extension, and replace hot schema reload logic with migration subcommand.

[jiqiang90]

Example:

Working branch : https://github.com/subquery/subql/tree/cockroach-db

cockroach image: https://github.com/subquery/subql/blob/cockroach-db/packages/node/docker-cockroach/docker-compose.yml

subql-node-cockroach: https://hub.docker.com/r/jayji90/cr-subql-node

subql-query-cockroach: https://hub.docker.com/r/jayji90/cr-subql-query

forked: https://www.npmjs.com/package/@subql/x-graphile-build-pg/v/4.12.3-0.1.1-cockroach

---

Some research result with cockroach latest-v22.1

Advantages:

1. Query response faster than regular postgres/RDS , about 1/2 time.

Disadvantages:

1. Cockroach not available at btree extension, this means historical is not supported

2. Not support Drop trigger if exist , this means subscriptions && hot-schema reload not support

   

3. Create or Replace function not work

4. Db write seems slow, at regular postgres, we have 25-30 bps for starter project. Same query on RDS takes 33ms, but 80-110 ms on cockroach.

5. Query failed, need adjust postgraphile introspection query to make it work. Update: see updated @subql/x-graphile-build-pg/v/4.12.3-0.1.1-cockroach

{"level":"error","timestamp":"2022-11-24T02:58:50.074Z","pid":7,"hostname":"subquery-16667-query-deployment-7d6455488c-5hpsd","category":"graphql-module","length":118,"name":"error","severity":"ERROR","code":"42883","file":"name_resolution.go","line":"205","routine":"ResolveFunction","stack":"error: unknown function: pg_index_column_has_property()\n    at Parser.parseErrorMessage (/usr/local/lib/node_modules/@subql/query/node_modules/pg-protocol/dist/parser.js:287:98)\n    at Parser.handlePacket (/usr/local/lib/node_modules/@subql/query/node_modules/pg-protocol/dist/parser.js:126:29)\n    at Parser.parse (/usr/local/lib/node_modules/@subql/query/node_modules/pg-protocol/dist/parser.js:39:38)\n    at Socket.<anonymous> (/usr/local/lib/node_modules/@subql/query/node_modules/pg-protocol/dist/index.js:11:42)\n    at Socket.emit (node:events:513:28)\n    at addChunk (node:internal/streams/readable:315:12)\n    at readableAddChunk (node:internal/streams/readable:289:9)\n    at Socket.Readable.push (node:internal/streams/readable:228:10)\n    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)","type":"Error","message":"unknown function: pg_index_column_has_property()"}
/usr/local/lib/node_modules/@subql/query/dist/graphql/graphql.module.js:57
            throw new Error(`create apollo server failed, ${e.message}`);
                  ^

Error: create apollo server failed, Failed to build schema d-16667/454076513/cockroachdb 5 times
    at GraphqlModule.onModuleInit (/usr/local/lib/node_modules/@subql/query/dist/graphql/graphql.module.js:57:19)
    at async callModuleInitHook (/usr/local/lib/node_modules/@subql/query/node_modules/@nestjs/core/hooks/on-module-init.hook.js:51:9)
    at async NestApplication.callInitHook (/usr/local/lib/node_modules/@subql/query/node_modules/@nestjs/core/nest-application-context.js:178:13)
    at async NestApplication.init (/usr/local/lib/node_modules/@subql/query/node_modules/@nestjs/core/nest-application.js:96:9)
    at async NestApplication.listen (/usr/local/lib/node_modules/@subql/query/node_modules/@nestjs/core/nest-application.js:158:33)
    at async /usr/local/lib/node_modules/@subql/query/dist/main.js:29:5
Stream closed EOF for subquery/subquery-16667-query-deployment-7d6455488c-5hpsd (query)

Relate post:

https://github.com/graphile/postgraphile/issues/645

[stwiname]

https://github.com/subquery/subql/pull/1521