本文主要是介绍PostgreSQL中int类型达到上限的一些处理方案，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
使用int类型作为表的主键在pg中是很常见的情况，但是pg中int类型的范围在-2147483648到2147483647，最大只有21亿，这个在一些大表中很容易就会达到上限。一旦达到上限，那么表中便没办法在插入数据了，这个将会是很严重的问题。
如何监控？
对于此类情况，我们可以考虑将序列使用情况加入到监控中，防止达到最大值后表中无法插入数据的情况发生。
可以使用下面SQL去查询库中序列的使用情况：
SELECTseqs.relname AS sequence,format_type(s.seqtypid, NULL) sequence_datatype,CONCAT(tbls.relname, '.', attrs.attname) AS owned_by,format_type(attrs.atttypid, atttypmod) AS column_datatype,pg_sequence_last_value(seqs.oid::regclass) AS last_sequence_value,TO_CHAR((CASE WHEN format_type(s.seqtypid, NULL) = 'smallint' THEN(pg_sequence_last_value(seqs.relname::regclass) / 32767::float)WHEN format_type(s.seqtypid, NULL) = 'integer' THEN(pg_sequence_last_value(seqs.relname::regclass) / 2147483647::float)WHEN format_type(s.seqtypid, NULL) = 'bigint' THEN(pg_sequence_last_value(seqs.relname::regclass) / 9223372036854775807::float)END) * 100, 'fm9999999999999999999990D00%') AS sequence_percent,TO_CHAR((CASE WHEN format_type(attrs.atttypid, NULL) = 'smallint' THEN(pg_sequence_last_value(seqs.relname::regclass) / 32767::float)WHEN format_type(attrs.atttypid, NULL) = 'integer' THEN(pg_sequence_last_value(seqs.relname::regclass) / 2147483647::float)WHEN format_type(attrs.atttypid, NULL) = 'bigint' THEN(pg_sequence_last_value(seqs.relname::regclass) / 9223372036854775807::float)END) * 100, 'fm9999999999999999999990D00%') AS column_percent FROMpg_depend dJOIN pg_class AS seqs ON seqs.relkind = 'S'AND seqs.oid = d.objidJOIN pg_class AS tbls ON tbls.relkind = 'r'AND tbls.oid = d.refobjidJOIN pg_attribute AS attrs ON attrs.attrelid = d.refobjidAND attrs.attnum = d.refobjsubidJOIN pg_sequence s ON s.seqrelid = seqs.oid WHEREd.deptype = 'a'AND d.classid = 1259;
查询出的结果类似这样：
解决方案
1、修改序列为负数
因为pg中int类型是包含负数的，所以如果序列从0开始递增即将达到最大值，那么可以考虑切换到负数排序。将序列的起始值设置为-1然后降序来递增。
alter sequence test_id_seq no minvalue start with -1 increment -1 restart;
这种方式不需要修改表的结构，可以很快的解决问题。但是这种方案的前提是主键列只是单纯用来做递增的唯一约束用的，可以接受使用负数才行。
而且这也只能将可用数据范围翻倍，只能短期解决问题，如果后续负数用完了那就没办法了，只能去修改字段类型了。
2、修改序列cycle属性（分区表）
如果你的表是分区表的话，还可以考虑直接修改序列的属性为cycle。因为在pg中，主键并不是全局性的约束，而只是针对单个分区的。
即分区1和分区2中都可以出现主键id相同的数据。当然，这种方案仅限于分区表的场景。
alter sequence test_id_seq cycle;
3、修改字段类型为bigint
如果上面俩种方案都没法解决的话，那最终只能选择修改字段类型为bigint的方式了。不过肯定也不能直接去这样修改：
alter table xxx alter id type bigint;
毕竟一般int类型达到上限的表都很大了，修改int为bigint是会重写表的，需要长时间持有独占锁，这个对业务来说是难以接受的。
比较推荐的方案就是新增一个bigint列，然后用其去替换原先的int列。
alter table test add column id_new bigint; CREATE UNIQUE INDEX CONCURRENTLY test_id_new ON test (id_new);
紧接着再创建对应的bigint的序列。
CREATE SEQUENCE test_id_new_seq START 2147483776 AS bigint;
ALTER TABLE test ALTER COLUMN id_new SET DEFAULT nextval ('test_id_new_seq');
alter sequence test_id_new_seq owned by test.id_new;
现在旧的int类型和新的bigint类型的列就都在自增了。
bill=# select * from test;id | value | id_new ------------+-------+------------2000000009 | |2000000010 | |2000000011 | | 21474837762000000012 | | 2147483777
然后我们就可以将id列和id_new列进行重命名了，这一步需要放在同一个事务中去处理。
BEGIN; ALTER TABLE test DROP CONSTRAINT test_pkey; ALTER TABLE test ALTER COLUMN id DROP DEFAULT; ALTER TABLE test RENAME COLUMN id TO id_old; ALTER TABLE test RENAME COLUMN id_new TO id; ALTER TABLE test ALTER COLUMN id_old DROP NOT NULL; ALTER TABLE test ADD CONSTRAINT id_not_null CHECK (id IS NOT NULL) NOT VALID; COMMIT;
由于之前添加id_new列中有大量null值，因此需要将约束设置为NOT VALID，但是我们需要将该列变为主键的话，之前的null值还是需要去处理的，这里使用批量update的方式去进行更新。
WITH unset_values AS (SELECTid_oldFROMtestWHEREid IS NULLLIMIT 1000) UPDATEtest SETid = unset_values.id_old FROMunset_values WHEREunset_values.id_old = test.id_old;
null的数据处理完之后，我们便可以将新的id列更改为主键了。
ALTER TABLE test VALIDATE CONSTRAINT id_not_null; ALTER TABLE test ADD CONSTRAINT test_pkey PRIMARY KEY USING INDEX test_id_new; ALTER TABLE test DROP CONSTRAINT id_not_null;
最后我们便可以将旧的id列删除了，记得删除完之后重新收集下表的统计信息。
bill=# ALTER table test drop column id_old;
ALTER TABLE
bill=# analyze t1;
ANALYZE
bill=# \d testTable "public.test"Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+--------------------------------------value | integer | | |id | bigint | | not null | nextval('test_id_new_seq'::regclass)
Indexes:"test_pkey" PRIMARY KEY, btree (id)
总的来说这种方式也是比较麻烦的，其中在线创建索引和批量update都是比较耗时的操作。
因此对于应用来说还是应该尽可能的避免出现这种情况，大表在设计的阶段就应该考虑使用bigint或者bigserial来代替int类型，不要在int类型快要达到最大值再开始去救火。
这篇关于PostgreSQL中int类型达到上限的一些处理方案的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
