sql: sequences should be automatically added to a table for postgres compatibility

[BramGruneir]

This came out of hibernate's test suite, the following tests fail:

• org.hibernate.engine.spi.ExtraStateTest shouldMaintainExtraStateWhenUsingIdentityIdGenerationStrategy
• org.hibernate.id.CreateDeleteTest createAndDeleteAnEntityInTheSameTransactionTest
• org.hibernate.id.FlushIdGenTest testPersistBeforeTransaction

Due to a sequence not existing.

create table ChineseTakeawayRestaurant (
  id  bigserial not null,
  gobelinStars int4 not null,
  primary key (id)
)
insert into ChineseTakeawayRestaurant (gobelinStars) 
  values 3

select currval('ChineseTakeawayRestaurant_id_seq')

Postgres automatically creates a sequence whenever a serial or big serial type is used. We don't do this.

[vilterp]

We could change our serial type to use sequences, but that would make it slower, so would probably be a bad choice for most users.

Without doing that, it's unclear what would trigger autocreating the sequence — maybe if we see a CREATE TABLE with my_col INT DEFAULT nextval('my_seq') we'd autocreate my_seq, but that default expression isn't what Hibernate generates anyway.

[knz]

I'd like to challenge the idea that "serial using sequences would be a bad choice". I think we're gaining more with proper pg compat on this one. We should then propose a crdb-specific feature for replacement if users want more performance.

[vilterp]

The compat win would definitely be nice. Maybe we could try switching our TPCC benchmark roachtest to use sequences, and check the perf impact? I suspect it will slow things down a lot, but I'm not sure.

Also, a lot could be done to improve perf of sequences, such as pre-allocating values in memory for each node. This seems par for the course even on single-node databases (see Postgres CACHE option).

[knz]

yes a sequence cache is what I had in mind. We'll do some brainstorming about that I'm sure. The overarching problem is to solve #26925.