主页 / dba / 问题 / 29043
Accepted
Craig Efrein
Asked: 2012-11-22 02:27:30 +0800 CST

PostgreSQL 重复键违反唯一约束错误

  • 772

CentOS 6.2 64bit Server PostgreSQL 8.4.9 通过 yum 安装

我不时在 PostgreSQL 的日志中看到有关名为 events 的表的错误。事件表用于存储每次在被监控的服务器上发生特定事件时由 Zabbix 中的触发器生成的信息。

Zabbix 是唯一使用数据库服务器的应用程序。

这是错误：

INSTRUCTION :  insert into events (eventid,source,object,objectid,clock,ns,value,value_changed) values (6393428,2,3,38,1353354000,0,1,0)
ERROR:  duplicate key violates unique constraint « events_pkey »

没有与表事件关联的序列

这是事件的表结构

                               Table « public.events »
    Colonne    |  Type   |          Modificateurs           | Stockage | Description 
---------------+---------+----------------------------------+----------+-------------
 eventid       | bigint  | non NULL                         | plain    | 
 source        | integer | non NULL Par défaut, 0           | plain    | 
 object        | integer | non NULL Par défaut, 0           | plain    | 
 objectid      | bigint  | non NULL Par défaut, (0)::bigint | plain    | 
 clock         | integer | non NULL Par défaut, 0           | plain    | 
 value         | integer | non NULL Par défaut, 0           | plain    | 
 acknowledged  | integer | non NULL Par défaut, 0           | plain    | 
 ns            | integer | non NULL Par défaut, 0           | plain    | 
 value_changed | integer | non NULL Par défaut, 0           | plain    | 
Index :
    "events_pkey" PRIMARY KEY, btree (eventid)
    "events_1" btree (object, objectid, eventid)
    "events_2" btree (clock)
Référencé par :
    TABLE "acknowledges" CONSTRAINT "c_acknowledges_2" FOREIGN KEY (eventid) REFERENCES events(eventid) ON DELETE CASCADE
    TABLE "alerts" CONSTRAINT "c_alerts_2" FOREIGN KEY (eventid) REFERENCES events(eventid) ON DELETE CASCADE

问题

我可以采取哪些步骤来解决此错误？

谢谢

11/26 更新

我尝试运行 dezso 发送给我的 ALTER TABLE ALTER COLUMN 语句，但我收到有关 bigserial 类型的错误。

zabbix=# ALTER TABLE events ALTER COLUMN eventid TYPE bigserial;
ERREUR:  le type « bigserial » n'existe pas

但是，我可以使用该数据类型创建一个新表

zabbix=# \connect postgres
psql (8.4.13)
Vous êtes maintenant connecté à la base de données « postgres ».
postgres=# CREATE TABLE test(x bigserial);
NOTICE:  CREATE TABLE créera des séquences implicites « test_x_seq » pour la colonne serial « test.x »
CREATE TABLE
postgres=# \ds
              Liste des relations
 Schéma |    Nom     |   Type   | Propriétaire 
--------+------------+----------+--------------
 public | test_x_seq | séquence | postgres
(1 ligne)

我应该如何将 eventid 更改为 bigserial？

更新 11/27

我尝试了另一种方法将序列添加到列 eventid

create sequence eventid_seq;
alter table events alter column eventid set default nextval('eventid_seq');
select (max(eventid) +1) as nexteventid from events;
SELECT setval('eventid_seq', 6645754);

那行得通，但是当我重新打开 Zabbix 时，eventid 值继续增加，但序列没有。我做错了什么？

postgresql unique-constraint

1 个回答

  1. Best Answer

    （我试图从评论中编译一个答案。）

    您的应用程序生成主键值的过程中似乎存在问题。通常将其留给 DBMS 更为明智：定义一个序列并让它填充 PK 值。如果你有一个序列，你至少有选项：要么设置像

    $eventid = exec(SELECT nextval('your_sequence')) // pseudocode!

    并在INSERT语句中使用它，或者通过省略 PK 将其完全留给 PostgreSQL INSERT：

    INSERT INTO events (source, object, [...]) 
VALUES (2, 3, [...]);

    （我个人通常选择第二种解决方案。）

    您可以通过将 PK 列的数据类型设置为bigserial（如果是integer，则只是serial）来更改此行为：

    ALTER TABLE events ALTER COLUMN eventid TYPE bigserial;

    这将引发一个通知，告诉您生成了一个名为（可能）的序列events_eventid_seq。您可以如上所述使用它。

    编辑（2012-11-27）

    您可以通过如下查询设置序列

    SELECT setval('events_eventid_seq', (SELECT max(eventid) FROM events));

    为了让序列产生下一个值eventid，你不能在你的INSERT. 如果这样做，它将采用手动提供的值并且不会推进序列。这将在以后导致相同的唯一违规错误。

    关于ALTER TABLE：如您所见，ALTER TABLE- 类型不存在和CREATE TABLE. 你确定你在同一个数据库上尝试这个吗？我不记得旧版本了，但 8.3 已经有了bigserial速记（它不是真正的类型）。（进一步考虑，您很可能无法在 中使用它ALTER TABLE，因为它是一种速记...）您可以按照手册解决问题：

    CREATE SEQUENCE events_eventid_seq;

ALTER TABLE events 
ALTER COLUMN eventid TYPE bigint;

ALTER SEQUENCE events_eventid_seq OWNED BY events.eventid;

SELECT setval('events_eventid_seq', (SELECT max(eventid) FROM events));
    • 3

相关问题