CODEWITHSUNDEEP
ruby-on-railspostgresql
Nona
Nona

Reputation: 5462

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

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

Upvotes: 6

Views: 2938

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246513

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.

Upvotes: 5

Related Questions