我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
当前回答
试着重建索引。
更新:正如评论中指出的,这是对最初问题的回答。
其他回答
更新模式中用作ID的所有序列的方法:
DO $$ DECLARE
r RECORD;
BEGIN
FOR r IN (SELECT tablename, pg_get_serial_sequence(tablename, 'id') as sequencename
FROM pg_catalog.pg_tables
WHERE schemaname='YOUR_SCHEMA'
AND tablename IN (SELECT table_name
FROM information_schema.columns
WHERE table_name=tablename and column_name='id')
order by tablename)
LOOP
EXECUTE
'SELECT setval(''' || r.sequencename || ''', COALESCE(MAX(id), 1), MAX(id) IS NOT null)
FROM ' || r.tablename || ';';
END LOOP;
END $$;
之前我还没有尝试过代码:在下面我张贴 Klaus和user457226解决方案的sql代码版本 它在我的电脑上运行(Postgres 8.3),只做了一些小调整 克劳斯的版本和我的user457226版本。
克劳斯解决方案:
drop function IF EXISTS rebuilt_sequences() RESTRICT;
CREATE OR REPLACE FUNCTION rebuilt_sequences() RETURNS integer as
$body$
DECLARE sequencedefs RECORD; c integer ;
BEGIN
FOR sequencedefs IN Select
constraint_column_usage.table_name as tablename,
constraint_column_usage.table_name as tablename,
constraint_column_usage.column_name as columnname,
replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','') as sequencename
from information_schema.constraint_column_usage, information_schema.columns
where constraint_column_usage.table_schema ='public' AND
columns.table_schema = 'public' AND columns.table_name=constraint_column_usage.table_name
AND constraint_column_usage.column_name = columns.column_name
AND columns.column_default is not null
LOOP
EXECUTE 'select max('||sequencedefs.columnname||') from ' || sequencedefs.tablename INTO c;
IF c is null THEN c = 0; END IF;
IF c is not null THEN c = c+ 1; END IF;
EXECUTE 'alter sequence ' || sequencedefs.sequencename ||' restart with ' || c;
END LOOP;
RETURN 1; END;
$body$ LANGUAGE plpgsql;
select rebuilt_sequences();
User457226解决方案:
--drop function IF EXISTS reset_sequence (text,text) RESTRICT;
CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text,columnname text) RETURNS bigint --"pg_catalog"."void"
AS
$body$
DECLARE seqname character varying;
c integer;
BEGIN
select tablename || '_' || columnname || '_seq' into seqname;
EXECUTE 'SELECT max("' || columnname || '") FROM "' || tablename || '"' into c;
if c is null then c = 0; end if;
c = c+1; --because of substitution of setval with "alter sequence"
--EXECUTE 'SELECT setval( "' || seqname || '", ' || cast(c as character varying) || ', false)'; DOES NOT WORK!!!
EXECUTE 'alter sequence ' || seqname ||' restart with ' || cast(c as character varying);
RETURN nextval(seqname)-1;
END;
$body$ LANGUAGE 'plpgsql';
select sequence_name, PG_CLASS.relname, PG_ATTRIBUTE.attname,
reset_sequence(PG_CLASS.relname,PG_ATTRIBUTE.attname)
from PG_CLASS
join PG_ATTRIBUTE on PG_ATTRIBUTE.attrelid = PG_CLASS.oid
join information_schema.sequences
on information_schema.sequences.sequence_name = PG_CLASS.relname || '_' || PG_ATTRIBUTE.attname || '_seq'
where sequence_schema='public';
这个问题发生在我使用实体框架创建数据库,然后用初始数据播种数据库时,这使得序列不匹配。
我通过创建一个脚本来解决这个问题,在播种数据库后运行:
DO
$do$
DECLARE tablename text;
BEGIN
-- change the where statments to include or exclude whatever tables you need
FOR tablename IN SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' AND table_name != '__EFMigrationsHistory'
LOOP
EXECUTE format('SELECT setval(pg_get_serial_sequence(''"%s"'', ''Id''), (SELECT MAX("Id") + 1 from "%s"))', tablename, tablename);
END LOOP;
END
$do$
最短最快的路
SELECT setval('tbl_tbl_id_seq', max(tbl_id)) FROM tbl;
tbl_id是表tbl的串行列或IDENTITY列,取自序列tbl_tbl_id_seq(生成的默认名称)。看到的:
自动递增表列
如果你不知道附加序列的名称(不一定是默认形式),使用pg_get_serial_sequence()(也适用于IDENTITY):
SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id'), max(tbl_id)) FROM tbl;
这里没有差1的误差。手册:
双参数形式将序列的last_value字段设置为 值,并将其is_called字段设置为true Next nextval将在返回值之前推进序列。
大胆强调我的。
如果表可以为空,在这种情况下实际从1开始:
SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id')
, COALESCE(max(tbl_id) + 1, 1)
, false)
FROM tbl;
我们不能只使用2参数形式并从0开始,因为序列的下界默认为1(除非自定义)。
在并发写负载下安全
为了防止并发序列活动或写入,可以在SHARE模式下锁定表。它可以防止并发事务写入更高的数字(或任何东西)。
还要考虑到客户端可能在主表上没有任何锁的情况下提前获取了序列号,然而(可能发生在某些设置中),只增加序列的当前值,而不要减少它。这可能看起来有些偏执,但这符合序列的本质,并可以防止并发性问题。
BEGIN;
LOCK TABLE tbl IN SHARE MODE;
SELECT setval('tbl_tbl_id_seq', max(tbl_id))
FROM tbl
HAVING max(tbl_id) > (SELECT last_value FROM tbl_tbl_id_seq); -- prevent lower number
COMMIT;
SHARE模式足够强大。手册:
这种模式保护表不受并发数据更改的影响。
与ROW EXCLUSIVE模式冲突。
命令UPDATE、DELETE和INSERT在目标表上获得这种锁定模式。
我的版本使用第一个,有一些错误检查…
BEGIN;
CREATE OR REPLACE FUNCTION reset_sequence(_table_schema text, _tablename text, _columnname text, _sequence_name text)
RETURNS pg_catalog.void AS
$BODY$
DECLARE
BEGIN
PERFORM 1
FROM information_schema.sequences
WHERE
sequence_schema = _table_schema AND
sequence_name = _sequence_name;
IF FOUND THEN
EXECUTE 'SELECT setval( ''' || _table_schema || '.' || _sequence_name || ''', ' || '(SELECT MAX(' || _columnname || ') FROM ' || _table_schema || '.' || _tablename || ')' || '+1)';
ELSE
RAISE WARNING 'SEQUENCE NOT UPDATED ON %.%', _tablename, _columnname;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql';
SELECT reset_sequence(table_schema, table_name, column_name, table_name || '_' || column_name || '_seq')
FROM information_schema.columns
WHERE column_default LIKE 'nextval%';
DROP FUNCTION reset_sequence(_table_schema text, _tablename text, _columnname text, _sequence_name text) ;
COMMIT;