Postgresql修正序列插件之pg_sequence_fixer

#创建测试表
<16:28:43><db:hank><user:postgres><pid:30677>=# CREATE TABLE t_test_seq (
(#     id          serial     PRIMARY KEY, 
(#     info     text
(# );
CREATE TABLE
#使用序列自动填充，插入两条数据
<16:32:05><db:hank><user:postgres><pid:30677>=# INSERT INTO t_test_seq (info) VALUES ('foo') RETURNING *;
 id │ info 
────┼──────
  1 │ foo
(1 row)

INSERT 0 1
<16:32:11><db:hank><user:postgres><pid:30677>=# INSERT INTO t_test_seq (info) VALUES ('bar') RETURNING *;
 id │ info 
────┼──────
  2 │ bar
(1 row)

INSERT 0 1
#手动插入第三条成功
<16:32:17><db:hank><user:postgres><pid:30677>=# INSERT INTO t_test_seq VALUES (3, 'bang') RETURNING *;
 id │ info 
────┼──────
  3 │ bang
(1 row)

INSERT 0 1

#使用序列自动填充插入报错违反唯一约束，因为这里序列的值是3
<16:32:32><db:hank><user:postgres><pid:30677>=# INSERT INTO t_test_seq (info) VALUES ('boom') RETURNING *;
ERROR:  duplicate key value violates unique constraint "t_test_seq_pkey"
DETAIL:  Key (id)=(3) already exists.

#安装，这里我下载的zip包，解压安装即可
unzip pg_sequence_fixer-main.zip
cd pg_sequence_fixer-main
make install
/usr/bin/mkdir -p '/opt/pgsql/share/extension'
/usr/bin/mkdir -p '/opt/pgsql/share/extension'
/usr/bin/mkdir -p '/opt/pgsql/share/doc/extension'
/usr/bin/install -c -m 644 .//pg_sequence_fixer.control '/opt/pgsql/share/extension/'
/usr/bin/install -c -m 644 .//pg_sequence_fixer--*.sql  '/opt/pgsql/share/extension/'
/usr/bin/install -c -m 644 .//README.md '/opt/pgsql/share/doc/extension/'

<16:28:39><db:hank><user:postgres><pid:30677>=# create extension pg_sequence_fixer ;
CREATE EXTENSION
<16:28:41><db:hank><user:postgres><pid:30677>=# \dx
 pg_sequence_fixer  │ 1.0     │ public     │ adjusting sequences which got out of sync
#查看相关函数，可见有两个参数，第一个参数表示，我们想要在序列确定的最大值上增加的安全边界。第二个参数控制现是现在锁这些表还是在操作期间锁这些表。通常，锁表不是我们想要的，但为了保证操作一致性，有时候是必要的，以防无法关闭正在修复序列的应用程序。

<16:32:37><db:hank><user:postgres><pid:30677>=# \df pg_sequence_fixer

                                             List of functions
 Schema │       Name        │ Result data type │                 Argument data types                 │ Type 
────────┼───────────────────┼──────────────────┼─────────────────────────────────────────────────────┼──────
 public │ pg_sequence_fixer │ void             │ v_margin integer, v_lock_mode boolean DEFAULT false │ func
(1 row)

#原来表中最大值为3，以下例子是在3的基础上增加安全边界10，也就设置当前序列值为13
<16:35:32><db:hank><user:postgres><pid:30677>=# SELECT pg_sequence_fixer(10, false);
NOTICE:  setting sequence for t_test_seq to 13

 pg_sequence_fixer 
───────────────────
 
(1 row)

#使用序列自动填充，可见插入值为下一个值14
<16:36:50><db:hank><user:postgres><pid:30677>=# INSERT INTO t_test_seq (info) VALUES ('ttt') RETURNING *;
 id │ info 
────┼──────
 14 │ ttt
(1 row)

INSERT 0 1

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION pg_sequence_fixer" to load this file. \quit

CREATE FUNCTION pg_sequence_fixer(IN v_margin int, IN v_lock_mode boolean DEFAULT false)
RETURNS void AS
$$
  DECLARE
    v_rec    RECORD;  
    v_sql    text;
    v_max    int8;
  BEGIN
    IF  v_margin IS NULL    --判断v_margin的值，如果是空，发出notice并设置为1，如果是负值，则发出warning
    THEN 
      RAISE NOTICE 'the safety margin will be set to 1';
      v_margin := 1;
    END IF;

    IF   v_margin < 1
    THEN
      RAISE WARNING 'a negative safety margin is used';
    END IF;
    --v_rec直接取出序列，表，列相关数据
    FOR v_rec IN 
      SELECT  d.objid::regclass,
        d.refobjid::regclass,
        a.attname
      FROM   pg_depend AS d
        JOIN pg_class AS t
          ON d.objid = t.oid
        JOIN pg_attribute AS a
          ON d.refobjid = a.attrelid 
            AND d.refobjsubid = a.attnum
      WHERE   d.classid = 'pg_class'::regclass
        AND d.refclassid = 'pg_class'::regclass
        AND t.oid >= 16384
        AND t.relkind = 'S'
        AND d.deptype IN ('a', 'i')
    LOOP
      IF  v_lock_mode = true      --如果参数设置为true,则使用lock table显试加排它锁
      THEN
        v_sql := 'LOCK TABLE ' || v_rec.refobjid::regclass || ' IN EXCLUSIVE MODE';
        RAISE NOTICE 'locking: %', v_rec.refobjid::regclass;
        EXECUTE v_sql;
      END IF;
                                     --设置序列的值，取出列的最大值并加上v_margin
      v_sql := 'SELECT setval(' || quote_literal(v_rec.objid::regclass) || '::text, max(' 
        || quote_ident(v_rec.attname::text) || ') + ' || v_margin 
        || ') FROM ' || v_rec.refobjid::regclass;
      EXECUTE v_sql INTO v_max;
      RAISE NOTICE 'setting sequence for % to %', v_rec.refobjid::text, v_max; 
    END LOOP;

    RETURN;
  END;
$$ LANGUAGE 'plpgsql';