pg数据库序列值问题

Question

现在我有个user_test表，里面有个test_id字段，有个唯一字段user_id，现在想将test_id为0的记录置为一个序列值，假如我这个表记录有好几百万条，但我每次只想置1w条，有什么好的办法吗？执行的sql最好效率要高能走索引，因为数据量比较大

UPDATE user_test set test_id = nextval('user_test_id_seq') where test_id =0;

现在想实现的功能 ：UPDATE user_test set test_id = nextval('user_test_id_seq') where test_id =0 limit 10000; 此段sql会报错

Answer 1

UPDATE user_test SET test_id= nextval('user_test_id_seq')
WHERE user_id IN (
    SELECT user_id FROM (
        SELECT user_id FROM user_test
        where test_id =0 
        LIMIT 10000
    ) subquery
);

Answer 2

postgresql不支持，update.....limit 这样的语法，所以可以这样。

WITH cte AS (
   SELECT server_ip          -- pk column or any (set of) unique column(s)
   FROM   server_info
   WHERE  status = 'standby'
   LIMIT  1                  -- arbitrary pick (cheapest)
   )
UPDATE server_info s
SET    status = 'active' 
FROM   cte
WHERE  s.server_ip = cte.server_ip
RETURNING server_ip;

Answer 3

提供一个思路。
1.创建一个临时表,把要更新的user_id和序列号先放入这个临时表中

create table tmp as
select user_id,row_number() over(order by user_id) as test_id
from user_test
where test_id =0;

2.写一个脚本去更新user_test,不断更新${offset}值，一致到更新完所有数据

with t as (
  select user_id,test_id
  from tmp
  order by user_id
  limit 10000 offset ${offset}
)
update user_test
set user_test.test_id=t.test_id
from user_test,t
where user_test.user_id=t.user_id

Answer 4

1、首先，这个处理你是需要执行多少次？是手工执行还是程序执行？如果只执行几次或手工执行，慢一点是没有关系的，在 with t as 之后加上： MATERIALIZED 关键字即可节省执行时间，即：

with t as MATERIALIZED ( select …… limit 10000 ) update ……  from …… where …… ;-- 具体见楼上各位的回答

2、如果你需要长期在程序中执行，你就需要分析一下有索引的user_id字段的数据分布了，分别对其前n个字符进行统计：

SELECT LEFT( user_id, n ) usr, count( 1 ) cnt FROM user_test WHERE test_id = 0 GROUP BY LEFT( user_id, n ); -- n需要从小到大的试

当得到的cnt数量足够小（正好10000基本是不可能的，大于10000或分布比较平均即可），将usr字段的结果保存到程序中（估计user表的数据变化不会太大，一次统计应该能够满足一定时期的运行使用，运行一段时间后，再重复上面的分析即可），然后通过

 WHERE user_id like '前n个字符%' and test_id = 0 limit 10000

来提取数据，这样可能会走索引，最终能否走索引还要看数据库的执行计划的对cost的最终判定，只有试了才知道……