Re: Default ordering option - Mailing list pgsql-general

From Cyril Champier
Subject Re: Default ordering option
Date
Msg-id CAJaA8VdSpsq0s5PgRW9CACTx3jdQ2ie8txcFqOFDG8MLmsx2Kg@mail.gmail.com
Whole thread Raw
In response to Re: Default ordering option  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: Default ordering option
List pgsql-general
Indeed, you are right, I do my test in pure sql and via ruby ActiveRecord, and I must had been confused,
the behaviour is correct in sql, it must have been a cache thing in ActiveRecord that prevented the reordering.
But meanwhile, I tested on our whole CI, and it took twice the normal time with updates to shuffle DB :(

For the union, I speak about production code like this:
"select count(*) from (#{directory_doctors_query_sql} union all #{profiles_query_sql}) as doctors"
In the to_sql, we cannot detect that we will be injected into a union.
So I cannot blindly add the random in the to_sql method.







On Wed, Jul 24, 2019 at 4:48 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 7/24/19 1:45 AM, Cyril Champier wrote:
> 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"

Hmm, I don't see that:

test=# \d t1
                       Table "public.t1"
  Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
  a      | integer           |           | not null |
  b      | character varying |           |          |
Indexes:
     "t1_pkey" PRIMARY KEY, btree (a)


test=# select * from t1;
  a |    b
---+---------
  2 | cat
  3 | fish
  1 | dogfish
(3 rows)

test=# select a from t1;
  a
---
  2
  3
  1
(3 rows)

Are you sure there is nothing going on between the first and second
queries e.g. ROLLBACK?

>
>
>
> 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.
>
Are you talking about the production or test queries above?



--
Adrian Klaver
adrian.klaver@aklaver.com

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: postgres 9.5 DB corruption
Next
From: Adrian Klaver
Date:
Subject: Re: Default ordering option