[YSQL] Tserver sequence cache isn't invalidated after a sequence's currval is updated

[mislam-yb]

Jira Link: DB-5899

Description

This issue is a consequence of the solution to issue 15642. When an alter sequence statement with restart is made, the tserver cache for that sequence is invalid. Similarly, when setval is called, it is also invalid. In these cases we should invalidate the cache entry.

Reproduction:

Create a cluster with the gflag ysql_sequence_cache_method=server. Next run the following:

yugabyte=# create sequence s;
CREATE SEQUENCE
yugabyte=# select nextval('s');
 nextval 
---------
       1
(1 row)

yugabyte=# alter sequence s restart;
ALTER SEQUENCE
yugabyte=# select nextval('s');
 nextval 
---------
       2
(1 row)

yugabyte=# select setval('s', 1);
 setval 
--------
      1
(1 row)

yugabyte=# select nextval('s');
 nextval 
---------
       3
(1 row)

Notice that the sequence isn't restarted, nor set to 1.

For alter statements, this issue can be fixed by invalidating all tserver sequence caches when the catalog version, or some similar version, is incremented. However, this does not follow postgres behavior, where only the cached values for the altered sequence is cleared. Further, this is not necessary for alter statements that only change metadata. To follow Postgres behavior, a more explicit invalidation message may be used that specifies the sequence whose cache entry needs to be cleared.

Note that setval may not currently increment the catalog version, which would require some additional work such as broadcasting to all tservers that they need to invalidate their caches.

Warning: Please confirm that this issue does not contain any sensitive information

• [X] I confirm this issue does not contain any sensitive information.

[dmagda]

Voting up for a resolution to the issue. An open source user needed the ysql_sequence_cache_method=server and also had to use the ALTER SEQUENCE name RESTART WITH X to support one of the use cases. Just flagging.