CREATE SEQUENCE s VIRTUAL: Add ability to use unordered_unique_rowid() as source

[mgoddard]

Is your feature request related to a problem? Please describe.

We've got a Hibernate app which is using sequences and seeing performance issues due to these sequential primary key values. @ajwerner suggested using unordered_unique_rowid() as the source of these PK values, which is a great idea, though it would require altering the Hibernate app.

Describe the solution you'd like

Currently, we have the CREATE SEQUENCE s VIRTUAL syntax, which returns unique_rowid() values, but these are not as broadly distributed as values provided by unordered_unique_rowid(). If there is a way to toggle between these methods for generating the next value in the sequence, it would help us to mitigate this issue with our Hibernate app. I suspect this would be broadly applicable as we migrate more and more apps off traditional RDBMS systems onto CockroachDB.

Describe alternatives you've considered A clear and concise description of any alternative solutions or features you've considered. We've tried using CACHE 1000 with the sequences (to eliminate the nextval() calls being a bottlneck) and USING HASH on the primary keys.

Jira issue: CRDB-19256

[blathers-crl[bot]]

Hello, I am Blathers. I am here to help you get the issue triaged.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-experience (found keywords: Hibernate)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rafiss]

if you use the setting serial_normalization=unordered_rowid you can use a SERIAL column type that is backed by unordered_unique_rowid

I see that the docs have this at https://www.cockroachlabs.com/docs/stable/serial.html#generated-values-for-mode-unordered_rowid but the surrounding sections are not complete

[ajwerner]

if you use the setting serial_normalization=unordered_rowid you can use a SERIAL column type that is backed by unordered_unique_rowid

That's true, but it doesn't allow you to have invocations of nextval give you such a value. See https://github.com/cockroachdb/cockroach/blob/b6dc58b2b4253967fceebfeabecb5881290283db/pkg/sql/sequence.go#L124