Why does inserting duplicate values trigger this error in Postgres on a Rails 5 application?

Question

I am doing a bulk insertion of duplicate values (duplicate type and uuid) using the below Postgres query.

I see an error in my logs like ActiveRecord::StatementInvalid: PG::CardinalityViolation: ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

Why is this conflict resolution not working?

ActiveRecord::Base.connection.execute <<-POSTGRESQL
  INSERT INTO #{Movie.table_name} (#{headers})
  VALUES #{values}
  ON CONFLICT (uuid, type) DO UPDATE SET video_id = EXCLUDED.video_id,
  status = 1,
  category = EXCLUDED.category, updated_at = EXCLUDED.updated_at
POSTGRESQL


CREATE TABLE movies (
    id integer NOT NULL,
    video_id integer NOT NULL,
    category integer NOT NULL,
    uuid character varying NOT NULL,
    data json DEFAULT '{}'::json,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL,
    type character varying DEFAULT 'FeatureLengthVideo'::character varying,
    status integer DEFAULT 0 NOT NULL,
);

Laurenz Albe · Accepted Answer · 2016-12-08 21:42:14Z

5

This is pretty well explained in src/backend/executor/nodeModifyTable.c:

/*
 * This can occur when a just inserted tuple is updated again in
 * the same command. E.g. because multiple rows with the same
 * conflicting key values are inserted.
 *
 * This is somewhat similar to the ExecUpdate()
 * HeapTupleSelfUpdated case.  We do not want to proceed because
 * it would lead to the same row being updated a second time in
 * some unspecified order, and in contrast to plain UPDATEs
 * there's no historical behavior to break.
 *
 * It is the user's responsibility to prevent this situation from
 * occurring.  These problems are why SQL-2003 similarly specifies
 * that for SQL MERGE, an exception must be raised in the event of
 * an attempt to update the same row twice.
 */
if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetXmin(tuple.t_data)))
    ereport(ERROR,
            (errcode(ERRCODE_CARDINALITY_VIOLATION),
             errmsg("ON CONFLICT DO UPDATE command cannot affect row a second time"),
             errhint("Ensure that no rows proposed for insertion within the same command have duplicate constrained values.")));

So you have to make sure that you don't insert the same primary or unique key twice in the same statement.

answered Dec 8, 2016 at 21:42

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nona Over a year ago

Thanks, so if the primary key were named "id", could I fix it by adding id = EXCLUDED.id ?

Laurenz Albe Over a year ago

No - you have to fix the data so that they don't contain the same id twice.

Collectives™ on Stack Overflow

Why does inserting duplicate values trigger this error in Postgres on a Rails 5 application?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related