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.barw...@2ndquadrant.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 >
