功能介绍
Oracle数据库在创建sequence的时候可以支持设置maxvalue 为9999999999999999999999999999,这样的SQL在LightDB23.3版本之前都是执行失败的。为了方便Oracle用户迁移到LightDB上,在LightDB23.3版本上,增加了sequence支持maxvalue设置为9999999999999999999999999999的语法兼容。
如果设置的maxvalue的值大于INT64_MAX(9223372036854775807),则设置maxvalue为sequence类型所对应的最大值。
使用说明
- 创建sequence时设置maxvalue为9999999999999999999999999999
lightdb@lt_test=# create sequence s1 maxvalue 9999999999999999999999999999;
CREATE SEQUENCE
lightdb@lt_test=# \d+ s1
Sequence "public.s1"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
lightdb@lt_test=#
- 修改sequence时设置maxvalue为9999999999999999999999999999
lightdb@lt_test=# create sequence s2 maxvalue 100000;
CREATE SEQUENCE
lightdb@lt_test=# \d+ s2
Sequence "public.s2"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------+-----------+---------+-------
bigint | 1 | 1 | 100000 | 1 | no | 1
lightdb@lt_test=#
lightdb@lt_test=# alter sequence s2 maxvalue 9999999999999999999999999999;
ALTER SEQUENCE
lightdb@lt_test=# \d+ s2
Sequence "public.s2"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
lightdb@lt_test=#
- 当sequence类型为smallint时,设置maxvalue为9999999999999999999999999999
lightdb@lt_test=# create sequence s3 as smallint maxvalue 1000;
CREATE SEQUENCE
lightdb@lt_test=# \d+ s3
Sequence "public.s3"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
----------+-------+---------+---------+-----------+---------+-------
smallint | 1 | 1 | 1000 | 1 | no | 1
lightdb@lt_test=# alter sequence s3 maxvalue 9999999999999999999999999999;
ALTER SEQUENCE
lightdb@lt_test=# \d+ s3
Sequence "public.s3"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
----------+-------+---------+---------+-----------+---------+-------
smallint | 1 | 1 | 32767 | 1 | no | 1
lightdb@lt_test=#