Class: PgSqlCaller::BulkUpdate

Inherits:
Object
  • Object
show all
Defined in:
lib/pg_sql_caller/bulk_update.rb

Overview

Bulk partial-update of existing rows keyed by one or more columns, via ‘UPDATE … FROM unnest(…)`:

PgSqlCaller::BulkUpdate.call(Employee, [
  { id: 1, name: 'John', department_id: 10 },
  { id: 2, name: 'Jane', department_id: 20 }
])

Match on a composite key (or any custom set of uniqueness columns) by passing ‘unique_by` an array instead of a single column:

PgSqlCaller::BulkUpdate.call(Employee, attrs_list, unique_by: %i[department_id name])

Chosen over ‘upsert_all`: PostgreSQL NOT NULL-checks the candidate INSERT tuple of `INSERT … ON CONFLICT DO UPDATE` before conflict arbitration, so upsert rejects partial payloads that omit the table’s other NOT NULL columns. This join only ever touches the listed columns of rows that already exist.

Preferred over N separate ‘update_all` calls wrapped in a transaction: a transaction makes those writes atomic but does nothing to batch them — it is still N statements, N client<->server round-trips, and N parse/plan cycles. This is a single statement and a single round-trip; PostgreSQL applies the whole set-based update server-side. Round-trip latency dominates the N-call approach as the row count grows, so this stays roughly flat while the loop scales linearly (see spec/pg_sql_caller/bulk_update_spec.rb benchmark).

Each column is sent as one typed PostgreSQL array; ‘unnest` zips the arrays back into rows. Values are bound through ActiveRecord’s sanitizer (PgSqlCaller::Model) and never interpolated; the only identifiers placed into the SQL are restricted to the model’s own columns, so the statement is injection-safe by construction.

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(model_class, attrs_list, unique_by: :id) ⇒ BulkUpdate

Returns a new instance of BulkUpdate.

Parameters:

  • model_class (Class<ActiveRecord::Base>) —

    the model whose table is updated

  • attrs_list (Array<Hash>) —

    one hash per row; each MUST include every ‘unique_by` column, and all hashes MUST share the same keys

  • unique_by (Symbol, Array<Symbol>) (defaults to: :id) —

    the match column(s) — a single column, or all parts of a composite key (default :id)



57
58
59
60
61
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 57

def initialize(model_class, attrs_list, unique_by: :id)
  @model_class = model_class
  @attrs_list = attrs_list
  @unique_by = Array(unique_by)
end

Instance Attribute Details

#attrs_list ⇒ Object (readonly)

Returns the value of attribute attrs_list.



50
51
52
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 50

def attrs_list
  @attrs_list
end

#model_class ⇒ Object (readonly)

Returns the value of attribute model_class.



50
51
52
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 50

def model_class
  @model_class
end

#unique_by ⇒ Object (readonly)

Returns the value of attribute unique_by.



50
51
52
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 50

def unique_by
  @unique_by
end

Class Method Details

.call(model_class, attrs_list, unique_by: :id) ⇒ Integer

Build and run a bulk update in one call.

Parameters:

  • model_class (Class<ActiveRecord::Base>) —

    the model whose table is updated

  • attrs_list (Array<Hash>) —

    one hash per row; each MUST include every ‘unique_by` column, and all hashes MUST share the same keys

  • unique_by (Symbol, Array<Symbol>) (defaults to: :id) —

    the match column(s) — a single column, or all parts of a composite key (default :id)

Returns:

  • (Integer) —

    the number of rows affected



46
47
48
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 46

def self.call(model_class, attrs_list, unique_by: :id)
  new(model_class, attrs_list, unique_by: unique_by).call
end

Instance Method Details

#call ⇒ Integer

Execute the bulk update as a single ‘UPDATE … FROM unnest(…)` statement.

Returns:

  • (Integer) —

    the number of rows affected (0 when attrs_list is empty)

Raises:

  • (ArgumentError) —

    if a row omits a ‘unique_by` column, or names a column that does not exist on the model



68
69
70
71
72
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 68

def call
  return 0 if attrs_list.empty?

  sql_caller.execute(sql, *bindings).cmd_tuples
end