我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
当前回答
这个答案是从毛罗那里抄来的。
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
DISTINCT(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
ORDER BY sequencename
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 ||' minvalue '||c ||' start ' || c ||' restart with ' || c;
END LOOP;
RETURN 1; END;
$body$ LANGUAGE plpgsql;
select rebuilt_sequences();
其他回答
之前我还没有尝试过代码:在下面我张贴 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';
当序列名、列名、表名或模式名中有空格、标点符号等有趣的字符时,这些函数充满了危险。我这样写:
CREATE OR REPLACE FUNCTION sequence_max_value(oid) RETURNS bigint
VOLATILE STRICT LANGUAGE plpgsql AS $$
DECLARE
tabrelid oid;
colname name;
r record;
newmax bigint;
BEGIN
FOR tabrelid, colname IN SELECT attrelid, attname
FROM pg_attribute
WHERE (attrelid, attnum) IN (
SELECT adrelid::regclass,adnum
FROM pg_attrdef
WHERE oid IN (SELECT objid
FROM pg_depend
WHERE refobjid = $1
AND classid = 'pg_attrdef'::regclass
)
) LOOP
FOR r IN EXECUTE 'SELECT max(' || quote_ident(colname) || ') FROM ' || tabrelid::regclass LOOP
IF newmax IS NULL OR r.max > newmax THEN
newmax := r.max;
END IF;
END LOOP;
END LOOP;
RETURN newmax;
END; $$ ;
你可以为单个序列调用它,通过传递OID,它将返回任何表所使用的默认序列的最大值;或者你可以用这样的查询来运行它,重置数据库中的所有序列:
select relname, setval(oid, sequence_max_value(oid))
from pg_class
where relkind = 'S';
使用不同的qual,只能重置某个模式中的序列,等等。例如,如果你想调整"public"模式中的序列:
select relname, setval(pg_class.oid, sequence_max_value(pg_class.oid))
from pg_class, pg_namespace
where pg_class.relnamespace = pg_namespace.oid and
nspname = 'public' and
relkind = 'S';
注意,由于setval()的工作方式,您不需要向结果添加1。
作为结束语,我必须警告,一些数据库似乎有默认的序列链接方式,不让系统目录拥有它们的完整信息。当你在psql的\d中看到这样的东西时,就会发生这种情况:
alvherre=# \d baz
Tabla «public.baz»
Columna | Tipo | Modificadores
---------+---------+------------------------------------------------
a | integer | default nextval(('foo_a_seq'::text)::regclass)
请注意,该默认子句中的nextval()调用除了::regclass强制转换外,还有一个::text强制转换。我认为这是由于数据库是从旧的PostgreSQL版本pg_dump'ed。上面的sequence_max_value()函数将忽略这样一个表。为了解决这个问题,你可以重新定义DEFAULT子句,以直接引用序列而不进行强制转换:
alvherre=# alter table baz alter a set default nextval('foo_a_seq');
ALTER TABLE
然后psql会正确显示它:
alvherre=# \d baz
Tabla «public.baz»
Columna | Tipo | Modificadores
---------+---------+----------------------------------------
a | integer | default nextval('foo_a_seq'::regclass)
一旦您修复了这个问题,这个函数就可以正确地工作于这个表以及所有其他可能使用相同序列的表。
重新检查公共模式函数中的所有序列
CREATE OR REPLACE FUNCTION public.recheck_sequence (
)
RETURNS void AS
$body$
DECLARE
_table_name VARCHAR;
_column_name VARCHAR;
_sequence_name VARCHAR;
BEGIN
FOR _table_name IN SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public' LOOP
FOR _column_name IN SELECT column_name FROM information_schema.columns WHERE table_name = _table_name LOOP
SELECT pg_get_serial_sequence(_table_name, _column_name) INTO _sequence_name;
IF _sequence_name IS NOT NULL THEN
EXECUTE 'SELECT setval('''||_sequence_name||''', COALESCE((SELECT MAX('||quote_ident(_column_name)||')+1 FROM '||quote_ident(_table_name)||'), 1), FALSE);';
END IF;
END LOOP;
END LOOP;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
选择setval…使JDBC变得无聊,所以这里有一种与java兼容的方法:
-- work around JDBC 'A result was returned when none was expected.'
-- fix broken nextval due to poorly written 20140320100000_CreateAdminUserRoleTables.sql
DO 'BEGIN PERFORM setval(pg_get_serial_sequence(''admin_user_role_groups'', ''id''), 1 + COALESCE(MAX(id), 0), FALSE) FROM admin_user_role_groups; END;';
重置所有序列,除了每个表的主键是“id”外,对名称没有任何假设:
CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text)
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
EXECUTE 'SELECT setval( pg_get_serial_sequence(''' || tablename || ''', ''' || columnname || '''),
(SELECT COALESCE(MAX(id)+1,1) FROM ' || tablename || '), false)';
END;
$body$ LANGUAGE 'plpgsql';
select table_name || '_' || column_name || '_seq', reset_sequence(table_name, column_name) from information_schema.columns where column_default like 'nextval%';