教你识别一些 sequence 的相关问题

CREATE OR REPLACE FUNCTION public.dfm_get_seqence_info(    OUT namespace text,  -- 序列的schema    OUT sequencename text, -- 序列名称    OUT min_value bigint,  -- 序列最小值    OUT max_value bigint,  -- 序列最大值    OUT start_value bigint,  -- 起始值    OUT increment_by bigint,  -- 增加步长    OUT cache_value bigint,  -- cache值    OUT is_cycled boolean -- 是否可以循环使用) RETURNS SETOF record LANGUAGE plpgsql NOT FENCED NOT SHIPPABLEAS $function$DECLARE    rowObject record;    rowDetail record;    queryObjectStr text;    queryDetailStr text;BEGIN    --Get all the node names    queryObjectStr := 'SELECT c.oid, relname, n.nspname FROM pg_class c INNER JOIN pg_namespace n on c.relnamespace = n.oid WHERE c.relkind = ''S''';    FOR rowObject IN EXECUTE(queryObjectStr) LOOP        queryDetailStr := 'SELECT start_value, increment_by, max_value, min_value, cache_value, is_cycled FROM ' || quote_ident(rowObject.nspname) || '.' || quote_ident(rowObject.relname);        FOR rowDetail IN EXECUTE(queryDetailStr) LOOP            namespace = rowObject.nspname;            sequencename = rowObject.relname;            start_value = rowDetail.start_value;            increment_by = rowDetail.increment_by;            max_value = rowDetail.max_value;            min_value = rowDetail.min_value;            cache_value = rowDetail.cache_value;            is_cycled = rowDetail.is_cycled;            RETURN next;        END LOOP;    END LOOP;    RETURN;END; $function$;
CREATE VIEW public.dfm_get_seqence_info AS SELECT * FROM public.dfm_get_seqence_info();

postgres=# CREATE SEQUENCE public.test_seq;CREATE SEQUENCETime: 13.917 mspostgres=# CREATE TABLE public.test_serial(a bigserial, b int) DISTRIBUTE BY ROUNDROBIN;NOTICE:  CREATE TABLE will create implicit sequence "test_serial_a_seq" for serial column "test_serial.a"SQLSTATE: 00000LOCATION:  CreateSeqOwnedByTable, parse_utilcmd.cpp:144CREATE TABLETime: 16.572 mspostgres=# CREATE SEQUENCE public.test_seq_1;CREATE SEQUENCETime: 13.412 mspostgres=# CREATE TABLE public.test_serial_default(a bigint default nextval('public.test_seq_1'::regclass), b int) DISTRIBUTE BY ROUNDROBIN;CREATE TABLETime: 12.006 mspostgres=# SELECT * FROM public.dfm_get_seqence_info; namespace |   sequencename    | min_value |      max_value      | start_value | increment_by | cache_value | is_cycled-----------+-------------------+-----------+---------------------+-------------+--------------+-------------+----------- public    | test_seq          |         1 | 9223372036854775807 |           1 |            1 |           1 | f public    | test_serial_a_seq |         1 | 9223372036854775807 |           1 |            1 |           1 | f public    | test_seq_1        |         1 | 9223372036854775807 |           1 |            1 |           1 | f(3 rows)

postgres=# SELECTpostgres-#     pg_get_userbyid(c1.relowner) AS seqowner,  -- sequence的用户postgres-#     n1.nspname AS seqschema,  -- sequence的schemapostgres-#     c1.relname AS seqname, -- -- sequence的名称postgres-#     (pg_sequence_parameters(c1.oid)).minimum_value AS minvalue, -- sequence的最小值postgres-#     (pg_sequence_parameters(c1.oid)).maximum_value AS maxvalue, -- sequence的最大值postgres-#     (pg_sequence_parameters(c1.oid)).increment AS incrementby, -- sequence的步长postgres-#     pg_get_userbyid(c.relowner) AS tableowner, -- 关联的表的ownerpostgres-#     n.nspname AS tableschema,  -- 关联的表的schemapostgres-#     c.relname AS tablename,-- 关联的表的名称postgres-#     a.attname, -- -- 关联的表的列的名称postgres-#     d.adsrc as defaultexpression -- 关联的sequence的表达式postgres-# FROM pg_catalog.pg_class cpostgres-# INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespacepostgres-# INNER JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oidpostgres-# INNER JOIN pg_catalog.pg_attrdef d ON d.adrelid = c.oid AND d.adnum = a.attnumpostgres-# INNER JOIN pg_catalog.pg_depend p ON p.objid = d.oidpostgres-# INNER JOIN pg_catalog.pg_class c1 ON c1.oid = p.refobjidpostgres-# INNER JOIN pg_catalog.pg_namespace n1 ON n1.oid = c1.relnamespacepostgres-# WHERE c.relkind = 'r'::"char" AND c.oid > 16384postgres-# AND n.nspname not in ('pg_toast', 'cstore', 'pg_catalog')postgres-# AND a.attnum > 0postgres-# AND p.classid='pg_catalog.pg_attrdef'::regclasspostgres-# AND refclassid = 'pg_catalog.pg_class'::regclasspostgres-# AND p.refobjsubid = 0postgres-# AND c1.relkind = 'S'::"char"postgres-# ; seqowner  | seqschema |      seqname      | minvalue |      maxvalue       | incrementby | tableowner | tableschema |      tablename      | attname |           defaultexpression-----------+-----------+-------------------+----------+---------------------+-------------+------------+-------------+---------------------+---------+---------------------------------------- j00565968 | public    | test_serial_a_seq |        1 | 9223372036854775807 |           1 | j00565968  | public      | test_serial         | a       | nextval('test_serial_a_seq'::regclass) j00565968 | public    | test_seq_1        |        1 | 9223372036854775807 |           1 | j00565968  | public      | test_serial_default | a       | nextval('test_seq_1'::regclass)(2 rows)

postgres=# DROP TABLE public.test_serial;DROP TABLETime: 19.871 mspostgres=# CREATE TABLE public.test_serial(a text DEFAULT sys_guid(), b int) DISTRIBUTE BY HASH(a);CREATE TABLETime: 19.245 ms

postgres=# ALTER TABLE public.test_serial  DISTRIBUTE BY ROUNDROBIN;ALTER TABLETime: 218.796 mspostgres=# ALTER TABLE public.test_serial DROP COLUMN a;ALTER TABLETime: 9.509 ms

postgres=# DROP TABLE public.test_serial;DROP TABLETime: 18.786 mspostgres=# CREATE TABLE public.test_serial(a text DEFAULT sys_guid(), b int, primary key(a)) DISTRIBUTE BY HASH(a);NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_serial_pkey" for table "test_serial"CREATE TABLE