Re: Default ordering option

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?


> 
> Thanks,
> Cyril
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com