Sequel implicitly adds ordering in dataset.paged_each

[ametalon]

When I use method SomeModel.dataset.paged_each Sequel adds ORDER BY clause if dataset is associated with a model with a primary key.

I use this method to declare a cursor in postgres and in some cases I do not want ordering.

Let's say I have a table items and model for it:

DB.create_table(:items, temp: true) do  
  primary_key :id  
end  
  
class Item < Sequel::Model  
end

This code doesn't add ordering and works as expected by me:

item_ds = DB[:items]  
item_ds.paged_each {}  

resulting in a query:

DECLARE "sequel_cursor" NO SCROLL CURSOR WITHOUT HOLD FOR 
SELECT * FROM "items"

This code implicitly adds ordering:

item_model_ds = Item.dataset  
item_model_ds.paged_each {}  

resulting in a query:

DECLARE "sequel_cursor" NO SCROLL CURSOR WITHOUT HOLD FOR 
SELECT * FROM "items" ORDER BY "id"

I'm not sure if you'd consider it a bug, but it was an unexpected behavior for me. I understand that ordering is needed for pagination strategies in Sequel::Dataset#paged_each. I'm just not sure that adding implicit ordering is Sequel's responsibility.

For postgres model dataset #paged_each first dispatches to
Sequel::Model::DatasetMethods#paged_each where ORDER BY is added and only then it goes up the inheritance chain to postgres specific implementation Sequel::Postgres::Dataset#paged_each.

Addition of ordering can be bypassed by wrapping model dataset in "bare" dataset:

item_model_ds = Item.dataset  
DB[item_model_ds].paged_each {}

resulting in a query:

DECLARE "sequel_cursor" NO SCROLL CURSOR WITHOUT HOLD FOR 
SELECT * FROM (SELECT * FROM "items") AS "t1"

Postgres optimizes away this wrapping so it's an acceptable solution for me.

Here is a complete example:

# frozen_string_literal: true  
  
require "bundler/inline"  
  
gemfile do  
  source "https://rubygems.org"  
  gem "logger"  
  gem "bigdecimal"  
  gem "pg"  
  gem "sequel", "~> 5.90.0"  
  gem "sequel_pg", require: "sequel"  
end  
  
require "logger"  
require "sequel"  
  
DB = Sequel.postgres(  
  host: "localhost",  
  port: 5444,  
  password: "postgres",  
  user: "postgres"  
)  
Sequel::Model.db = DB  
DB.create_table(:items, temp: true) do  
  primary_key :id  
end  
class Item < Sequel::Model  
end  
  
puts "--- versions ---"  
puts(ruby: RUBY_VERSION)  
puts(sequel: Sequel::VERSION)  
puts(db: DB.get(Sequel.function(:version)))  
  
puts "--- datasets ---"  
item_ds = DB[:items]  
item_model_ds = Item.dataset  
puts(item_ds:)  
puts(item_model_ds:)  
  
logger = Logger.new($stdout, formatter: proc { |_severity, _time, _progname, msg| puts msg })  
DB.logger = logger  
  
puts "--- cursors ---"  
puts "no ordering"  
item_ds.paged_each {}  
puts "ordering by pk added by Sequel"  
item_model_ds.paged_each {}  
puts "workaround to avoid ordering"  
DB[item_model_ds].paged_each {}

and its output:

--- versions ---
{ruby: "3.4.1"}
{sequel: "5.90.0"}
{db: "PostgreSQL 15.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 12.2.1_git20220924-r4) 12.2.1 20220924, 64-bit"}

--- datasets ---
{item_ds: #<Sequel::Postgres::Dataset: "SELECT * FROM \"items\"">}
{item_model_ds: #<Sequel::Dataset::_Subclass: "SELECT * FROM \"items\"">}

--- cursors ---

no ordering
(0.000183s) BEGIN
(0.000415s) DECLARE "sequel_cursor" NO SCROLL CURSOR WITHOUT HOLD FOR SELECT * FROM "items"
(0.000128s) FETCH FORWARD 1000 FROM "sequel_cursor"
(0.000085s) CLOSE "sequel_cursor"
(0.000081s) COMMIT

ordering by pk added by Sequel
(0.000084s) BEGIN
(0.000380s) DECLARE "sequel_cursor" NO SCROLL CURSOR WITHOUT HOLD FOR SELECT * FROM "items" ORDER BY "id"
(0.000120s) FETCH FORWARD 1000 FROM "sequel_cursor"
(0.000098s) CLOSE "sequel_cursor"
(0.000078s) COMMIT

workaround to avoid ordering
(0.000081s) BEGIN
(0.000179s) DECLARE "sequel_cursor" NO SCROLL CURSOR WITHOUT HOLD FOR SELECT * FROM (SELECT * FROM "items") AS "t1"
(0.000116s) FETCH FORWARD 1000 FROM "sequel_cursor"
(0.000084s) CLOSE "sequel_cursor"
(0.000077s) COMMIT

[jeremyevans]

The behavior is currently expected. Without an order, the default limit/offset paged_each strategy could result in the same row being yielded multiple times and missing rows, even when rows are not deleted/inserted. That issue isn't present on PostgreSQL, because it uses cursors for paged_each. I think it's reasonable to remove the implicit order on PostgreSQL.

[jeremyevans]

I did some more research, and the default paged_each raises if the dataset is not already ordered. So the reason paged_each sets an order if not already ordered for model datasets is to allow the method to work at all in such cases.

[ametalon]

Yes, it's completely acceptable to manually remove the implicit order, even more so not wanting ordering is not a common case. Usually even for pg cursors I need an order, so it's not a big deal to remove it in a minority of special cases.

Regarding default paged_each raising for unordered datasets I thought that it's a very clear way of telling a coder that they need to add the explicit order for given implementation of pagination. So not adding it implicitly won't break things silently.

However, I realize that changing current behavior is a breaking change and I'm not advocating for it, just wanted to clarify if the current behavior is intended.

Something just working (tm) in a majority of cases and being adaptable to the special ones is a good tradeoff for me. Thank you, sir.