sql - 插入到没有序列列的空表时出现重复键约束错误

Question

当我插入的表为空时，为什么会出现此错误？该表的前一次迭代(自删除后)有一个序列号，所以我想知道 Postgres 是否正在缓存旧表架构？

这是错误:

-- Executing query:
DELETE FROM student
WHERE sno =101;

ERROR:  duplicate key value violates unique constraint "cancel_pkey"
DETAIL:  Key (eno, cdate)=(1, 2014-12-08 21:21:53.710883) already exists.
CONTEXT:  SQL statement "INSERT INTO cancel(eno,excode,sno)
            SELECT eno, excode, sno 
            FROM entry
            WHERE eno = OLD.eno"
PL/pgSQL function cancel_entry() line 3 at SQL statement
SQL statement "DELETE FROM entry
            WHERE sno = OLD.sno"
PL/pgSQL function delete_student() line 8 at SQL statement

实际的插入查询发生在如下所示的触发器中:

CREATE OR REPLACE FUNCTION delete_student()
RETURNS trigger AS $BODY$
    BEGIN
    IF (SELECT EXISTS (SELECT * FROM entry WHERE sno = OLD.sno)) THEN
    INSERT INTO cancel(eno,excode,sno)
        SELECT eno, excode, sno 
        FROM entry
        WHERE sno = OLD.sno;
    DELETE FROM entry
        WHERE sno = OLD.sno;
    END IF;
    RETURN OLD;
    END; $BODY$ 
    LANGUAGE plpgsql;

CREATE TRIGGER DeleteStudent
    BEFORE DELETE ON student
    FOR EACH ROW
    execute procedure delete_student();

cancel 表架构如下所示:

CREATE TABLE cancel (
eno     INTEGER     NOT NULL,
excode  CHAR(4)     NOT NULL, --not unique as many instances of cancellations for exam
sno     INTEGER     NOT NULL, --not unique as student may cancel several exams
cdate   TIMESTAMP   NOT NULL    DEFAULT NOW(),
cuser   VARCHAR(128) NOT NULL   DEFAULT CURRENT_USER,
PRIMARY KEY(eno,cdate)
-- sno is not a foreign key, as it must still exist even where student is deleted. 
);

entry 表模式是:

CREATE TABLE entry (
eno     INTEGER     NOT NULL    DEFAULT NEXTVAL('eno_sequence'),
excode  CHAR(4)     NOT NULL,
sno     INTEGER     NOT NULL,
egrade  DECIMAL(5,2)    CHECK(egrade BETWEEN 0 AND 100),
PRIMARY KEY(eno),
FOREIGN KEY(excode) REFERENCES exam MATCH FULL ON DELETE RESTRICT,
FOREIGN KEY(sno) REFERENCES student MATCH FULL ON DELETE RESTRICT --handle with stored procedure to retain student reference in cancel table
);

'学生'表模式

CREATE TABLE student (
   sno          INTEGER         NOT NULL,
   sname            VARCHAR(20) NOT NULL,
   semail      VARCHAR(20)  NOT NULL,
    PRIMARY KEY(sno)
);

Answer 1

因为您要从附加 表中移动行 entry ，并且您先运行 SELECT/INSERT，则存在潜在的竞争条件:多个并发事务可以同时尝试相同的操作。

但是，触发器由 DELETE 调用在 table 上 student ，它需要一个 ROW EXCLUSIVE锁定受影响的行。还有你的DELETE的谓词( WHERE sno = 101 ) 在unique 列上。即使表的实际表定义 student问题中缺少，我可以从表 entry 中的 FK 定义中看出这需要对引用的列进行唯一或 PK 约束。这可以防止并发事务删除 student 中的行这将在竞争行上调用触发器。

起初也让我感到困惑的是:您显示触发器/函数 DeleteStudent/delete_student() , 但异常是从 cancel_entry() 引发的，这在您的问题中不。走出去，看起来你叫同样的INSERT 另一个触发器中的命令，这将解释异常。

无论哪种方式，我们看到的功能都比必要的更昂贵并且更容易受到竞争条件的影响。使用 data-modifying CTE先删除(锁定行)，然后才插入另一个表(这可能会解决您的问题，信息丢失)。同时简化:

CREATE OR REPLACE FUNCTION delete_student()
  RETURNS trigger AS
$func$
BEGIN
   <strike>IF (SELECT EXISTS (SELECT * FROM entry WHERE sno = OLD.sno)) THEN</strike> -- no need
   WITH del AS (
      DELETE FROM entry  -- delete first, locking rows
      WHERE  sno = OLD.sno
      RETURNING eno, excode, sno
      )
   INSERT INTO cancel(eno, excode, sno) -- only then insert
   SELECT eno, excode, sno 
   FROM   del;
   <strike>END IF;</strike>
   RETURN OLD;
END
$func$  LANGUAGE plpgsql;

您不需要 IF完全构建。如果SELECT (或更新版本中的 DELETE)在 entry 中找不到任何行, INSERT 什么都不做。对于 CTE，最终的 INSERT CTE del 时甚至从未执行过不返回任何行。这是尽可能快和干净的。

相关回答:

• Deleting a rows from a table dpending upon the rows deleted from another table
• Get row to swap tables on a certain condition