Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: any plan of implementing 'UPSERT' or 'ON DUPLICATE KEY UPDATE' ? #1962

Closed
arypurnomoz opened this issue Aug 5, 2015 · 10 comments
Closed

sql: any plan of implementing 'UPSERT' or 'ON DUPLICATE KEY UPDATE' ? #1962

arypurnomoz opened this issue Aug 5, 2015 · 10 comments
Assignees
@danhhz
Labels
C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) E-intermediate Intermediate complexity, needs a contributor with 3-6 months of past contribution experience.
Milestone
Q2

Comments

@arypurnomoz
Copy link

No description provided.

@petermattis
Copy link
Collaborator

There are no immediate plans. There is also REPLACE (https://dev.mysql.com/doc/refman/5.0/en/replace.html).

@tamird
Copy link
Contributor

tamird commented Aug 14, 2015

This would just require a flag on parser.Insert which would cause sql.(*planner).Insert to use Put instead of CPut. Tagging easy.

@tamird tamird added the E-easy Easy issue to tackle, requires little or no CockroachDB experience label Aug 14, 2015
@petermattis
Copy link
Collaborator

@tamird Not sure if just changing Put to CPut would be sufficient. On a duplicate primary key you would have to delete old column values and secondary indexes as Update does. It still isn't too difficult, but maybe not easy.

@petermattis petermattis added this to the 1.0 milestone Aug 14, 2015
@tbg tbg added the SQL label Aug 15, 2015
@tamird tamird added E-intermediate Intermediate complexity, needs a contributor with 3-6 months of past contribution experience. and removed E-easy Easy issue to tackle, requires little or no CockroachDB experience labels Aug 22, 2015
@fire
Copy link

fire commented Sep 7, 2015

Postgresql has its own implementation of "Upsert".

@petermattis
Copy link
Collaborator

Thanks for the pointer, @fire. I imagine we'll implement upsert for 1.0, though it isn't currently on our roadmap.

@petermattis petermattis added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) and removed SQL labels Feb 13, 2016
@archiecobbs
Copy link

+1 This is a must-have for me.

I simply want to use CockroachDB as a key/value store.

Since using CockroachDB directly as a key/value store is not recommended/possible, the only way to do this is by using SQL and creating a simple table with key and value columns.

OK fine...

But without this functionality there's no way to do a PUT!

@petermattis petermattis modified the milestones: Q2, 1.0 Apr 7, 2016
@petermattis
Copy link
Collaborator

@archiecobbs Thanks for the prioritization. We've wanted UPSERT for some of our internal uses.

@danhhz
Copy link
Contributor

danhhz commented Apr 14, 2016

@archiecobbs I'm starting on this and considering adding mysql's REPLACE first and then adding ON CONFLICT UPDATE/ON DUPLICATE KEY later as it's needed. I think REPLACE is enough for your key/value use, do you agree?

@archiecobbs
Copy link

@paperstreet Yes, that will work great. Thanks for the fast turnaround.

@danhhz
Copy link
Contributor

danhhz commented Apr 25, 2016

Sorry for the lack of updates, I've been taking this opportunity to clean some stuff up a bit: http://github.com/cockroachdb/cockroach/pull/6211. With that out of the way, I'm on to REPLACE itself

@danhhz danhhz mentioned this issue Apr 26, 2016
Merged
danhhz added a commit to danhhz/cockroach that referenced this issue May 2, 2016
@danhhz
sql: implement limited first version of UPSERT
8de99c2 
Specifically, add a batching tableWriter for the "short form" described in
https://github.com/cockroachdb/cockroach/blob/master/docs/RFCS/upsert.md.

For cockroachdb#1962.

Notable followups:
- Add support for the full ON CONFLICT DO UPDATE form.
- Hook returningHelper up to the tableWriter abstraction.
- Using fetchRows to compute fetch selectors in Insert/Upsert/Delete.

name                          old time/op    new time/op    delta
Insert1000_Cockroach-8          19.5ms ± 3%    19.8ms ± 6%    ~             (p=0.690 n=5+5)
Update1000_Cockroach-8          43.4ms ± 3%    43.5ms ± 6%    ~             (p=0.841 n=5+5)
Delete1000_Cockroach-8          65.1ms ± 2%    64.9ms ± 6%    ~             (p=1.000 n=5+5)
Scan1000_Cockroach-8            2.24ms ± 4%    2.15ms ± 2%  -3.97%          (p=0.016 n=5+5)
TrackChoices1000_Cockroach-8     153µs ± 6%     148µs ± 4%    ~             (p=0.222 n=5+5)

name                          old alloc/op   new alloc/op   delta
Insert1000_Cockroach-8          3.66MB ± 1%    3.64MB ± 1%    ~             (p=0.095 n=5+5)
Update1000_Cockroach-8          6.23MB ± 1%    6.21MB ± 0%    ~             (p=0.548 n=5+5)
Delete1000_Cockroach-8          2.68MB ± 0%    2.68MB ± 0%    ~             (p=0.413 n=5+4)
Scan1000_Cockroach-8             313kB ± 0%     313kB ± 0%    ~             (p=1.000 n=5+5)
TrackChoices1000_Cockroach-8    23.2kB ± 4%    23.7kB ± 6%    ~             (p=0.317 n=5+5)

name                          old allocs/op  new allocs/op  delta
Insert1000_Cockroach-8           25.6k ± 1%     25.5k ± 1%    ~             (p=0.095 n=5+5)
Update1000_Cockroach-8           41.5k ± 0%     41.4k ± 0%    ~             (p=0.206 n=5+5)
Delete1000_Cockroach-8           16.7k ± 0%     16.7k ± 0%    ~             (p=0.548 n=5+5)
Scan1000_Cockroach-8             2.29k ± 0%     2.29k ± 0%    ~     (all samples are equal)
TrackChoices1000_Cockroach-8       115 ± 2%       116 ± 2%    ~             (p=0.429 n=5+5)

(The Scan1000 movement is noise.)
@danhhz danhhz mentioned this issue May 2, 2016
Merged
danhhz added a commit to danhhz/cockroach that referenced this issue May 3, 2016
@danhhz
sql: implement limited first version of UPSERT
3133b2f 
Specifically, add a batching tableWriter for the "short form" described in
https://github.com/cockroachdb/cockroach/blob/master/docs/RFCS/upsert.md.

For cockroachdb#1962.

Notable followups:
- Add support for the full ON CONFLICT DO UPDATE form.
- Hook returningHelper up to the tableWriter abstraction.
- Using fetchRows to compute fetch selectors in Insert/Upsert/Delete.
danhhz added a commit to danhhz/cockroach that referenced this issue May 3, 2016
@danhhz
sql: implement limited first version of UPSERT
0323834 
Specifically, add a batching tableWriter for the "short form" described in
https://github.com/cockroachdb/cockroach/blob/master/docs/RFCS/upsert.md.

For cockroachdb#1962.

Notable followups:
- Add support for the full ON CONFLICT DO UPDATE form.
- Hook returningHelper up to the tableWriter abstraction.
- Using fetchRows to compute fetch selectors in Insert/Upsert/Delete.
danhhz added a commit to danhhz/cockroach that referenced this issue May 3, 2016
@danhhz
sql: implement limited first version of UPSERT
004d7c7 
Specifically, add a batching tableWriter for the "short form" described in
https://github.com/cockroachdb/cockroach/blob/master/docs/RFCS/upsert.md.

For cockroachdb#1962.

Notable followups:
- Add support for the full ON CONFLICT DO UPDATE form.
- Hook returningHelper up to the tableWriter abstraction.
- Using fetchRows to compute fetch selectors in Insert/Upsert/Delete.
danhhz added a commit to danhhz/cockroach that referenced this issue May 9, 2016
@danhhz
sql: ON CONFLICT DO UPDATE form of upsert
5bfb714 
name                    old time/op    new time/op    delta
Upsert1_Cockroach-8       1.15ms ± 5%    1.18ms ± 1%    ~     (p=0.151 n=5+5)
Upsert10_Cockroach-8      1.71ms ± 1%    1.75ms ± 1%  +2.26%  (p=0.008 n=5+5)
Upsert100_Cockroach-8     7.31ms ± 1%    7.41ms ± 2%    ~     (p=0.056 n=5+5)
Upsert1000_Cockroach-8    74.2ms ± 4%    74.2ms ± 3%    ~     (p=1.000 n=5+5)

name                    old alloc/op   new alloc/op   delta
Upsert1_Cockroach-8       82.4kB ± 0%    86.7kB ± 0%  +5.11%  (p=0.008 n=5+5)
Upsert10_Cockroach-8       164kB ± 0%     168kB ± 0%  +2.45%  (p=0.008 n=5+5)
Upsert100_Cockroach-8     1.05MB ± 0%    1.06MB ± 0%  +0.89%  (p=0.008 n=5+5)
Upsert1000_Cockroach-8    9.19MB ± 0%    9.27MB ± 0%  +0.90%  (p=0.008 n=5+5)

name                    old allocs/op  new allocs/op  delta
Upsert1_Cockroach-8        1.09k ± 0%     1.17k ± 0%  +7.40%  (p=0.008 n=5+5)
Upsert10_Cockroach-8       1.60k ± 0%     1.69k ± 0%  +5.58%  (p=0.008 n=5+5)
Upsert100_Cockroach-8      7.05k ± 0%     7.32k ± 0%  +3.87%  (p=0.008 n=5+5)
Upsert1000_Cockroach-8     60.9k ± 0%     63.0k ± 0%  +3.39%  (p=0.008 n=5+5)

The benchmarks will get better once I do the "all Puts" optimization, which is
coming next.

Closes cockroachdb#1962.
@danhhz danhhz mentioned this issue May 9, 2016
Merged
danhhz added a commit to danhhz/cockroach that referenced this issue May 10, 2016
@danhhz
sql: ON CONFLICT DO UPDATE form of upsert
b909e40 
name                    old time/op    new time/op    delta
Upsert1_Cockroach-8       1.15ms ± 5%    1.18ms ± 1%    ~     (p=0.151 n=5+5)
Upsert10_Cockroach-8      1.71ms ± 1%    1.75ms ± 1%  +2.26%  (p=0.008 n=5+5)
Upsert100_Cockroach-8     7.31ms ± 1%    7.41ms ± 2%    ~     (p=0.056 n=5+5)
Upsert1000_Cockroach-8    74.2ms ± 4%    74.2ms ± 3%    ~     (p=1.000 n=5+5)

name                    old alloc/op   new alloc/op   delta
Upsert1_Cockroach-8       82.4kB ± 0%    86.7kB ± 0%  +5.11%  (p=0.008 n=5+5)
Upsert10_Cockroach-8       164kB ± 0%     168kB ± 0%  +2.45%  (p=0.008 n=5+5)
Upsert100_Cockroach-8     1.05MB ± 0%    1.06MB ± 0%  +0.89%  (p=0.008 n=5+5)
Upsert1000_Cockroach-8    9.19MB ± 0%    9.27MB ± 0%  +0.90%  (p=0.008 n=5+5)

name                    old allocs/op  new allocs/op  delta
Upsert1_Cockroach-8        1.09k ± 0%     1.17k ± 0%  +7.40%  (p=0.008 n=5+5)
Upsert10_Cockroach-8       1.60k ± 0%     1.69k ± 0%  +5.58%  (p=0.008 n=5+5)
Upsert100_Cockroach-8      7.05k ± 0%     7.32k ± 0%  +3.87%  (p=0.008 n=5+5)
Upsert1000_Cockroach-8     60.9k ± 0%     63.0k ± 0%  +3.39%  (p=0.008 n=5+5)

The benchmarks will get better once I do the "all Puts" optimization, which is
coming next.

Closes cockroachdb#1962.
danhhz added a commit to danhhz/cockroach that referenced this issue May 10, 2016
@danhhz
sql: ON CONFLICT DO UPDATE form of upsert
f83bc28 
name                    old time/op    new time/op    delta
Upsert1_Cockroach-8       1.15ms ± 5%    1.18ms ± 1%    ~     (p=0.151 n=5+5)
Upsert10_Cockroach-8      1.71ms ± 1%    1.75ms ± 1%  +2.26%  (p=0.008 n=5+5)
Upsert100_Cockroach-8     7.31ms ± 1%    7.41ms ± 2%    ~     (p=0.056 n=5+5)
Upsert1000_Cockroach-8    74.2ms ± 4%    74.2ms ± 3%    ~     (p=1.000 n=5+5)

name                    old alloc/op   new alloc/op   delta
Upsert1_Cockroach-8       82.4kB ± 0%    86.7kB ± 0%  +5.11%  (p=0.008 n=5+5)
Upsert10_Cockroach-8       164kB ± 0%     168kB ± 0%  +2.45%  (p=0.008 n=5+5)
Upsert100_Cockroach-8     1.05MB ± 0%    1.06MB ± 0%  +0.89%  (p=0.008 n=5+5)
Upsert1000_Cockroach-8    9.19MB ± 0%    9.27MB ± 0%  +0.90%  (p=0.008 n=5+5)

name                    old allocs/op  new allocs/op  delta
Upsert1_Cockroach-8        1.09k ± 0%     1.17k ± 0%  +7.40%  (p=0.008 n=5+5)
Upsert10_Cockroach-8       1.60k ± 0%     1.69k ± 0%  +5.58%  (p=0.008 n=5+5)
Upsert100_Cockroach-8      7.05k ± 0%     7.32k ± 0%  +3.87%  (p=0.008 n=5+5)
Upsert1000_Cockroach-8     60.9k ± 0%     63.0k ± 0%  +3.39%  (p=0.008 n=5+5)

The benchmarks will get better once I do the "all Puts" optimization, which is
coming next.

Closes cockroachdb#1962.
danhhz added a commit to danhhz/cockroach that referenced this issue May 11, 2016
@danhhz
sql: ON CONFLICT DO UPDATE form of upsert
5d70ec7 
name                    old time/op    new time/op    delta
Upsert1_Cockroach-8       1.15ms ± 5%    1.18ms ± 1%    ~     (p=0.151 n=5+5)
Upsert10_Cockroach-8      1.71ms ± 1%    1.75ms ± 1%  +2.26%  (p=0.008 n=5+5)
Upsert100_Cockroach-8     7.31ms ± 1%    7.41ms ± 2%    ~     (p=0.056 n=5+5)
Upsert1000_Cockroach-8    74.2ms ± 4%    74.2ms ± 3%    ~     (p=1.000 n=5+5)

name                    old alloc/op   new alloc/op   delta
Upsert1_Cockroach-8       82.4kB ± 0%    86.7kB ± 0%  +5.11%  (p=0.008 n=5+5)
Upsert10_Cockroach-8       164kB ± 0%     168kB ± 0%  +2.45%  (p=0.008 n=5+5)
Upsert100_Cockroach-8     1.05MB ± 0%    1.06MB ± 0%  +0.89%  (p=0.008 n=5+5)
Upsert1000_Cockroach-8    9.19MB ± 0%    9.27MB ± 0%  +0.90%  (p=0.008 n=5+5)

name                    old allocs/op  new allocs/op  delta
Upsert1_Cockroach-8        1.09k ± 0%     1.17k ± 0%  +7.40%  (p=0.008 n=5+5)
Upsert10_Cockroach-8       1.60k ± 0%     1.69k ± 0%  +5.58%  (p=0.008 n=5+5)
Upsert100_Cockroach-8      7.05k ± 0%     7.32k ± 0%  +3.87%  (p=0.008 n=5+5)
Upsert1000_Cockroach-8     60.9k ± 0%     63.0k ± 0%  +3.39%  (p=0.008 n=5+5)

The benchmarks will get better once I do the "all Puts" optimization, which is
coming next.

Closes cockroachdb#1962.
@blathers-crl blathers-crl bot mentioned this issue Jun 10, 2020
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@danhhz danhhz

Labels
C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) E-intermediate Intermediate complexity, needs a contributor with 3-6 months of past contribution experience.
Projects
None yet
Milestone
Q2
Development

No branches or pull requests
7 participants
@fire @danhhz @archiecobbs @tamird @arypurnomoz @tbg @petermattis