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(dot)klaver(at)aklaver(dot)com