sql: `UPSERT` / `INSERT ... ON CONFLICT DO UPDATE` arbitrate on the row before the BEFORE INSERT trigger runs: the trigger's row is dropped, another row is updated or overwritten, and some UNIQUE constraints admit duplicates

Open
#176,184 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
5/5
Estimated time
Over a week
Newbie friendliness
20/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
go, postgresql, sql
Domain
backend, databases

Research direction

Start in pkg/sql/opt/optbuilder/insert.go, where the issue identifies the ordering of buildInputForUpsert and buildRowLevelBeforeTriggers; compare it with the DO NOTHING path, which runs triggers first. Then read pkg/sql/opt/optbuilder/mutation_builder_unique.go around buildUniqueChecksForUpsert, where arbiter checks are skipped. Done means conflict arbitration uses the trigger’s returned row and all UNIQUE constraints reject duplicates, with regression coverage for the reported cases.

Written by the indexing model from the issue text.

Description

C-bug O-community T-sql-queries X-blathers-triaged X-move-internal

Describe the problem

When a row-level BEFORE INSERT trigger changes a column that the upsert uses to detect a conflict (the primary key
or the ON CONFLICT arbiter), CockroachDB decides between insert and update on the values from before the
trigger. PostgreSQL fires BEFORE INSERT triggers first and arbitrates on the row the trigger returns.

  1. The pre-trigger value conflicts with an existing row, and the trigger's value does not conflict. CockroachDB
    takes the update path. The row the trigger produced is never inserted, an existing, unrelated row is updated,
    and the statement reports 1 row affected. With UPSERT, the existing row is overwritten with the new row's
    other column values, so its own data is lost as well.
  2. The trigger's value conflicts with an existing row, and the pre-trigger value does not. For constraints that
    the optimizer enforces with explicit uniqueness checks rather than a unique index (UNIQUE WITHOUT INDEX,
    UNIQUE on REGIONAL BY ROW tables), the check for the arbiter constraint is skipped. A duplicate is stored
    that a plain INSERT would reject. With an ordinary UNIQUE index the statement fails with
    duplicate key value violates unique constraint, where PostgreSQL takes the DO UPDATE path.

FK, CHECK and NOT NULL constraints are evaluated on the trigger's values. Without the trigger, all cases behave
correctly.

To Reproduce

  1. cockroach start-single-node --insecure
  2. cockroach sql --insecure:
CREATE TABLE t (id INT8 PRIMARY KEY, b STRING UNIQUE, hits INT8 DEFAULT 0);
INSERT INTO t (id, b) VALUES (1, 'a');
-- a BEFORE INSERT trigger that rewrites the key of every new row
CREATE FUNCTION f() RETURNS TRIGGER LANGUAGE PLpgSQL AS $$ BEGIN NEW.id := NEW.id + 100; RETURN NEW; END $$;
CREATE TRIGGER tr BEFORE INSERT ON t FOR EACH ROW EXECUTE FUNCTION f();
-- the trigger's row (101, 'n') does not conflict with anything
INSERT INTO t (id, b) VALUES (1, 'n') ON CONFLICT (id) DO UPDATE SET hits = t.hits + 1;
SELECT * FROM t ORDER BY id;
UPSERT INTO t (id, b) VALUES (1, 'u');
SELECT * FROM t ORDER BY id;

-- UNIQUE WITHOUT INDEX: the trigger's value conflicts, the pre-trigger value does not
SET experimental_enable_unique_without_index_constraints = true;
CREATE TABLE u (id INT8 PRIMARY KEY, b STRING, hits INT8 DEFAULT 0, UNIQUE WITHOUT INDEX (b));
CREATE FUNCTION up() RETURNS TRIGGER LANGUAGE PLpgSQL AS $$ BEGIN NEW.b := upper(NEW.b); RETURN NEW; END $$;
CREATE TRIGGER tr BEFORE INSERT ON u FOR EACH ROW EXECUTE FUNCTION up();
INSERT INTO u (id, b) VALUES (1, 'ABC');
INSERT INTO u (id, b) VALUES (2, 'abc') ON CONFLICT (b) DO UPDATE SET hits = u.hits + 1;
SELECT * FROM u ORDER BY id;
INSERT INTO u (id, b) VALUES (3, 'abc');

Output on master:

INSERT 0 1
  id | b | hits
-----+---+-------
   1 | a |    1          <- (101, 'n') was not inserted; row 1 was updated instead
INSERT 0 1
  id | b | hits
-----+---+-------
   1 | u |    0          <- UPSERT overwrote row 1 ('a' is gone); (101, 'u') was not inserted
...
INSERT 0 1
  id |  b  | hits
-----+-----+-------
   1 | ABC |    0
   2 | ABC |    0          <- duplicate under UNIQUE WITHOUT INDEX (b)
ERROR: duplicate key value violates unique constraint "unique_b"   <- plain INSERT is rejected

PostgreSQL (18.6 and 20devel) with the same table and trigger:

INSERT INTO t (id, b) VALUES (1, 'n') ON CONFLICT (id) DO UPDATE SET hits = t.hits + 1;
 id  | b | hits
-----+---+------
   1 | a |    0
 101 | n |    0

The same happens with a UNIQUE index as the arbiter (trigger NEW.b := 'z',
INSERT (2, 'a') ON CONFLICT (b) DO UPDATE: CockroachDB updates row 1, PostgreSQL inserts (2, 'z')).
On a two-region cluster (cockroach demo --nodes=2 --demo-locality=region=us-east1,az=a:region=us-west1,az=a), a
REGIONAL BY ROW table with email STRING UNIQUE and a trigger NEW.email := lower(NEW.email) accepts
INSERT ... VALUES ('us-west1', 2, '[email protected]') ON CONFLICT (email) DO UPDATE ... next to the existing
('us-east1', 1, '[email protected]'). Both rows then hold [email protected], and a plain INSERT of the same email
is rejected with duplicate key value violates unique constraint "users_email_key".

Expected behavior

As in PostgreSQL: the BEFORE INSERT trigger runs first, and the conflict check uses the row it returns. A row
whose final key does not conflict is inserted. A row whose final key conflicts takes the DO UPDATE path, or the
statement fails if it cannot. The UNIQUE constraints hold in every case.

Additional data / screenshots

Cause on master, pkg/sql/opt/optbuilder/insert.go:

  • UPSERT (insert.go#L330-L370): buildInputForUpsert builds the arbiter left join
    on the input row (line 341). Only after that are the BEFORE INSERT triggers built (line 348). The comment at
    lines 345-347 says this order is deliberate ("so that there are no buffering operators between INSERT and
    UPDATE triggers").
  • INSERT ... ON CONFLICT DO UPDATE (insert.go#L372-L398): buildInputForUpsert
    (line 376) runs before buildRowLevelBeforeTriggers (line 379).
  • INSERT ... ON CONFLICT DO NOTHING (insert.go#L315-L328) runs the triggers first
    and is correct.

buildUniqueChecksForUpsert skips the uniqueness check of an arbiter constraint unless the DO UPDATE SET list
changes its columns (mutation_builder_unique.go#L167-L176). A
conflict that the trigger creates is therefore not caught later either. EXPLAIN of the u statement shows
arbiter constraints: unique_b and no uniqueness check.

Related but different: #132511 (the implementation of row-level BEFORE triggers), #176059 (skip_unique_checks
removing cross-region enforcement). A separate issue covers computed columns of upserted rows with BEFORE
triggers (stored computed columns and index keys taken from the fetched row).

Environment:

  • CockroachDB version: master at d30c905fff79 (2026-09-23, current public master HEAD on 2026-10-18), built
    from source with bazel build //pkg/cmd/cockroach-short --config=dev, and the edge binary
    v26.4.0-alpha.3-dev-74a73764f18b (built 2026-10-18). Also reproduced on v26.3.2, v26.4.0-beta.2 and
    v25.4.17.
  • Server OS: Linux (Ubuntu 22.04), single node, and cockroach demo with two regions
  • Client app: cockroach sql, psycopg2

Additional context

BEFORE INSERT triggers that normalize keys (lower-casing e-mail addresses, trimming codes, mapping external ids)
are common in applications ported from PostgreSQL, and so is the upsert idiom. With both, an acknowledged insert
can vanish, a different customer's row can be overwritten, and a UNIQUE constraint that plain INSERTs respect can
hold duplicates. No error is raised.

Jira issue: CRDB-70531

Dominant language
Go
Stars
32.6k
Forks
4.2k
PR merge metrics
No merged PRs in 30d

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from cockroachdb/cockroach

All issues in cockroachdb/cockroach

Similar issues

More Go issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.