INSERT ON CONFLICT behavior

I am having a table like below -

root@:26257/van> SHOW CREATE TABLE keyval;
+--------+---------------------------------------------------------------------------------+
| Table  |                                   CreateTable                                   |
+--------+---------------------------------------------------------------------------------+
| keyval | CREATE TABLE keyval (
                                                      |
|        |     customerid INT NOT NULL,
                                               |
|        |     keystring STRING NOT NULL,
                                             |
|        |     valuestring STRING NULL,
                                               |
|        |     expirytime INT NULL,
                                                   |
|        |     version INT NULL DEFAULT 1:::INT,
                                      |
|        |     CONSTRAINT "primary" PRIMARY KEY (customerid ASC, keystring ASC),
      |
|        |     INDEX keyval_by_cid_expiry_time (customerid ASC, expirytime ASC),
      |
|        |     FAMILY "primary" (customerid, keystring, valuestring, expirytime, version)

|
| | ) |
±-------±--------------------------------------------------------------------------------+

Executed the following commands-

root@:26257/van> INSERT  INTO  keyval VALUES (1,'IPMetaDataKey','1b2b7976b44bfa609156dc849392a144',9223372036854775807,1);
INSERT 1

root@:26257/van> INSERT  INTO  keyval VALUES (1,'IPMetaDataKey','1b2b7976b44bfa609156dc849392a144',9223372036854775807,1) ON CONFLICT(customerid,keystring) DO UPDATE SET version = excluded.version+1;
INSERT 1
root@:26257/van> SELECT * FROM keyval;
+------------+---------------+----------------------------------+---------------------+---------+
| customerid |   keystring   |           valuestring            |     expirytime      | version |
+------------+---------------+----------------------------------+---------------------+---------+
|          1 | IPMetaDataKey | 1b2b7976b44bfa609156dc849392a144 | 9223372036854775807 |       2 |
+------------+---------------+----------------------------------+---------------------+---------+
(1 row)
root@:26257/van> INSERT  INTO  keyval VALUES (1,'IPMetaDataKey','1b2b7976b44bfa609156dc849392a144',8223372036854775807,1) ON CONFLICT(customerid,keystring) DO UPDATE SET version = excluded.version+1;
INSERT 1
root@:26257/van> SELECT * FROM keyval;
+------------+---------------+----------------------------------+---------------------+---------+
| customerid |   keystring   |           valuestring            |     expirytime      | version |
+------------+---------------+----------------------------------+---------------------+---------+
|          1 | IPMetaDataKey | 1b2b7976b44bfa609156dc849392a144 | 9223372036854775807 |       2 |
+------------+---------------+----------------------------------+---------------------+---------+
(1 row)

Can some one let me know why the last INSERT ON CONFLICT is reporting INSERT 1 when nothing seems to be inserted?

Is this some bug on Cockroach side?

@tuk, I just reproduced this and don’t understand why it’s happening either. I’ll find someone more familiar with INSERT ON CONFLICT to check as well.

excluded.version refers to the version from the VALUES clause (i.e., 1), so this ON CONFLICT clause will always set the value to 2. (and then it returns INSERT 1, because this number is the number of rows affected by either the INSERT or UPDATE parts of the statement). Use UPDATE SET verson=keyval.version+1 instead of excluded.version and it will do what you want:

root@:26257/test> create table conflict_test (id string primary key, version int);
CREATE TABLE
root@:26257/test> INSERT INTO conflict_test VALUES ('abc', 1) ON CONFLICT(id) DO UPDATE SET version = conflict_test.version + 1;
INSERT 1

Time: 3.114146ms

root@:26257/test> select * from conflict_test;
+-----+---------+
| id  | version |
+-----+---------+
| abc |       1 |
+-----+---------+
(1 row)

Time: 7.726204ms

root@:26257/test> INSERT INTO conflict_test VALUES ('abc', 1) ON CONFLICT(id) DO UPDATE SET version = conflict_test.version + 1;
INSERT 1

Time: 2.15163ms

root@:26257/test> SELECT * FROM conflict_test;
+-----+---------+
| id  | version |
+-----+---------+
| abc |       2 |
+-----+---------+
(1 row)

Time: 1.012494ms

root@:26257/test> INSERT INTO conflict_test VALUES ('abc', 1) ON CONFLICT(id) DO UPDATE SET version = conflict_test.version + 1;
INSERT 1

Time: 1.689036ms

root@:26257/test> SELECT * FROM conflict_test;
+-----+---------+
| id  | version |
+-----+---------+
| abc |       3 |
+-----+---------+
(1 row)

Time: 1.223466ms

(I don’t think there’s a difference between versions, but FYI I’m testing on release 1.1.1)

@jesse @bdarnell - Don’t you think this INSERT 1 output is bit confusing? This feels like one value is inserted in the table which in this case is not

It may be confusing, but PostgreSQL does exactly the same thing. The number that appears at command completion is always the number of rows affected, even though complex commands can affect rows in multiple ways. Maybe instead of displaying the command tag verbatim we should reformat it to something like “1 row affected” and not display the verb.

Yes 1 row affected will be nice.

Do you want me to file an issue in github for updating the output message?

I filed https://github.com/cockroachdb/cockroach/issues/19625