Re: UPSERT with non-unique index

Sun, 2 Aug 2020 22:39:04 +0200, /Axel Schwenke/:
> On 02.08.2020 18:55, Stanimir Stamenkov wrote:
>
>> I need to implement concurrent-safe UPSERT
> ...
>> Traditional INSERT ... ON DUPLICATE KEY [1] doesn't work for me
> ...
>> There's an index:
>>
>>      CREATE INDEX ON table (col3, col4, col5)
>>
>> It is non-unique as there are legacy data that does not allow me to declare
>> it unique.
> 
> That is the point where I would solve it.
> 
> 1. add a new column `is_legacy_data`, i.e. TINYINT DEFAULT 0 that will be 0
> for all new columns
> 
> 2. for legacy columns with (col3, col4, col5) being not unique, set the new
> column to a value that makes (col3, col4, col5, `is_legacy_data`) unique
> 
> 3. add the unique index on (col3, col4, col5, `is_legacy_data`)

The current setup is similar, col5 is a new column which has a default 
value for applications which don't know about it.  Can't think of an 
easy way to make it unique and recognize the data is from an older 
application this way.  There will be a transitional period where old and 
new version applications will be pumping data into the table.

At the end, I'll be normalizing the data post application upgrade to 
ensure all (col3, col4, col5) are unique and finally make the index 
unique, but wanted to have some insurance no unnecessary duplicates may 
appear in the meantime.

> 4. be happy with INSERT ... ON DUPLICATE KEY ...

Insert attempts which will end up updates are too many to waste sequence 
keys for me.

-- 
Stanimir