Duplicate key constraint error when inserting to empty table with no sequenced columns

Question

Why do I get this error when the table I'm inserting into is empty? A previous iteration of the table (since dropped) had a serial, so I wondered if Postgres was caching the old table schema?

Here's the error:

-- 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

The actual insert query takes place within a trigger which looks like this:

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();

The cancel table schema looks like:

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. 
);

And the entry table schema is:

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
);

'student' table schema

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

You might get such an error if two concurrent transactions both attempted to delete the same student. Both could see the `cancel` table initially empty and one or more `entry` rows with that student number, but the second to commit would likely fail with an error such as you describe. — John Bollinger, Dec 08 '14 at 21:55
Thanks for the info John, how could I confirm this is indeed happening and rectify the problem? — nscoppin, Dec 09 '14 at 00:06

score 2 · Accepted Answer · edited May 23 '17 at 12:22

Since you are moving rows from the additional table entry, and you run SELECT / INSERT first, there is a potential race condition: multiple concurrent transactions could try the same at the same time.

However, the trigger is invoked by a DELETE on table student, which takes a ROW EXCLUSIVE lock on affected rows. And the predicate of your DELETE (WHERE sno = 101) is on a unique column. Even though the actual table definition of table student is missing in the question, I can tell from the FK definition in table entry which requires a unique or PK constraint on the referenced column. That keeps concurrent transactions from deleting rows in student that would invoke the trigger on competing rows.

What also had me confused at first: You display the trigger / function DeleteStudent / delete_student(), but the exception is raised from cancel_entry(), which is not in your question. Going out on a limb, it looks like you call the same INSERT command in another trigger, which would explain the exception.

Either way, the function we see is more expensive and susceptible to race conditions than necessary. Use a data-modifying CTE to DELETE first (locking rows) and only then INSERT into the other table (this might fix your problem, information is missing). Also simplify:

CREATE OR REPLACE FUNCTION delete_student()
  RETURNS trigger AS
$func$
BEGIN
   IF (SELECT EXISTS (SELECT * FROM entry WHERE sno = OLD.sno)) THEN -- 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;
   END IF;
   RETURN OLD;
END
$func$  LANGUAGE plpgsql;

You don't need the IF construct at all. If the SELECT (or the DELETE in the updated version) finds no row in entry, the INSERT does nothing. In the case of the CTE, the final INSERT is never even executed when CTE del returns no rows. That's as fast and clean as it gets.

Related answers:

Thanks for the answer. just to clarify,the purpose of the trigger is to check for a value on the 'entry' table that references sno, if that exists copy it to the 'cancel' table and then delete the row on 'entry' to allow the 'student' to be deleted (as there is a foreign key constraint). So I assume if I make the changes above I would need to move the DELETE elsewhere? Sorry if this makes no sense. — nscoppin, Dec 09 '14 at 00:27
@nscoppin: OK, I completely rewrote my answer after reading more thoroughly. You already accepted, but the answer wasn't actually right, yet. And much is still unclear ... — Erwin Brandstetter, Dec 09 '14 at 01:00
I'm afraid this hasn't fixed it yet either - still getting duplicate violation. I'll update my question to include the student schema. — nscoppin, Dec 09 '14 at 10:10
I've taken a slightly different approach - see my answer. I'd be interested to hear your thoughts on this method. — nscoppin, Dec 09 '14 at 10:44
@nscoppin: I think you are missing the elephant in the room. The exception is raised by the function ***`cancel_entry()`***, not `delete_student()`. Have you seen the latest updates to my answer? — Erwin Brandstetter, Dec 09 '14 at 11:27
You're right, I never noticed that! So obvious now you've pointed it out! I did read your update, but missed the reference to cancel_entry(). It's all resolved now, thank you for your help, again! — nscoppin, Dec 09 '14 at 13:58

score 0 · Answer 2 · answered Dec 09 '14 at 10:43

For now, I've decided to use the trigger below with an ON DELETE CASCADE constraint on the FOREIGN KEY in 'entry'

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

Duplicate key constraint error when inserting to empty table with no sequenced columns

2 Answers2