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
Nobody has claimed this yet.
Assessment
- Difficulty
- 5/5
- Estimated time
- Over a week
- Newbie friendliness
- 20/100
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
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.
- 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. WithUPSERT, the existing row is overwritten with the new row's
other column values, so its own data is lost as well. - 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,
UNIQUEonREGIONAL BY ROWtables), the check for the arbiter constraint is skipped. A duplicate is stored
that a plainINSERTwould reject. With an ordinaryUNIQUEindex the statement fails with
duplicate key value violates unique constraint, where PostgreSQL takes theDO UPDATEpath.
FK, CHECK and NOT NULL constraints are evaluated on the trigger's values. Without the trigger, all cases behave
correctly.
To Reproduce
cockroach start-single-node --insecurecockroach 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):buildInputForUpsertbuilds 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 beforebuildRowLevelBeforeTriggers(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
masterHEAD on 2026-10-18), built
from source withbazel 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 demowith 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
- No Dockerfile or Docker Compose file
- No pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from cockroachdb/cockroach
-
C-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
cockroachdb/cockroach#176245 · 1 comment ·
-
sql: pg_database.dattablespace is 0 but pg_tablespace.oid is 1663, so they no longer joinPossibly taken @1AbdullahButt claimed this 14 days ago. OpenC-bug O-community X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
cockroachdb/cockroach#176228 · 1 comment ·
-
PG compat: overflow in integer power calculation triggers incompatible SQLSTATEMay be free again @darshpatel1052 claimed this 18 days ago, and no pull request is open. OpenC-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
cockroachdb/cockroach#176223 · 2 comments ·
-
util/log: bufferedSinkCloser.Close leaks a goroutine after timeoutPossibly taken @dajiaohuang claimed this 14 days ago. OpenC-bug O-community X-blathers-untriaged
Difficulty 1/5 Under an hour Newbie friendliness 84/100
cockroachdb/cockroach#176210 · 1 comment ·
-
C-bug O-community T-sql-queries X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
cockroachdb/cockroach#176150 · 1 comment ·
All issues in cockroachdb/cockroach
Similar issues
-
area/import-export area/projects concern/ux
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
go-vikunja/vikunja#4455 · 1 comment ·
Maintainers usually reply within 2 days
-
Dependabot PRs no longer merge automaticallyPossibly taken @dwisiswant0 claimed this today. Open
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
projectdiscovery/urlfinder#160 ·
Maintainers usually reply within 1 day
-
database
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
chnm/legal-modernism#450 ·
Maintainers usually reply within 1 day
-
tech-debt
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
Maintainers usually reply within 1 day
-
bug ready-for-triage ready-to-code
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
RedHatInsights/insights-ingress-go#946 · 4 comments ·
Maintainers usually reply within 1 day