dws
- 开发指南
- 欢迎
- 系统概述
- 数据库使用入门
- 最佳实践
- 教程:调优表设计
- 教程:从OBS导入数据到集群
- 教程:使用GDS从远端服务器导入数据
- 管理数据库安全
- 开发设计建议
- 导入数据
- 导出数据
- 查询外部数据
- 使用Express查询OBS上的数据
- PostGIS Extension
- 配置GUC参数
- 资源负载管理
- PL/Java自定义函数
- 优化查询性能
- SQL参考
- DWS SQL
- DWS与Postgres SQL
- 关键字
- 数据类型
- 常量与宏
- 函数和操作符
- 表达式
- 类型转换
- 系统操作
- 事务控制
- DDL语法一览表
- DML语法一览表
- DCL语法一览表
- SQL语法
- ABORT
- ALTER DATABASE
- ALTER DEFAULT PRIVILEGES
- ALTER EXTENSION
- ALTER FOREIGN TABLE (For GDS)
- ALTER FOREIGN TABLE (For HDFS or OBS)
- ALTER FUNCTION
- ALTER GROUP
- ALTER INDEX
- ALTER LARGE OBJECT
- ALTER NODE
- ALTER NODE GROUP
- ALTER RESOURCE POOL
- ALTER ROLE
- ALTER SCHEMA
- ALTER SEQUENCE
- ALTER SERVER
- ALTER SESSION
- ALTER SYSTEM KILL SESSION
- ALTER TABLE
- ALTER TABLE PARTITION
- ALTER TEXT SEARCH CONFIGURATION
- ALTER TRIGGER
- ALTER TYPE
- ALTER USER
- ALTER VIEW
- ANALYZE | ANALYSE
- BEGIN
- CALL
- CHECKPOINT
- CLEAN CONNECTION
- CLOSE
- CLUSTER
- COMMENT
- COMMIT | END
- COMMIT PREPARED
- COPY
- CREATE BARRIER
- CREATE DATABASE
- CREATE EXTENSION
- CREATE FOREIGN TABLE (GDS导入导出)
- CREATE FOREIGN TABLE (OBS导入导出)
- CREATE FOREIGN TABLE (SQL on Hadoop or OBS)
- CREATE FUNCTION
- CREATE GROUP
- CREATE INDEX
- CREATE NODE
- CREATE NODE GROUP
- CREATE PROCEDURE
- CREATE RESOURCE POOL
- CREATE ROLE
- CREATE SCHEMA
- CREATE SEQUENCE
- CREATE SERVER
- CREATE TABLE
- CREATE TABLE AS
- CREATE TABLE PARTITION
- CREATE TEXT SEARCH CONFIGURATION
- CREATE TRIGGER
- CREATE TYPE
- CREATE USER
- CREATE VIEW
- CURSOR
- DEALLOCATE
- DELETE
- DO
- DROP DATABASE
- DROP EXTENSION
- DROP FOREIGN TABLE
- DROP FUNCTION
- DROP GROUP
- DROP INDEX
- DROP NODE
- DROP NODE GROUP
- DROP OWNED
- DROP PROCEDURE
- DROP RESOURCE POOL
- DROP ROLE
- DROP SCHEMA
- DROP SEQUENCE
- DROP SERVER
- DROP TABLE
- DROP TEXT SEARCH CONFIGURATION
- DROP TRIGGER
- DROP TYPE
- DROP USER
- DROP VIEW
- EXECUTE
- EXECUTE DIRECT
- EXPLAIN
- FETCH
- GRANT
- INSERT
- LOCK
- MOVE
- MERGE INTO
- PREPARE
- PREPARE TRANSACTION
- REASSIGN OWNED
- REINDEX
- RELEASE SAVEPOINT
- RESET
- REVOKE
- ROLLBACK
- ROLLBACK PREPARED
- ROLLBACK TO SAVEPOINT
- SAVEPOINT
- SELECT
- SELECT INTO
- SET
- SET CONSTRAINTS
- SET ROLE
- SET SESSION AUTHORIZATION
- SET TRANSACTION
- SHOW
- START TRANSACTION
- TRUNCATE
- UPDATE
- VACUUM
- VALUES
- GIN索引
- 存储过程
- 系统表和系统视图
- 系统表和系统视图概述
- 系统表
- GS_OBSSCANINFO
- GS_WLM_EC_OPERATOR_INFO
- GS_WLM_OPERATOR_INFO
- GS_WLM_SESSION_INFO
- PG_AGGREGATE
- PG_AM
- PG_AMOP
- PG_AMPROC
- PG_APP_WORKLOADGROUP_MAPPING
- PG_ATTRDEF
- PG_ATTRIBUTE
- PG_AUTHID
- PG_AUTH_HISTORY
- PG_AUTH_MEMBERS
- PG_CAST
- PG_CLASS
- PG_COLLATION
- PG_CONSTRAINT
- PG_CONVERSION
- PG_DATABASE
- PG_DB_ROLE_SETTING
- PG_DEFAULT_ACL
- PG_DEPEND
- PG_DESCRIPTION
- PG_DIRECTORY
- PG_ENUM
- PG_EXTENSION
- PG_EXTENSION_DATA_SOURCE
- PG_FOREIGN_DATA_WRAPPER
- PG_FOREIGN_SERVER
- PG_FOREIGN_TABLE
- PG_INDEX
- PG_INHERITS
- PG_JOB
- PG_JOB_PROC
- PG_LANGUAGE
- PG_LARGEOBJECT
- PG_LARGEOBJECT_METADATA
- PG_NAMESPACE
- PG_OBSSCANINFO
- PG_OPCLASS
- PG_OPERATOR
- PG_OPFAMILY
- PG_PARTITION
- PG_PLTEMPLATE
- PG_PROC
- PG_RANGE
- PG_RESOURCE_POOL
- PG_REWRITE
- PG_SECLABEL
- PG_SHDEPEND
- PG_SHDESCRIPTION
- PG_SHSECLABEL
- PG_STATISTIC
- PG_STATISTIC_EXT
- PG_TABLESPACE
- PG_TRIGGER
- PG_TS_CONFIG
- PG_TS_CONFIG_MAP
- PG_TS_DICT
- PG_TS_PARSER
- PG_TS_TEMPLATE
- PG_TYPE
- PG_USER_MAPPING
- PG_USER_STATUS
- PG_WORKLOAD_GROUP
- PGXC_CLASS
- PGXC_GROUP
- PGXC_NODE
- 系统视图
- ALL_ALL_TABLES
- ALL_CONSTRAINTS
- ALL_CONS_COLUMNS
- ALL_COL_COMMENTS
- ALL_DEPENDENCIES
- ALL_IND_COLUMNS
- ALL_INDEXES
- ALL_OBJECTS
- ALL_PROCEDURES
- ALL_SEQUENCES
- ALL_SOURCE
- ALL_TAB_COLUMNS
- ALL_TAB_COMMENTS
- ALL_TABLES
- ALL_USERS
- ALL_VIEWS
- DBA_DATA_FILES
- DBA_USERS
- DBA_COL_COMMENTS
- DBA_CONSTRAINTS
- DBA_CONS_COLUMNS
- DBA_IND_COLUMNS
- DBA_IND_EXPRESSIONS
- DBA_IND_PARTITIONS
- DBA_INDEXES
- DBA_OBJECTS
- DBA_PART_INDEXES
- DBA_PART_TABLES
- DBA_PROCEDURES
- DBA_SEQUENCES
- DBA_SOURCE
- DBA_TAB_COLUMNS
- DBA_TAB_COMMENTS
- DBA_TAB_PARTITIONS
- DBA_TABLES
- DBA_TABLESPACES
- DBA_TRIGGERS
- DBA_VIEWS
- DUAL
- GS_ALL_CONTROL_GROUP_INFO
- GS_CLUSTER_RESOURCE_INFO
- GS_SESSION_CPU_STATISTICS
- GS_SESSION_MEMORY_STATISTICS
- GS_SQL_COUNT
- GS_WLM_CGROUP_INFO
- GS_WLM_EC_OPERATOR_STATISTICS
- GS_WLM_EC_OPERATOR_HISTORY
- GS_WLM_OPERATOR_HISTORY
- GS_WLM_OPERATOR_STATISTICS
- GS_WLM_SESSION_HISTORY
- GS_WLM_SESSION_STATISTICS
- GS_WLM_WORKLOAD_RECORDS
- GS_STAT_DB_CU
- GS_STAT_SESSION_CU
- GS_TOTAL_NODEGROUP_MEMORY_DETAIL
- PG_AVAILABLE_EXTENSION_VERSIONS
- PG_AVAILABLE_EXTENSIONS
- PG_COMM_DELAY
- PG_COMM_STATUS
- PG_COMM_RECV_STREAM
- PG_COMM_SEND_STREAM
- PG_CONTROL_GROUP_CONFIG
- PG_CURSORS
- PG_EXT_STATS
- PG_GET_INVALID_BACKENDS
- PG_GET_SENDERS_CATCHUP_TIME
- PG_GROUP
- PG_INDEXES
- PG_LOCKS
- PG_NODE_ENV
- PG_OS_THREADS
- PG_POOLER_STATUS
- PG_PREPARED_STATEMENTS
- PG_PREPARED_XACTS
- PG_REPLICATION_SLOTS
- PG_ROLES
- PG_RULES
- PG_RUNNING_XACTS
- PG_SECLABELS
- PG_SESSION_WLMSTAT
- PG_SESSION_IOSTAT
- PG_SETTINGS
- PG_SHADOW
- PG_SHARED_MEMORY_DETAIL
- PG_STATS
- PG_STAT_ACTIVITY
- PG_STAT_ALL_INDEXES
- PG_STAT_ALL_TABLES
- PG_STAT_BAD_BLOCK
- PG_STAT_BGWRITER
- PG_STAT_DATABASE
- PG_STAT_DATABASE_CONFLICTS
- PG_STAT_GET_MEM_MBYTES_RESERVED
- PG_STAT_USER_FUNCTIONS
- PG_STAT_USER_INDEXES
- PG_STAT_USER_TABLES
- PG_STAT_REPLICATION
- PG_STAT_SYS_INDEXES
- PG_STAT_SYS_TABLES
- PG_STAT_XACT_ALL_TABLES
- PG_STAT_XACT_SYS_TABLES
- PG_STAT_XACT_USER_FUNCTIONS
- PG_STAT_XACT_USER_TABLES
- PG_STATIO_ALL_INDEXES
- PG_STATIO_ALL_SEQUENCES
- PG_STATIO_ALL_TABLES
- PG_STATIO_SYS_INDEXES
- PG_STATIO_SYS_SEQUENCES
- PG_STATIO_SYS_TABLES
- PG_STATIO_USER_INDEXES
- PG_STATIO_USER_SEQUENCES
- PG_STATIO_USER_TABLES
- PG_THREAD_WAIT_STATUS
- PG_TABLES
- PG_TIMEZONE_ABBREVS
- PG_TIMEZONE_NAMES
- PG_TOTAL_MEMORY_DETAIL
- PG_TOTAL_USER_RESOURCE_INFO
- PG_USER
- PG_USER_MAPPINGS
- PG_VIEWS
- PG_WLM_STATISTICS
- PGXC_COMM_DELAY
- PGXC_COMM_RECV_STREAM
- PGXC_COMM_SEND_STREAM
- PGXC_COMM_STATUS
- PGXC_GET_STAT_ALL_TABLES
- PGXC_GET_TABLE_SKEWNESS
- PGXC_NODE_ENV
- PGXC_OS_THREADS
- PGXC_PREPARED_XACTS
- PGXC_RUNNING_XACTS
- PGXC_STAT_ACTIVITY
- PGXC_STAT_BAD_BLOCK
- PGXC_SQL_COUNT
- PGXC_THREAD_WAIT_STATUS
- PGXC_TOTAL_MEMORY_DETAIL
- PGXC_WLM_EC_OPERATOR_HISTORY
- PGXC_WLM_EC_OPERATOR_INFO
- PGXC_WLM_EC_OPERATOR_STATISTICS
- PGXC_WLM_OPERATOR_HISTORY
- PGXC_WLM_OPERATOR_INFO
- PGXC_WLM_OPERATOR_STATISTICS
- PGXC_WLM_SESSION_INFO
- PGXC_WLM_SESSION_HISTORY
- PGXC_WLM_SESSION_STATISTICS
- PGXC_WLM_WORKLOAD_RECORDS
- PV_FILE_STAT
- PV_OS_RUN_INFO
- PV_SESSION_MEMORY
- PV_SESSION_MEMORY_DETAIL
- PV_SESSION_STAT
- PV_SESSION_TIME
- PV_TOTAL_MEMORY_DETAIL
- USER_COL_COMMENTS
- USER_CONSTRAINTS
- USER_CONS_COLUMNS
- USER_INDEXES
- USER_IND_COLUMNS
- USER_IND_EXPRESSIONS
- USER_IND_PARTITIONS
- USER_JOBS
- USER_OBJECTS
- USER_PART_INDEXES
- USER_PART_TABLES
- USER_PROCEDURES
- USER_SEQUENCES
- USER_SOURCE
- USER_TAB_COLUMNS
- USER_TAB_COMMENTS
- USER_TAB_PARTITIONS
- USER_TABLES
- USER_TRIGGERS
- USER_VIEWS
- V$SESSION
- V$SESSION_LONGOPS
- Information Schema
- 全文检索
- 术语表
- 修订记录
SEQUENCE函数
链接复制成功!
更新时间:
2019/11/12 GMT+08:00
序列函数为用户从序列对象中获取后续的序列值提供了简单的多用户安全的方法。
- nextval(regclass)
描述:递增序列并返回新值。
- 为了避免从同一个序列获取值的并发事务被阻塞, nextval操作不会回滚;也就是说,一旦一个值已经被抓取, 那么就认为它已经被用过了,并且不会再被返回。 即使该操作处于事务中,当事务之后中断,或者如果调用查询结束不使用该值,也是如此。这种情况将在指定值的顺序中留下未使用的"空洞"。 因此,DWS序列对象不能用于获得"无间隙"序列。
- 如果nextval被下推到DN上时,各个DN会自动连接GTM,请求next values值,例如(insert into t1 select xxx,t1某一列需要调用nextval函数),由于GTM上有最大连接数为8192的限制,而这类下推语句会导致消耗过多的GTM连接数,因此对于这类语句的并发数目限制为7000(其它语句需要占用部分连接)/集群DN数目。
返回类型:bigint
示例:
select nextval('seqDemo'); nextval --------- 2 (1 row)
- currval(regclass)
返回当前会话里最近一次nextval返回的指定的sequence的数值。如果当前会话还没有调用过指定的sequence的nextval,那么调用currval将会报错。需要注意的是,这个函数在默认情况下是不支持的,需要通过设置enable_beta_features为true之后,才能使用这个函数。同时在设置enable_beta_features为true之后,nextval()函数将不支持下推。
返回类型:bigint
示例:
select currval('seq1'); currval --------- 2 (1 row)
- lastval()
描述:返回当前会话里最近一次nextval返回的数值。这个函数等效于currval,只是它不用序列名为参数,它抓取当前会话里面最近一次nextval使用的序列。如果当前会话还没有调用过nextval,那么调用lastval将会报错。
需要注意的是,这个函数在默认情况下是不支持的,需要通过设置enable_beta_features或者lastval_supported为true之后,才能使用这个函数。同时这种情况下,nextval()函数将不支持下推。
返回类型:bigint
示例:
select lastval(); lastval --------- 2 (1 row)
- setval(regclass, bigint)
描述:设置序列的当前数值。
返回类型:bigint
示例:
select setval('seqDemo',1); setval -------- 1 (1 row)
- setval(regclass, bigint, boolean)
描述:设置序列的当前数值以及is_called标志。
返回类型:bigint
示例:
select setval('seqDemo',1,true); setval -------- 1 (1 row)
Setval后当前会话及GTM上会立刻生效,但如果其他会话有缓存的序列值,只能等到缓存值用尽才能感知Setval的作用。所以为了避免序列值冲突,setval要谨慎使用。
因为序列是非事务的,setval造成的改变不会由于事务的回滚而撤销。
父主题:
函数和操作符
相关文档
相关产品



