there is a repeated record and one record is gone from the list/page

[laptopmutia]

so I have this weird bug on my app there is a record that is repeated and one record is gone from the list idk what makes it so, but refactoring some line on my code fix it, and it only occurs on production

here is my code

  def freights_sold
    @freights = Freight.joins(:consignee).where(sales_agent_id: current_user.id)
    @freights = @freights.where(consignee_id: params[:consignee_id]) if params[:consignee_id].present?
    @freights = @freights.where(sales_agent_id: params[:sales_id]) if params[:sales_id].present?
    @freights = @freights.where(type: params[:type]) if params[:type].present?
    if params[:tanggal_dari].present? && params[:tanggal_sampai].present?
      tanggal_dari = params[:tanggal_dari].to_date
      tanggal_sampai = params[:tanggal_sampai].to_date
    else
      tanggal_dari = DateTime.current.beginning_of_year
      tanggal_sampai = DateTime.current
    end
    @freights = @freights.where(order_date: tanggal_dari.beginning_of_day..tanggal_sampai.end_of_day)
    @freights = Freight.joins(:consignee).where("hbl_number ILIKE ?", "%#{params[:hbl_number]}%") if params[:hbl_number].present?
    @freights = Freight.joins(:consignee).where("number ILIKE ?", "%#{params[:number]}%") if params[:number].present?
    @total_jobs = 0
    @freights.each do |f|
      @total_jobs += f.profit_loss
    end
    @pagy, @freights = pagy @freights.reorder(sort_column => sort_direction), items: params.fetch(:count, 10)
  end

  def sort_column
    %w{ order_date number companies.nama hbl_number }.include?(params[:sort]) ? params[:sort] : "order_date"
  end

  def sort_direction
    %w{ asc desc }.include?(params[:direction]) ? params[:direction] : "desc"
  end

changing the :count params from the front end (via parameter) fix the issue though
and also changing the parameter for the sort column and sort direction also fixing the bug

[benkoshy]

@laptopmutia it is very difficult to debug with the above code.

I need a minimal example - pls see the pagy rails template in order to reproduce the bug - otherwise we will expend a lot of energywithout getting anywhere in particular. The problem might not be in pagy - it might be somewhere else. The reproducible example will allow you to find the fault yourself, or would otherwise provide very precise answers so that a bug fix can be reproduced.

[laptopmutia]

yeah I aware of that, trying to reproduce it but still can't, so I hesitant about it being a bug, so I make a discussions page instead an issue, just wondering maybe someone have encountered same issue.

thanks for that pagy rails template, could I use postgresql for that though?

[benkoshy]

thanks for that pagy rails template, could I use postgresql for that though?

You can use whatever DB you like including postgresql. just make sure the 'pg' gem is in the inline gem file and it should just work.

[bradbajuz]

@laptopmutia I had a similar issue to this and it came down to how Postgres was running my query. The issue didn't happen in Sqlite. Did you try .order(:id) first to make your queries deterministic?

This is from the Postgres Doc

OFFSET says to skip that many rows before beginning to return rows. OFFSET 0 is the same as omitting the OFFSET clause, and LIMIT NULL is the same as omitting the LIMIT clause. If both OFFSET and LIMIT appear, then OFFSET rows are skipped before starting to count the LIMIT rows that are returned.

When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows. You might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? The ordering is unknown, unless you specified ORDER BY.

The query optimizer takes LIMIT into account when generating query plans, so you are very likely to get different plans (yielding different row orders) depending on what you give for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.

[laptopmutia]

hi been a while since I stumbled upon this, currently I ignore this problem and raising my perpage number into 25 from 10

I not yet tried that .order(:id) and I think that is not gonna work if I use UUID

[ddnexus]

@joyoy96 Some golden answers to this problem!

#306 (comment)
#306 (comment)

[laptopmutia]

thanks for the mention, looking at it

[benkoshy]

Discussion Solved with

https://ddnexus.github.io/pagy/docs/troubleshooting/#records-may-randomly-repeat-in-different-pages-or-be-missing