"UNIQUE constraint failed" error with UPSERT and UPDATE trigger that does INSERT OR REPLACE
(1) By Even Rouault (rouault) on 2022-10-16 18:06:01 [source]
Given test.sql:
create table foo(fid integer primary key, other integer);
CREATE VIRTUAL TABLE foo_rtree USING rtree(id, minx, maxx, miny, maxy);
-- same error if using a regular table instead of the rtree virtual table
-- CREATE TABLE foo_rtree(id INTEGER PRIMARY KEY, minx, maxx, miny, maxy);
CREATE TRIGGER "rtree_insert" AFTER INSERT ON foo BEGIN INSERT OR REPLACE INTO foo_rtree VALUES(NEW.fid, NEW.other, NEW.other, NEW.other, NEW.other); end;
CREATE TRIGGER "rtree_delete" AFTER DELETE ON foo BEGIN DELETE FROM foo_rtree WHERE id = OLD.fid; end;
CREATE TRIGGER "rtree_update" AFTER UPDATE OF other ON foo WHEN NEW.fid = OLD.fid BEGIN INSERT OR REPLACE INTO foo_rtree VALUES(NEW.fid, NEW.other, NEW.other, NEW.other, NEW.other); end;
insert into foo values (1, 10);
select * from foo_rtree;
insert or replace into foo values (1, 20);
select * from foo_rtree;
insert into foo values (1, 30) on conflict (fid) do update set other=excluded.other;
select * from foo_rtree;
-- workaround issue with rtree_update
DROP TRIGGER rtree_update;
CREATE TRIGGER "rtree_update" AFTER UPDATE OF other ON foo WHEN NEW.fid = OLD.fid BEGIN update foo_rtree set minx=NEW.other, miny=NEW.other, maxx=NEW.other, maxy=NEW.other WHERE foo_rtree.id=NEW.fid; end;
insert into foo values (1, 30) on conflict (fid) do update set other=excluded.other;
select * from foo_rtree;
| $ cat test.sql | ~/install-sqlite-3.39.0/bin/sqlite3 |
outputs
1|10.0|10.0|10.0|10.0
1|20.0|20.0|20.0|20.0
Runtime error near line 15: UNIQUE constraint failed: foo_rtree.id (19)
1|20.0|20.0|20.0|20.0
1|30.0|30.0|30.0|30.0
The error here occurs on the "insert into foo values (1, 30) on conflict (fid) do update set other=excluded.other;" upsert statement, and seems to be due to the "CREATE TRIGGER "rtree_update" AFTER UPDATE OF other ON foo WHEN NEW.fid = OLD.fid BEGIN INSERT OR REPLACE INTO foo_rtree VALUES(NEW.fid, NEW.other, NEW.other, NEW.other, NEW.other); end;", since changing the latter one to use an UPDATE instead of INSERT OR REPLACE works. Note that the error does not occur when using "insert or replace into foo values (1, 20);".
This is a simplified case which is admitedly a bit dummy. In the real use case, the upsert is needed since the conflict column is not the integer primary key but another unique column.
I don't think the error is expected, right ?
(2) By Richard Hipp (drh) on 2022-10-17 16:28:44 in reply to 1 [link] [source]
The CREATE TRIGGER documentation says:
An ON CONFLICT clause may be specified as part of an UPDATE or INSERT action within the body of the trigger. However if an ON CONFLICT clause is specified as part of the statement causing the trigger to fire, then conflict handling policy of the outer statement is used instead.
Furthermore, the UPSERT documentation says:
The conflict resolution algorithm for the update operation of the DO UPDATE clause is always ABORT. In other words, the behavior is as if the DO UPDATE clause were actually written as "DO UPDATE OR ABORT".
Combining these two rules means that all REPLACE statements that are within of any trigger that is invoked by ON CONFLICT DO UPDATE become ordinary INSERT statements. So, in other words, SQLite appears to be working as documented, if perhaps not the way you want it to work.
(3) By Artem (ardmn_) on 2023-01-09 02:22:56 in reply to 2 [link] [source]
Can you explain, please, why it works in this way? As for me
Combining these two rules means that all REPLACE statements that are within of ?any trigger that is invoked by ON CONFLICT DO UPDATE become ordinary INSERT statements. So, in other words, SQLite appears to be working as documented, if perhaps not the way you want it to work.
this behavior is strange... What a problems I'will get if
REPLACE