Rails - Aug 2009 - Postgres defaults to ID DESC, MySQL defaults to ID ASC

I just switched to Postgres and some of my tests are failing. It seems
that Postgres defaults by ordering data by ID DESC, whereas MySQL
defaults to ordering by ID ASC.

What is the best practice for handling this? Is there a setting in
Postgres you can change? Should I be explicitly specifying the order of
my data in my queries?

In various places throughout my application I am doing things like:

User.first

This obviously is not good with the order of the data is not consistent.
I also feel like the first User should return the first user, not the
last one created.

What do you think?
-- 
Posted via http://www.ruby-forum.com/.

On Tue, Aug 18, 2009 at 5:01 PM, Ben
Johnson<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
>
> I just switched to Postgres and some of my tests are failing. It seems
> that Postgres defaults by ordering data by ID DESC, whereas MySQL
> defaults to ordering by ID ASC.
>
> What is the best practice for handling this? Is there a setting in
> Postgres you can change? Should I be explicitly specifying the order of
> my data in my queries?
>
> In various places throughout my application I am doing things like:
>
> User.first
>
> This obviously is not good with the order of the data is not consistent.
> I also feel like the first User should return the first user, not the
> last one created.
The Rails tests use explicit ordering to work around this discrepancy.

User.first without an ordering is technically undefined. It could be any row.

Since you''re usually doing account.users.first, you could :order =>
''id asc'' on the has_many.

jeremy

Ben Johnson wrote:
> I just switched to Postgres and some of my tests are failing. It seems
> that Postgres defaults by ordering data by ID DESC, whereas MySQL
> defaults to ordering by ID ASC.
As Jeremy correctly pointed out, without an ORDER clause, your records 
may be returned in any order.

[...]
> 
> In various places throughout my application I am doing things like:
> 
> User.first
> 
> This obviously is not good with the order of the data is not consistent.
> I also feel like the first User should return the first user, not the
> last one created.
There is no guarantee of that -- without the ORDER clause, the database 
is free to return the first user created, the last user created, or one 
somewhere in the middle.  User.first just returns whichever user the DB 
decided to retrieve first.  If you need a specific ordering, you need an 
order clause.
> 
> What do you think?
I think you don''t quite understand how SQL databases retrieve records.

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

Marnen Laibow-Koser wrote:
>> What do you think?
> 
> I think you don''t quite understand how SQL databases retrieve
records.
Think of it this way: select * from users; returns a record "set." As 
in, "unordered set" as apposed to a record "array." An id
field is just
another column that happens to be used to identify rows. The only 
requirement (assuming id is used as a primary key) is that each value is 
unique. The sequence of those values is undefined. Therefore, the order 
of the record set is undefined.

An "order by" clause is applied to the resulting set before the set is
returned, which essentially turns it into an "ordered set/array." This
final result is stored in an Array object by ActiveRecord (another 
ordered storage object) so can be depended upon to be kept in order.
-- 
Posted via http://www.ruby-forum.com/.

(My point of view) Order the result by ID is also a bad idea, if you
switch to another ID schema (for example UUID, descending sequence,
etc.) you must rewrite a lot of code. The ID is something the
application must not "know", order for example by name, by a
created_at field (and that have more sense if you want "the last
created user").

Regards.

Franco Catena.

On Aug 18, 9:01 pm, Ben Johnson
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
> I just switched to Postgres and some of my tests are failing. It seems
> that Postgres defaults by ordering data by ID DESC, whereas MySQL
> defaults to ordering by ID ASC.
>
> What is the best practice for handling this? Is there a setting in
> Postgres you can change? Should I be explicitly specifying the order of
> my data in my queries?
>
> In various places throughout my application I am doing things like:
>
> User.first
>
> This obviously is not good with the order of the data is not consistent.
> I also feel like the first User should return the first user, not the
> last one created.
>
> What do you think?
> --
> Posted viahttp://www.ruby-forum.com/.