diff options
Diffstat (limited to 'doc/development/database')
| -rw-r--r-- | doc/development/database/client_side_connection_pool.md | 2 | ||||
| -rw-r--r-- | doc/development/database/index.md | 1 | ||||
| -rw-r--r-- | doc/development/database/setting_multiple_values.md | 59 |
3 files changed, 30 insertions, 32 deletions
diff --git a/doc/development/database/client_side_connection_pool.md b/doc/development/database/client_side_connection_pool.md index 1a30d2d73a3..8316a75ac8d 100644 --- a/doc/development/database/client_side_connection_pool.md +++ b/doc/development/database/client_side_connection_pool.md @@ -43,7 +43,7 @@ hardcoded value (10). At this point, we need to investigate what is using more connections than we anticipated. To do that, we can use the `gitlab_ruby_threads_running_threads` metric. For example, [this -graph](https://thanos-query.ops.gitlab.net/graph?g0.range_input=1h&g0.max_source_resolution=0s&g0.expr=sum%20by%20(thread_name)%20(%20gitlab_ruby_threads_running_threads%7Buses_db_connection%3D%22yes%22%7D%20)&g0.tab=0) +graph](https://thanos.gitlab.net/graph?g0.range_input=1h&g0.max_source_resolution=0s&g0.expr=sum%20by%20(thread_name)%20(%20gitlab_ruby_threads_running_threads%7Buses_db_connection%3D%22yes%22%7D%20)&g0.tab=0) shows all running threads that connect to the database by their name. Threads labeled `puma worker` or `sidekiq_worker_thread` are the threads that define `Gitlab::Runtime.max_threads` so those are diff --git a/doc/development/database/index.md b/doc/development/database/index.md index 367ef455898..870ae1542bd 100644 --- a/doc/development/database/index.md +++ b/doc/development/database/index.md @@ -69,3 +69,4 @@ info: To determine the technical writer assigned to the Stage/Group associated w ## Miscellaneous - [Maintenance operations](maintenance_operations.md) +- [Update multiple database objects](setting_multiple_values.md) diff --git a/doc/development/database/setting_multiple_values.md b/doc/development/database/setting_multiple_values.md index 54870380047..0f23aae9f79 100644 --- a/doc/development/database/setting_multiple_values.md +++ b/doc/development/database/setting_multiple_values.md @@ -4,24 +4,22 @@ group: Database info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/engineering/ux/technical-writing/#assignments --- -# Setting Multiple Values +# Update multiple database objects > [Introduced](https://gitlab.com/gitlab-org/gitlab/-/issues/32921) in GitLab 13.5. -There's often a need to update multiple objects with new values for one -or more columns. One method of doing this is using `Relation#update_all`: +You can update multiple database objects with new values for one or more columns. +One method is to use `Relation#update_all`: ```ruby user.issues.open.update_all(due_date: 7.days.from_now) # (1) user.issues.update_all('relative_position = relative_position + 1') # (2) ``` -But what do you do if you cannot express the update as either a static value (1) -or as a calculation (2)? - -Thankfully we can use `UPDATE FROM` to express the need to update multiple rows -with distinct values in a single query. One can either use a temporary table, or -a Common Table Expression (CTE), and then use that as the source of the updates: +If you cannot express the update as either a static value (1) or as a calculation (2), +use `UPDATE FROM` to express the need to update multiple rows with distinct values +in a single query. Create a temporary table, or a Common Table Expression (CTE), +and use it as the source of the updates: ```sql with updates(obj_id, new_title, new_weight) as ( @@ -34,23 +32,22 @@ update issues where id = obj_id ``` -The bad news: there is no way to express this in ActiveRecord or even dropping -down to ARel. The `UpdateManager` does not support `update from`, so this -is not expressible. - -The good news: we supply an abstraction to help you generate these kinds of -updates, called `Gitlab::Database::BulkUpdate`. This constructs queries such as the -above, and uses binding parameters to avoid SQL injection. +You can't express this in ActiveRecord, or by dropping down to [Arel](https://api.rubyonrails.org/v6.1.0/classes/Arel.html), +because the `UpdateManager` does not support `update from`. However, we supply +an abstraction to help you generate these kinds of updates: `Gitlab::Database::BulkUpdate`. +This abstraction constructs queries like the previous example, and uses +binding parameters to avoid SQL injection. ## Usage -To use this, we need: +To use `Gitlab::Database::BulkUpdate`, we need: -- the list of columns to update -- a mapping from object/ID to the new values to set for that object -- a way to determine the table for each object +- The list of columns to update. +- A mapping from the object (or ID) to the new values to set for that object. +- A way to determine the table for each object. -For example, we can express the query above as: +For example, we can express the example query in a way that determines the +table by calling `object.class.table_name`: ```ruby issue_a = Issue.find(..) @@ -63,10 +60,7 @@ issue_b = Issue.find(..) }) ``` -Here the table can be determined automatically, from calling -`object.class.table_name`, so we don't need to provide anything. - -We can even pass heterogeneous sets of objects, if the updates all make sense +You can even pass heterogeneous sets of objects, if the updates all make sense for them: ```ruby @@ -82,8 +76,8 @@ merge_request = MergeRequest.find(..) }) ``` -If your objects do not return the correct model class (perhaps because they are -part of a union), then we need to specify this explicitly in a block: +If your objects do not return the correct model class, such as if they are part +of a union, then specify the model class explicitly in a block: ```ruby bazzes = params @@ -103,7 +97,10 @@ end ## Caveats -Note that this is a **very low level** tool, and operates on the raw column -values. Enumerations and state fields must be translated into their underlying -representations, for example, and nested associations are not supported. No -validations or hooks are called. +This tool is **very low level**, and operates directly on the raw column +values. You should consider these issues if you implement it: + +- Enumerations and state fields must be translated into their underlying + representations. +- Nested associations are not supported. +- No validations or hooks are called. |