search

cockroachdb / cockroach

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.
https://www.cockroachlabs.com
Other
30.11k stars 3.81k forks source link

sql: make it possible to update a part of an array database-side #28790

Open knz opened 6 years ago

knz commented 6 years ago

This is a feature implemented by PostgreSQL but not by CockroachDB and it came up recently on Gitter.

The problem is the following: suppose there is a an array with 1000 elements in a column of a table. Then the client wants to update one of these elements, but not the rest.

Currently in CockroachDB the client would need to pull all the 1000 things from the server, over the client/server network connection, then modify one of them client-side, then send the entire 1000 things again over the client/server connection to be processed in an UPDATE.

This gives, naturally, terrible performance.

The proper way to do this is to support server-side element-wise mutations.

For arrays the Postgres dialect supports e.g. UPDATE SET foo[10] = 123 if foo is an array column.

Note: this problem doesn't exist as much with JSON because there is a json_set().

CockroachDB should consider implementing the UPDATE syntax or at least provide some array_set() function.

Jira issue: CRDB-4893

github-actions[bot] commented 3 years ago

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 5 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!

knz commented 3 years ago

still current in 21.1

github-actions[bot] commented 1 year ago

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!