Cockroach: Auto Increment function

[WhiteTrashLord]

I have thought about your current SERIAL data type and came to the conclusion that I don't like it for all use cases. For example if I make a table for User data I would prefer to have very small INT user IDs.
So I would highly appreciate if you could add something like an auto increment function. Even if it's much slower. I think it's always good to give developers some more options.

[mahaben]

I agree with @WhiteTrashLord. It would be nice if you could provide something like an auto increment function instead of being obliged to use the serial data type!

[petermattis]

@WhiteTrashLord, @mahaben Is there a performance threshold at which this stops making sense? An auto-increment function requires central coordination of the counter. This might double the time to perform an insert.

I think it's always good to give developers some more options.

Many language and system designers would disagree with this. Your system can end up a confusing mess if this is taken to the extreme. This isn't meant to argue against your suggestion. I imagine the lack of small integer keys for an auto-increment type is surprising to many.

PS This is likely a dup of https://github.com/cockroachdb/cockroach/issues/5811

[bdarnell]

Note that while it's not a function, you can get this behavior today with a subquery: INSERT INTO tbl (id, ...) VALUES ((SELECT ifnull(max(id), 0)+1 FROM tbl), ...);. This guarantees a gapless monotonic sequence if you're willing to pay the performance price. (if you can tolerate gaps you can improve performance by allocating the counter in a separate transaction)

[rjnn]

@vilterp i assume you're no longer working on this task? I'm bumping the milestone to 2.1. Feel free to unassign yourself and reassign it to PMs for triage.

[bdarnell]

I think now that we have sequences this can just be closed.