Re: Default ordering option - Mailing list pgsql-general
| From | Cyril Champier |
|---|---|
| Subject | Re: Default ordering option |
| Date | |
| Msg-id | CAJaA8VcTtLQMXXABzPN6yJdAVsSOkHkmaivQVZ_iGyPoOV48tQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Default ordering option (Ian Barwick <ian.barwick@2ndquadrant.com>) |
| Responses |
Re: Default ordering option
(Adrian Klaver <adrian.klaver@aklaver.com>)
|
| List | pgsql-general |
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)
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"
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.barwick@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
pgsql-general by date: