Increment counter and rails "first" using postgreSQL strange behavior

Question

When I increment some integer column using increment_counter and passing some record id and then try to get the first record using Model.first, this return the record id plus 1.

Something like this:

Model.increment_counter :field, id
Model.first

It returns not the

Model.find(1)

but

Model.find(id+1)

Is that some particular issue of postgreSQL?

score 6 · Accepted Answer · answered Jun 29 '11 at 19:07

6

Model.first will use the default sorting of your database (which is not necessarily an id).

Try this instead:

Model.order("id").first

answered Jun 29 '11 at 19:07

Dylan Markow

123,080
26
284
201

Ok, this is not causing me any problem. I just want to understand why the default sorting of postgreSQL is not an id. Which is the default postgre sorting? – Vinícius Jun 29 '11 at 19:19
6

Per PostgreSQL's documentation, "If sorting is not chosen, the rows will be returned in an unspecified order". I think in practice they will be returned in the order they were created (not by ID, though if you never alter the IDs, it will usually be the same). So any time you need to make sure they're sorted by ID, you must call `order("id")`. – Dylan Markow Jun 29 '11 at 19:51
1

It actually is the order in which live rows appear on the disk. The fill factor can have a significant impact on that, too. – Denis de Bernardy Jun 29 '11 at 20:38
@Denis, if it's a sequential scan you can't even assume it's the order on the disk as per the 8.3 major version release notes: "Concurrent large sequential scans can now share disk reads. This is accomplished by starting the new sequential scan in the middle of the table (where another sequential scan is already in-progress) and wrapping around to the beginning to finish. This can affect the order of returned rows in a query that does not specify ORDER BY. The synchronize_seqscans configuration parameter can be used to disable this if necessary." – Matthew Wood Jul 02 '11 at 02:20
@Matthew: lol! Thanks for the note. I had completely missed that change. :D – Denis de Bernardy Jul 02 '11 at 02:29
1

At the very least, the Active Record docs should reflect that "if you are using postgresql and you wish to call the absolute first record created, you must use `Model.order("id DESC").first` to obtain the absolute first record in that table." or something to that effect. We're all taught at an early age to use `Model.first` or `Model.last` in every railscast, whatever... All of that code needs to be called out for lying :-). – pjammer Aug 08 '11 at 16:40
ugh i can't edit above, it's like Dylan said above. `Model.order("id")` I'm having one of those lifetimes... – pjammer Aug 08 '11 at 16:49

Alexis · Answer 2 · 2014-04-07T08:06:47.457

You can do some monkey patching to ActiveRecord,

#lib/postgresql_extras.rb

module ActiveRecord
  class Base
    def self.first_by_id
      order(:id).first
    end
    def self.all_by_id
      order(:id)
    end
  end
end

and require this in some initializer

#config/initializer/extensions.rb

require "postgresql_extras"

don't call this ones first and all cause it will generate errors on other querys, for example User.order(:email).limit(1) it will be different from User.order(:email).first in this case, cause it will reorder by id the items, I didn't find other methods with problems in posgresql yet and i try to fix it by change the tables pkey, but not luck there

Increment counter and rails "first" using postgreSQL strange behavior

2 Answers2

Linked