[YSQL] Enable support for sequences #665
Assignees
Labels
kind/enhancement
This is an enhancement of an existing feature
Comments
kmuthukk
added
the
kind/enhancement
yugabyte-ci
pushed a commit
that referenced
this issue
Mar 12, 2019
Summary:
This diff adds support for:
- CREATE SEQUENCE
- DROP SEQUENCE
- nextval
- currval
- lastval
- Create a table with columns of serial types
Our implementation of sequences uses one replicated user level table to store all sequences data. This table has four columns: db_oid, rel_oid, last_val, and is_called. The first two columns uniquely identify a sequence. last_val and is_called is the data necessary to determine what is the next value in the sequence. Our implementation differs from Postgres implementation by using one row in a special table as opposed to using a one-row table to store the same data.
Initially, when a sequence is created, an RPC to insert a new row is sent to the tserver that is the leader of the tablet which will store the new row. Initially last_val is set to the start value (default 1), and is_called is set to false. is_called is false when last_val hasn't been used. In other words, if nextval() reads the sequence data, it returns last_val if is_called is false. It returns last_val + increment otherwise.
Postgres implementation of nextval locks the sequence table, reads the data, checks whether incrementing (possibly by a negative value) would violate any constrains, writes the new values for last_val and is_called, and then it unlocks the table. In YugaByte's implementation we cannot lock the data table because this table is shared among all the sequences. Instead, a nextval() call sends a read RPC to read the current values `last_val` and `is_called`, constraints are then evaluated for these values, and if no errors occurred, we do a conditional update (update the row only if the values haven't changed since we last read them). If the conditional update fails, we retry the whole operation again: read data, check constraints, update data. Because our implementation uses two RPCs each time we increment `last_val` or change `is_called`, the performance of a default sequence (with `CACHE` set to 1) will be much lower than a similar sequence in Postgres. To minimize this, the user should use a cache sufficiently large to avoid issuing two RPCs for each sequence value requested through nextval(). The disadvantage of this approach, is that once a block of cache numbers has been generated, any unused numbers from the cache will be lost forever.
Pending:
- Support for CYCLE option
- ALTER SEQUENCE
- setval
Test Plan:
Manual for now. Tests coming soon:
```
postgres=# create sequence s1 increment 3 start 100 cache 1000 ;
CREATE SEQUENCE
postgres=# select nextval('s1');
nextval
---------
100
(1 row)
postgres=# select nextval('s1');
nextval
---------
103
(1 row)
postgres=# ^D\q
dog.local:~/code/yugabyte [postgres_sequence ↓·2↑·1|✚ 5⚑ 7]
14:22 $ ./bin/yb-ctl destroy; ./bin/yb-ctl create --enable_postgres; ./bin/yb-ctl status; ./bin/yb-ctl setup_pg_sequences_table; ./bin/psql -p 5433 -U postgres -h localhost^C
dog.local:~/code/yugabyte [postgres_sequence ↓·2↑·1|✚ 5⚑ 7]
14:22 $ ./bin/psql -p 5433 -U postgres -h localhost
psql (10.4)
Type "help" for help.
postgres=# select nextval('s1');
nextval
---------
3100
(1 row)
postgres=# ^D\q
dog.local:~/code/yugabyte [postgres_sequence ↓·2↑·1|✚ 5⚑ 7]
14:23 $ ./bin/psql -p 5433 -U postgres -h localhost
psql (10.4)
Type "help" for help.
postgres=# select nextval('s1');
nextval
---------
6100
(1 row)
```
```
psql (10.4)
Type "help" for help.
postgres=# create table t(k serial primary key, v int);
insert CREATE TABLE
postgres=# insert into t(v) values (100);
INSERT 0 1
postgres=# insert into t(v) values (101);
INSERT 0 1
postgres=# insert into t(v) values (102);
INSERT 0 1
postgres=# select * from t;
k | v
---+-----
1 | 100
2 | 101
3 | 102
(3 rows)
postgres=# \d t;
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------
k | integer | | not null | nextval('t_k_seq'::regclass)
v | integer | | |
postgres=# select nextval('t_k_seq');
nextval
---------
4
(1 row)
postgres=# select currval('t_k_seq');
currval
---------
4
(1 row)
postgres=# select lastval();
lastval
---------
4
(1 row)
```
```
postgres=# SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';
relname
---------
(0 rows)
postgres=# create sequence s1;
CREATE SEQUENCE
postgres=# create table t4(k serial, v int);
CREATE TABLE
postgres=# SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';
relname
----------
s1
t4_k_seq
(2 rows)
postgres=# drop sequence s1;
DROP SEQUENCE
postgres=# drop table t4;
DROP TABLE
postgres=# SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';
relname
----------
t4_k_seq
(1 row)
postgres=# drop sequence t4_k_seq;
DROP SEQUENCE
postgres=# SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';
relname
---------
(0 rows)
```
Reviewers: neil, karthik, mihnea, robert
Reviewed By: robert
Subscribers: kannan, bogdan, neha, yql
Differential Revision: https://phabricator.dev.yugabyte.com/D6128
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add support for sequences
The text was updated successfully, but these errors were encountered: