search

yugabyte / yugabyte-db

YugabyteDB - the cloud native distributed SQL database for mission-critical applications.
https://www.yugabyte.com
Other
9.05k stars 1.08k forks source link

[YSQL] Insert Query with ON CONFLICT Fails Due to Duplicate Key Error on Unique Constraint on third execution #25075

Open hariharasudhan-yb opened 4 days ago

hariharasudhan-yb commented 4 days ago

Jira Link: DB-14205

Description

Repro and schema details as following , This is in version 2.25.0.0-b350 . With and without batching same issue schema :

ioc_db=# \d new_ioc_table;
                Table "public.new_ioc_table"
  Column  | Type | Collation | Nullable |      Default
----------+------+-----------+----------+--------------------
 id       | text |           | not null |
 uuid_col | uuid |           | not null | uuid_generate_v4()
 name     | text |           | not null |
Indexes:
    "new_ioc_table_pkey" PRIMARY KEY, lsm ((id, name) HASH, uuid_col ASC)
    "new_ioc_table_uuid_col_key" UNIQUE CONSTRAINT, lsm (uuid_col HASH)
    "new_u_key_name" UNIQUE CONSTRAINT, lsm (name HASH)
    "new_unique_id_name_idx" UNIQUE, lsm (name HASH, id ASC)

First insert - succeeded 

ioc_db=# select * from new_ioc_table;
 id | uuid_col | name
----+----------+------
(0 rows)

ioc_db=# EXPLAIN (ANALYZE,DIST)INSERT INTO public.new_ioc_table (
    id, uuid_col, name
)
VALUES
    ('user-id-1-1', uuid_generate_v4(), 'Test Name 1')
ON CONFLICT (id, name)
DO UPDATE SET
    uuid_col = EXCLUDED.uuid_col;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Insert on new_ioc_table  (cost=0.00..0.01 rows=0 width=0) (actual time=0.636..0.637 rows=0 loops=1)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: new_unique_id_name_idx
   Tuples Inserted: 1
   Conflicting Tuples: 0
   ->  Result  (cost=0.00..0.01 rows=1 width=80) (actual time=0.037..0.038 rows=1 loops=1)
         Storage Index Read Requests: 1
         Storage Index Read Execution Time: 0.434 ms
         Storage Table Write Requests: 1
         Storage Index Write Requests: 3
 Planning Time: 0.028 ms
 Execution Time: 6.636 ms
 Storage Read Requests: 1
 Storage Read Execution Time: 0.434 ms
 Storage Rows Scanned: 0
 Storage Write Requests: 4
 Catalog Read Requests: 0
 Catalog Write Requests: 0
 Storage Flush Requests: 1
 Storage Flush Execution Time: 5.916 ms
 Storage Execution Time: 6.350 ms
 Peak Memory Usage: 24 kB
(22 rows)

second insert : AS i'm executing the same query again , there is a conflict on (id,name) and resolution is to update the uuid_col - succeded

ioc_db=# set yb_insert_on_conflict_read_batch_size =10;
SET
ioc_db=# EXPLAIN (ANALYZE,DIST)INSERT INTO public.new_ioc_table (
    id, uuid_col, name
)
VALUES
    ('user-id-1-1', uuid_generate_v4(), 'Test Name 1')
ON CONFLICT (id, name)
DO UPDATE SET
    uuid_col = EXCLUDED.uuid_col;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Insert on new_ioc_table  (cost=0.00..0.01 rows=0 width=0) (actual time=1.106..1.106 rows=0 loops=1)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: new_unique_id_name_idx
   Tuples Inserted: 0
   Conflicting Tuples: 1
   Storage Table Read Requests: 1
   Storage Table Read Execution Time: 0.323 ms
   Storage Table Rows Scanned: 1
   Storage Index Read Requests: 1
   Storage Index Read Execution Time: 0.469 ms
   Storage Index Rows Scanned: 1
   Storage Table Write Requests: 2
   Storage Index Write Requests: 2
   ->  Result  (cost=0.00..0.01 rows=1 width=80) (actual time=0.034..0.035 rows=1 loops=1)
 Planning Time: 0.030 ms
 Execution Time: 3.627 ms
 Storage Read Requests: 2
 Storage Read Execution Time: 0.792 ms
 Storage Rows Scanned: 2
 Storage Write Requests: 4
 Catalog Read Requests: 0
 Catalog Write Requests: 0
 Storage Flush Requests: 1
 Storage Flush Execution Time: 2.452 ms
 Storage Execution Time: 3.244 ms
 Peak Memory Usage: 24 kB
(26 rows)

ioc_db=# select * from new_ioc_table;
     id      |               uuid_col               |    name
-------------+--------------------------------------+-------------
 user-id-1-1 | 0ed79e20-d539-4400-a864-e51ea70ca5f0 | Test Name 1
(1 row)

Third time - Executing the same query again , it should perform the same ( it should update the uuid_col column again but it failed , throwing error - duplicate key value violates unique constraint "new_u_key_name")

ioc_db=# EXPLAIN (ANALYZE,DIST)INSERT INTO public.new_ioc_table (
    id, uuid_col, name
)
VALUES
    ('user-id-1-1', uuid_generate_v4(), 'Test Name 1')
ON CONFLICT (id, name)
DO UPDATE SET
    uuid_col = EXCLUDED.uuid_col;
ERROR:  duplicate key value violates unique constraint "new_u_key_name"

Postgres behaviour (15.2): In postgres , It doesn't throw the error

Had the same schema 

ioc_db=# \d new_ioc_table
        Table "public.new_ioc_table"
 Column | Type | Collation | Nullable |   Default    
----------+------+-----------+----------+--------------------
 id    | text |      | not null | 
 uuid_col | uuid |      | not null | uuid_generate_v4()
 name   | text |      | not null | 
Indexes:
  "new_ioc_table_pkey" PRIMARY KEY, btree (id, name, uuid_col)
  "new_ioc_table_uuid_col_key" UNIQUE CONSTRAINT, btree (uuid_col)
  "new_u_key_name" UNIQUE CONSTRAINT, btree (name)
  "new_unique_id_name_idx" UNIQUE, btree (name, id)

Anytime i repeat the query it succeeded updating the uuid_col

sample plan :

ioc_db=# EXPLAIN (ANALYZE)INSERT INTO public.new_ioc_table (
  id, uuid_col, name
)
VALUES
  ('user-id-1-1', uuid_generate_v4(), 'Test Name 1')
ON CONFLICT (id, name)
DO UPDATE SET
  uuid_col = EXCLUDED.uuid_col;
                       QUERY PLAN                        
-----------------------------------------------------------------------------------------------------
 Insert on new_ioc_table (cost=0.00..0.01 rows=0 width=0) (actual time=0.482..0.483 rows=0 loops=1)
  Conflict Resolution: UPDATE
  Conflict Arbiter Indexes: new_unique_id_name_idx
  Tuples Inserted: 0
  Conflicting Tuples: 1
  -> Result (cost=0.00..0.01 rows=1 width=80) (actual time=0.041..0.042 rows=1 loops=1)
 Planning Time: 0.095 ms
 Execution Time: 0.543 ms
(8 rows)

ioc_db=# select * from new_ioc_table;
   id   |        uuid_col        |  name   
-------------+--------------------------------------+-------------
 user-id-1-1 | 1fe80fd0-8a94-4fd0-b484-a3ce39233bf4 | Test Name 1

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

jasonyb commented 4 days ago

Please make copy-pastable instructions next time. It takes a while to parse your text and reverse engineer what you are doing.

create table new_ioc_table (id text, uuid_col uuid, name text, primary key ((id, name), uuid_col), unique (uuid_col), unique (name));
create unique index on new_ioc_table (name, id);
create extension "uuid-ossp";
EXPLAIN (ANALYZE,DIST)INSERT INTO public.new_ioc_table (
    id, uuid_col, name
)
VALUES
    ('user-id-1-1', uuid_generate_v4(), 'Test Name 1')
ON CONFLICT (id, name)
DO UPDATE SET
    uuid_col = EXCLUDED.uuid_col;
EXPLAIN (ANALYZE,DIST)INSERT INTO public.new_ioc_table (
    id, uuid_col, name
)
VALUES
    ('user-id-1-1', uuid_generate_v4(), 'Test Name 1')
ON CONFLICT (id, name)
DO UPDATE SET
    uuid_col = EXCLUDED.uuid_col;
EXPLAIN (ANALYZE,DIST)INSERT INTO public.new_ioc_table (
    id, uuid_col, name
)
VALUES
    ('user-id-1-1', uuid_generate_v4(), 'Test Name 1')
ON CONFLICT (id, name)
DO UPDATE SET
    uuid_col = EXCLUDED.uuid_col;
jasonyb commented 4 days ago

Narrowed down to commit aad56955e18179d6c9fbaab4652777ba144d607d. cc: @karthik-ramanathan-3006

andrei-mart commented 3 days ago

When statement 

INSERT INTO public.new_ioc_table (
    id, uuid_col, name
)
VALUES
    ('user-id-1-1', uuid_generate_v4(), 'Test Name 1')
ON CONFLICT (id, name)
DO UPDATE SET
    uuid_col = EXCLUDED.uuid_col;

inserts a conflicting record it modifies the primary key, so all 3 secondary indexes are to be updated. The keys of new_u_key_name and new_unique_id_name_idx are not modified, so their records can be updated, new_ioc_table_uuid_col_key requires its record to be deleted and new one inserted.

If conflict happens when ysql_yb_skip_redundant_update_ops flag is set to true, Postgres sends seemingly correct set of write operations, however the updated records of new_u_key_name and new_unique_id_name_idx disappear:

yugabyte=# explain select name from new_ioc_table where name = 'user-id-1-1';
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Index Only Scan using new_u_key_name on new_ioc_table  (cost=0.00..4.11 rows=1 width=32)
   Index Cond: (name = 'user-id-1-1'::text)
(2 rows)

yugabyte=# select name from new_ioc_table where name = 'user-id-1-1';
 name 
------
(0 rows)

yugabyte=# explain select name, id from new_ioc_table where name = 'user-id-1-1' and id > '';
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Index Only Scan using new_unique_id_name_idx on new_ioc_table  (cost=0.00..4.12 rows=1 width=64)
   Index Cond: ((name = 'user-id-1-1'::text) AND (id > ''::text))
(2 rows)

yugabyte=# select name, id from new_ioc_table where name = 'user-id-1-1' and id > '';
 name | id 
------+----
(0 rows)

The new_unique_id_name_idx is the arbiter index, that explains why Postgres can't see the conflict, but it is not clear how the new_u_key_name constraint fails.