Re: Default ordering option

Thanks for your answers.
Unfortunately the update trick only seems to work under certain conditions.

I do this to shuffle my patients table:
UPDATE "patients"
SET "updated_at" = NOW()
WHERE "patients"."id" = (SELECT "patients"."id" FROM "patients" ORDER BY
random() LIMIT 1)

Then indeed, this query returns different order:
SELECT *
FROM "patients"

But this one (because it use an index?) always returns values in the same
order:
SELECT "id"
FROM "patients"

And for the other suggestion, I cannot blindly add 'ORDER BY random()' to
every select,
because of the incompatibility with distinct and union, and the way we use
our orm.

On Wed, Jul 24, 2019 at 3:54 AM Ian Barwick <ian(dot)barwick(at)2ndquadrant(dot)com>
wrote:

> On 7/24/19 2:23 AM, Adrian Klaver wrote:
> > On 7/23/19 8:43 AM, Cyril Champier wrote:
> >> Hi,
> >>
> >> In this documentation <
> https://www.postgresql.org/docs/9.1/queries-order.html>, it is said:
> >>
> >> If sorting is not chosen, the rows will be returned in an
> >> unspecified order. The actual order in that case will depend on the
> >> scan and join plan types and the order on disk, but it must not be
> >> relied on.
> >>
> >>
> >> I would like to know if there is any way to change that to have a
> "real" random behaviour.
> >>
> >> My use case:
> >> At Doctolib, we do a lot of automatic tests.
> >> Sometimes, people forgot to order their queries. Most of the time,
> there is no real problem on production. Let say, we display a user list
> without order.
> >> When a developer writes a test for this feature, he will create 2 users
> A and B, then assert that they are displayed "[A, B]".
> >> 99% of the time the test will be ok, but sometimes, the displayed list
> will be "[B,A]", and the test will fail.
> >>
> >> One solution could be to ensure random order with an even distribution,
> so that such failing test would be detected quicker.
> >>
> >> Is that possible? Maybe with a plugin?
> >
> > Not that I know of.
> >
> > A possible solution given below:
> >
> > test_(postgres)> insert into t1 values (1, 'dog'), (2, 'cat'), (3,
> 'fish');
> > INSERT 0 3
> >
> > test_(postgres)> select * from t1 ;
> > a | b
> > ---+------
> > 1 | dog
> > 2 | cat
> > 3 | fish
> > (3 rows)
> >
> > test_(postgres)> update t1 set b = 'dogfish' where a =1;
> > UPDATE 1
> >
> > test_(postgres)> select * from t1 ;
> > a | b
> > ---+---------
> > 2 | cat
> > 3 | fish
> > 1 | dogfish
> > (3 rows)
> >
> > An UPDATE reorders the rows. Maybe throw an UPDATE into the test after
> creating the users to force an 'out of order' result?
>
> An UPDATE without changing any values should have the same effect, e.g. :
>
> UPDATE t1 SET b = b WHERE a = 1;
>
> Something like this
>
> WITH x AS (SELECT * FROM t1 ORDER BY a DESC)
> UPDATE t1 t
> SET a = t.a
> FROM x
> WHERE t.a = x.a
>
> would shuffle the rows into reverse order, which might be enough to catch
> out any missing ORDER BY (this assumes nothing else will touch the table
> and reorder it before the test is run).
>
> You could also order by RANDOM() but there would be a chance the rows would
> end up in sequential order.
>
> Regards
>
>
> Ian Barwick
>
> --
> Ian Barwick https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>