Re: Default ordering option

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