Trying to use: INSERT ON CONFLICT UPDATE SET

CrateDB SQL
1

Hi,
Using a single node CrateDB (v5.3.1) install, am getting unexpected behaviour when attempting to use “ON CONFLICT” when inserting. Has anyone else experience with this?

/***************
 * Testing INSERT ON CONFLICT UPDATE SET
 ***************/
drop table if exists history_tsx
;
CREATE  TABLE history_tsx (
  name TEXT NOT NULL,
  pointType TEXT NOT NULL,
  timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
  ts_day TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS date_trunc('day', timestamp),
  stringValue VARCHAR(255),
  value DOUBLE precision,
  primary key ("name", "ts_day", "timestamp")
) PARTITIONED BY (ts_day)
WITH (number_of_routing_shards = 4, number_of_replicas = 0, "write.wait_for_active_shards" = 1)
;
REFRESH TABLE history_tsx
;
INSERT INTO history_tsx 
("name", "pointtype", "timestamp", "value") VALUES 
('DEVICES.1  ', 'Application Point', '2023-05-01T16:37:59.7880000Z', 559) 
;
REFRESH TABLE history_tsx
;
INSERT INTO history_tsx 
("name", "pointtype", "timestamp", "value") VALUES 
('DEVICES.1  ', 'Application Point', '2023-05-01T16:37:59.7880000Z', 999) 
ON CONFLICT ("name", "ts_day", "timestamp") DO UPDATE SET 
	value = excluded."value"
;
REFRESH TABLE history_tsx
;
INSERT INTO history_tsx 
("name", "pointtype", "timestamp", "value") VALUES 
('DEVICES.2  ', 'Application Point', '2023-05-02T16:37:59.7880000Z', 559),
('DEVICES.2  ', 'Application Point', '2023-05-02T16:37:59.7880000Z', 999) 
;
REFRESH TABLE history_tsx
;
INSERT INTO history_tsx 
("name", "pointtype", "timestamp", "value") VALUES 
('DEVICES.3  ', 'Application Point', '2023-05-03T16:37:59.7880000Z', 559),
('DEVICES.3  ', 'Application Point', '2023-05-03T16:37:59.7880000Z', 999) 
ON CONFLICT (name, ts_day, timestamp) DO UPDATE SET 
	value = excluded."value"
;
REFRESH TABLE history_tsx
;
select "name", "ts_day", "timestamp", value
FROM history_tsx
;
-------------------------------------------------------------------
-- What I saw:
name       |ts_day                 |timestamp              | value|
-----------+-----------------------+-----------------------+------+
DEVICES.1  |2023-05-01 01:00:00.000|2023-05-01 17:37:59.788| 559.0|
DEVICES.2  |2023-05-02 01:00:00.000|2023-05-02 17:37:59.788| 559.0|
-------------------------------------------------------------------
-- What I expected to see:
name       |ts_day                 |timestamp              | value|
-----------+-----------------------+-----------------------+------+
DEVICES.1  |2023-05-01 01:00:00.000|2023-05-01 17:37:59.788| 999.0|
DEVICES.2  |2023-05-02 01:00:00.000|2023-05-02 17:37:59.788| 559.0|
DEVICES.3  |2023-05-03 01:00:00.000|2023-05-02 17:37:59.788| 999.0|
-------------------------------------------------------------------
1 Like
2

Hi Andy,

I tested other versions and could identify that the unexpected behavior began in 5.3.0. In the different versions tested, i.e. 5.2.2, 5.2.6, and 5.2.8 the expected behavior was observed.

I’ll report this to the team and keep you posted. In the meantime, I’d suggest you downgrade to a previous version that doesn’t present the reported issue.

Thank you!

Karyn Azevedo

2 Likes
3

Thanks Karyn, appreciate the swift response!
Andy

1 Like
4

Hi,
I tested and this problem is now solved in our latest nightly build, so you could use version 5.2.8 for the time being and upgrade to 5.3.3 or 5.4.0 once they are released.
Thank you.

2 Likes
5

Any date yet when 5.3.3. will be GA?

6

Hi,
5.3.3 was released to the testing channel yesterday and if there are no issues the plan is to promote it to stable before the end of the week.

1 Like