Knex is not throwing a 40001 when using cockroach db.

[cpaczek]

Environment

Knex version: 2.4.0
Database + version: Cockroach DB Latest
OS: Windows & Docker(Ubuntu)

Bug

This is an extension of: cockroachdb/cockroach#98034 related to strapi/strapi#12346

Basically, there is an issue where cockroach isn't inserting all the rows that it should be. This is a cockroach specific issue.

Here is a reproduction of the bug:
https://github.com/cpaczek/knex-test/

You can run the cockroach version using node ./index.js cockroachdb and you can run a sqllite version of it using node ./index.js sqlite

Note:
This code does cause a race condition where the blog_order increment won't go one by one but please ignore this for this test. I am just copying what strapi has in their codebase.

CockroachDB:

as you can see here

Cockroach db only allows for 17 of the rows to be entered

Sqlite

Whereas with sqlite: (or any other db for that matter)

Theories:

Not sure why this is happening, the leading theory is that maybe Cockroach is being smart and seeing that for some of the inserts the max value has changed and because the insert and the select is apart of the same transaction it will fail the transaction? But I don't have much of a clue

cc: @kibertoad

[cpaczek]

Seems like this issue is being caused by cockroach db being run in serializable isolation mode whereas Postgres, by default, runs in read committed. However, for some reason knex is not throwing a 40001 error when using cockroach where was knex does throw the proper error when using Postgres

Error when running the same code in postgres serializable isolation mode:

No error gets logged when using a cockroach database in both pg dialect and cockroachdb dialect

https://github.com/cpaczek/knex-test/tree/pg-in-serializable-mode