Using paged_update with multiple FROM tables

[davekaro]

I have a table with millions of rows that I want to update one column from the value in another table. I tried this:

TableA
  .qualify
  .from(:table_a, DB[:table_b].as(:table_b))
  .where { table_b[:id] =~ table_a[:table_b_id] }
  .paged_update({column: Sequel[:table_b][:column]}, rows_per_page: 5_000)

But I got this error PG::AmbiguousColumn: ERROR: column reference "id" is ambiguous (Sequel::DatabaseError) LINE 1: SELECT "id" FROM "table_a", ...

I modified Sequel to always qualify the id in paged_operations (davekaro@94eb8e0) and it worked. Wondering if there is another approach, or if I should push a PR.

Thanks!

[jeremyevans]

I think always qualifying the primary key would be fine. To work around, you could also set your own order, as Sequel only orders automatically if not already ordered.

Another thing I've been considering is disabling the automatic order on PostgreSQL, where it isn't needed as a cursor is used by default (and for other adapters that use similar optimizations). Not sure whether this should be done by default or via an extension/plugin. Doing it by default is a minor backwards compatibility break, as some users may be expecting the implicit order.

[davekaro]

you could also set your own order, as Sequel only orders automatically if not already ordered

I tried this but couldn't get it to work. Looking at

sequel/lib/sequel/plugins/paged_operations.rb

Line 176 in 8df5d59

_force_primary_key_order.offset(rows_per_page || 1000)

I don't think you can set your own order when using paged_* methods.

Note the ORDER BY id doesn't need to be qualified since we are only selecting one id column.

[jeremyevans]

This was my mistake, I misread the paged_update as paged_each.