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, returning: nil) ⇒ 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)

  • returning (Symbol, Array<Symbol>, nil) (defaults to: nil) —

    column(s) to read back from each updated row via SQL ‘RETURNING`; nil (default) keeps the row-count behavior



62
63
64
65
66
67
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 62

def initialize(model_class, attrs_list, unique_by: :id, returning: nil)
  @model_class = model_class
  @attrs_list = attrs_list
  @unique_by = Array(unique_by)
  @returning = returning.nil? ? nil : Array(returning)
end

Instance Attribute Details

#attrs_list ⇒ Object (readonly)

Returns the value of attribute attrs_list.



53
54
55
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 53

def attrs_list
  @attrs_list
end

#model_class ⇒ Object (readonly)

Returns the value of attribute model_class.



53
54
55
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 53

def model_class
  @model_class
end

#returning ⇒ Object (readonly)

Returns the value of attribute returning.



53
54
55
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 53

def returning
  @returning
end

#unique_by ⇒ Object (readonly)

Returns the value of attribute unique_by.



53
54
55
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 53

def unique_by
  @unique_by
end

Class Method Details

.call(model_class, attrs_list, unique_by: :id, returning: nil) ⇒ Integer, Array<Hash{Symbol => Object}>

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)

  • returning (Symbol, Array<Symbol>, nil) (defaults to: nil) —

    column(s) to read back from each updated row via SQL ‘RETURNING`; nil (default) keeps the row-count behavior

Returns:

  • (Integer, Array<Hash{Symbol => Object}>) —

    the number of rows affected, or —when returning is given — the updated rows as type-cast, Symbol-keyed hashes



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

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

Instance Method Details

#call ⇒ Integer, Array<Hash{Symbol => Object}>

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

Returns:

  • (Integer, Array<Hash{Symbol => Object}>) —

    without returning, the number of rows affected (0 when attrs_list is empty); with returning, the updated rows as type-cast, Symbol-keyed hashes ([] when attrs_list is empty)

Raises:

  • (ArgumentError) —

    if a row omits a ‘unique_by` column, names a column that does not exist on the model, or returning is empty or names an unknown column



76
77
78
79
80
81
82
83
84
85
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 76

def call
  validate_returning! unless returning.nil?
  return empty_result if attrs_list.empty?

  if returning.nil?
    sql_caller.execute(sql, *bindings).cmd_tuples
  else
    sql_caller.select_all_serialized(sql, *bindings)
  end
end