sql: support CREATE SEQUENCE

[petermattis]

...and add support for the nextval(), currval() and setval() functions. Note that these functions are atomic, but not transactional. We can implement them using the KV {Inc,Get,Set} operations. We'll need to figure out where to store the sequence key. Postgres says that a sequence creates a special single-row table, which could work for us as well. Something like:

CREATE TABLE sequence (
  val INT64
);

Note that Postgres supports tying the lifetime of a sequence to the lifetime of a specific column in a table. That would require adding a field to ColumnDescriptor that would allowing cascading the deletion of a column (either by dropping the column or the entire table) to deletion of the associated sequences.

[JackKrupansky]

Sort of related to the issue I filed on SERIAL:
sql: support SERIAL PK for appending rows to a table #4491

[maddyblue]

Update: nope, the following comment is wrong, kept for the archives.

One reason to do this as not just a wrapper around unique_rowid() is for sorting. Since a sequence usually results in things being inserted with increasing IDs, you can use those IDs as a sort column (otherwise you'd have to use a column with default NOW() or something). Sure, you get some things out of order, but in general it'll be correct.

[petermattis]

unique_rowid is only slightly less sorted than a sequence backed by KV Inc. Am I missing something?

[maddyblue]

unique_rowid is only sorted per node since the node ID is MSB of the output. A sequence would be sorted per cluster.

[petermattis]

We never made that change. unique_rowid() has the timestamp in the upper bits and the node ID in the lower bits.

[maddyblue]

I thought it was the opposite. Strike my comment then.

[petermattis]

@paperstreet Should we close this issue?

[danhhz]

I think leave it open and stick it in Later until someone needs it. SEQUENCE can be used for other things than implementing SERIAL

[bdarnell]

A need for this was reported in #12481

[dianasaur323]

A user also mentioned an issue with this when trying to load in pg dumped file. @nstewart

[vilterp]

@petermattis Why was lastval left out of this issue? It doesn't seem necessary (since you can always use currval, and probably should to avoid accidentally getting a value from the wrong sequence), but was just wondering what your reasoning was.

[petermattis]

No reason, just an oversight.

[hjames9]

Would it be possible to support the ALTER SEQUENCE support for changing the increment and the starting value? For example:

ALTER SEQUENCE campaigns_id_seq INCREMENT BY 2 START WITH 31337 RESTART WITH 31337;

INCREMENT BY 2 == increments sequence by 2 on every next value
START WITH 31337 == starts the sequence at 31337
RESTART WITH 31337 == Anytime ALTER SEQUENCE RESTART is called, it'll set the starting value back at 31337

[vilterp]

@hjames9 In master, you can ALTER these:

• INCREMENT BY
• START WITH

And support for these is planned (tracking issue here: #19723):

• ALTER SEQUENCE ... RESTART WITH x
• ALTER SEQUENCE ... RESTART

[vilterp]

We shipped this in 2.0, minus a couple small PG compatibility issues, which have been filed with the label A-sql-sequences: A-sql-sequences Sequence handling in SQL