Insert Ignore behavior

General
#1

Coming from Mysql world, we have a process to insert user jobs log into a table like this.

CREATE TABLE joblog (
id INT NOT NULL DEFAULT unique_rowid(),
uid INT NOT NULL,
jid INT NOT NULL,
date DATE NOT NULL,
CONSTRAINT “primary” PRIMARY KEY (id ASC),
INDEX joblog_idx (uid ASC),
UNIQUE INDEX uk_joblog (date ASC, jid ASC, uid ASC)
)

In my old application, I can insert log like this

insert ignore into joblog (UID, jid, date) values
(1608172896,5504,‘2017-12-17’), (1608172896,5504,‘2017-12-17’);

Which two records are the same and without issue.

Howerver, in CRDB

insert into joblog (uID, jid, date) values
(1608172896,5504,‘2017-12-17’), (1608172896,5504,‘2017-12-17’);
on conflict(date,jid,uID) do nothing;

I will get if the table does not contain this unqiue row
pq: duplicate key value (date,jid,uID)=(‘2017-12-17’,5504,1608172896) violates unique constraint “uk_joblog”

If the table contains this unique row, the error will be

pq: UPSERT/ON CONFLICT DO UPDATE command cannot affect row a second time

Not sure if this is a bug, if it is not, how can I do this in CRDB way?

Thanks.

#2

Sounds like a bug…

One obvious work around is to break it into multiple inserts as the on conflict seems fine if only doing one row (or multiple rows that don’t have duplicates).

Another options would be to rewrite as something like:
insert into joblog (uID, jid, date) select 1608172896, 5504, ‘2017-12-17’ union select 1608172896, 5504, ‘2017-12-17’ on conflict (date,jid,uID) do nothing;

so the duplicates are pre-merged via the union… seems to work around the problem if you want to keep it to single statement…

#3

@justin do you mind taking a look at this issue and filing something on github if it is a bug?

#4

Should I put this as a bug in github? The work around “union select” only works if two rows are exactly the same. If the rows have same unique ID but different value on the other columns, we still get the same “UPSERT/ON CONFLICT DO UPDATE command cannot affect row a second time” error.

Thansk,

#5

Hi @leokwan,

It looks like this may have gotten lost due to the holidays. Filing this as a bug on github would be very helpful, thanks!