sql: operations against the table backing sequences should be non-transactional

[ajwerner]

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

Sequences currently are backed by a table with a single row. Operations against that table are already irregular in so far as they use the KV IncrementRequest which is not exposed to SQL.
Currently operations against the backing table are transactional and can use features such as historical reads.

The sequence-as-data-source behavior should be to also skip the current kv object and read the latest mvcc value.

The same behavior exists in postgres: reads from sequences do not operate at the current txn read snapshot but instead to the latest sequence state.

Check as follows:

1. in terminal A, create sequence s then issue BEGIN; SELECT * FROM s; -- then observe the result.

2. in terminal B, run SELECT nextval('s') a couple of times

3. back in terminal A, still inside the txn, run SELECT * FROM s again. Observe: the value has changed. Reads from sequences are non-transactional.

Describe the solution you'd like

Avoid using the current transaction when accessing sequences.

Describe alternatives you've considered

To leave the behavior as is and move it to a deprecated form of sequences and to introduce a new sequence type with better semantics.

Additional context

This work is in anticipation of making sequences my efficient by side-stepping MVCC for its operations.

Jira issue: CRDB-5324

Epic CRDB-60873

[ajwerner]

@knz does this capture the bug?

[yangxuanjia]

@ajwerner
I test from your step, I found sequence is in transaction. and I think it's right.
if sequence is non-transaction, the isolation will because Read-Committed, which is not good.
But GC sequence table history keys maybe need non-transaction.

A Term:

>BEGIN;
Query OK, 0 rows affected (0.00 sec)
>select * from t100_id_seq;
+------------+---------+-----------+
| last_value | log_cnt | is_called |
+------------+---------+-----------+
|         28 |       0 |      TRUE |
+------------+---------+-----------+
1 row in set (0.00 sec)

B Term:

>SELECT nextval('t100_id_seq');
+------------------------+
| nextval('t100_id_seq') |
+------------------------+
|                     29 |
+------------------------+
1 row in set (0.07 sec)

A Term:

>select * from t100_id_seq;
+------------+---------+-----------+
| last_value | log_cnt | is_called |
+------------+---------+-----------+
|         28 |       0 |      TRUE |
+------------+---------+-----------+
1 row in set (0.00 sec)
>commit;
Query OK, 0 rows affected (0.00 sec)
>select * from t100_id_seq;
+------------+---------+-----------+
| last_value | log_cnt | is_called |
+------------+---------+-----------+
|         29 |       0 |      TRUE |
+------------+---------+-----------+
1 row in set (0.00 sec)

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!