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])

Narrow which rows are eligible with condition, and rewrite how individual columns are assigned with set_override — both are raw SQL fragments, interpolated verbatim:

PgSqlCaller::BulkUpdate.call(Order, [{ id: 1, status: 'processing' }],
                           condition: "t.status = 'pending'")

PgSqlCaller::BulkUpdate.call(
Order,
[{ id: 1, status: 'delivered', delivered_at: now }],
set_override: {
  status: "CASE WHEN t.status = 'pending' THEN v.status ELSE t.status END"
}
)

Qualify every column reference in those fragments with t. (the target table) or v. (the unnest source): both aliases expose the same column names, so an unqualified reference raises column reference "..." is ambiguous.

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. The exception is condition and the set_override expressions: those are raw SQL supplied by the caller and interpolated verbatim, so they must never be built from untrusted input.

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(model_class, attrs_list, unique_by: :id, returning: nil, condition: nil, set_override: {}) ⇒ 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, String, Array<Symbol>, Array<String>) (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

  • condition (String, nil) (defaults to: nil) —

    an extra raw-SQL predicate ANDed onto the match clause, so only rows also satisfying it are updated; nil (default) adds nothing

  • set_override (Hash{Symbol, String => String}) (defaults to: {}) —

    raw-SQL expressions replacing the default v.col assignment of the named columns (default {})



97
98
99
100
101
102
103
104
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 97

def initialize(model_class, attrs_list, unique_by: :id, returning: nil, condition: nil, set_override: {})
  @model_class = model_class
  @attrs_list = attrs_list
  @unique_by = Array(unique_by).map(&:to_sym)
  @returning = returning.nil? ? nil : Array(returning)
  @condition = condition
  @set_override = set_override.to_h.transform_keys(&:to_sym)
end

Instance Attribute Details

#attrs_list ⇒ Object (readonly)

Returns the value of attribute attrs_list.



84
85
86
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 84

def attrs_list
  @attrs_list
end

#condition ⇒ Object (readonly)

Returns the value of attribute condition.



84
85
86
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 84

def condition
  @condition
end

#model_class ⇒ Object (readonly)

Returns the value of attribute model_class.



84
85
86
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 84

def model_class
  @model_class
end

#returning ⇒ Object (readonly)

Returns the value of attribute returning.



84
85
86
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 84

def returning
  @returning
end

#set_override ⇒ Object (readonly)

Returns the value of attribute set_override.



84
85
86
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 84

def set_override
  @set_override
end

#unique_by ⇒ Object (readonly)

Returns the value of attribute unique_by.



84
85
86
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 84

def unique_by
  @unique_by
end

Class Method Details

.call(model_class, attrs_list, unique_by: :id, returning: nil, condition: nil, set_override: {}) ⇒ 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, String, Array<Symbol>, Array<String>) (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

  • condition (String, nil) (defaults to: nil) —

    an extra raw-SQL predicate ANDed onto the match clause, so only rows also satisfying it are updated; nil (default) adds nothing

  • set_override (Hash{Symbol, String => String}) (defaults to: {}) —

    raw-SQL expressions replacing the default v.col assignment of the named columns (default {})

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



73
74
75
76
77
78
79
80
81
82
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 73

def self.call(model_class, attrs_list, unique_by: :id, returning: nil, condition: nil, set_override: {})
  new(
    model_class,
    attrs_list,
    unique_by: unique_by,
    returning: returning,
    condition: condition,
    set_override: set_override
  ).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, returning is empty or names an unknown column, condition is blank, or set_override names an unknown or unique_by column or carries a blank expression



115
116
117
118
119
120
121
122
123
124
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 115

def call
  validate!
  return empty_result if attrs_list.empty?

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

#sql ⇒ String

The full UPDATE ... FROM unnest(...) statement, with one ? placeholder per column for the value arrays, plus a RETURNING clause when returning was given. Public so the generated SQL can be inspected and asserted on directly: it runs the very same validations as #call, so an input #call would reject never yields a statement here either. An empty attrs_list has no statement at all (#call short-circuits to #empty_result instead of building one), so it raises.

Returns:

  • (String)

Raises:

  • (ArgumentError) —

    on any input #call rejects (see #validate! and #validate_columns!), or when attrs_list is empty



136
137
138
139
140
141
 
# File 'lib/pg_sql_caller/bulk_update.rb', line 136

def sql
  validate!
  raise ArgumentError, 'attrs_list must not be empty to build SQL' if attrs_list.empty?

  build_sql
end