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&gt;, 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

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&gt;, 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

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"

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 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

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 8:22 AM, Cyril Champier wrote:

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 :(

Well there extra operations so that is to be expected. The question is
whether everything needs to be shuffled? It would seem you only need to
do that for those tests that are expecting a set order. I went back and
read your original post and am somewhat confused about that test. You said:

"Sometimes, people forgot to order their queries. Most of the time,
there is no real problem on production...."

If order is not an issue in the production code why test for it in the
test code?

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.

The below would not be affected by an order by in any case as the
count() would be the same:

"select count(*) from (#{directory_doctors_query_sql} union all
#{profiles_query_sql}) as doctors"

If you did want to use order by random() could you not just tack it on
the end?:

"... as doctors order by random()"

--
Adrian Klaver
adrian.klaver@aklaver.com

On 2019-07-23 17:43, Cyril Champier wrote:

In this documentation
<https://www.postgresql.org/docs/9.1/queries-order.html&gt;, 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.

It might be an interesting exercise to implement this as a post-parsing
hook.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

*Adrian*:

If order is not an issue in the production code why test for it in the
test code?

In many cases, it would not be a problem in tests if we had an unordered
array comparison helper.
But in other cases, it is a production issue.
In ruby ActiveRecord for exemple, you can do `Patient.find_by(last_name:
'champier')`,
which translates to `SELECT "patients".* FROM "patients" WHERE
"patients"."last_name" = 'champier' LIMIT 1`.
If last_name is not unique, the returned record will be random.

So yes, everything as to be randomized, because the sources are multiples
and the consequences can vary to a dramatic production bug, a failed CI 1%
of the time, or to a useless test assertion.

*Peter*:

It might be an interesting exercise to implement this as a post-parsing
hook.

I known nothing about that, but that sounds interesting, do you have any
documentation pointer to help me implement that?

On Wed, Jul 24, 2019 at 10:36 PM Peter Eisentraut <
peter.eisentraut@2ndquadrant.com> wrote:

On 2019-07-25 09:43, Cyril Champier wrote:

It might be an interesting exercise to implement this as a post-parsing
hook.

 
I known nothing about that, but that sounds interesting, do you have any
documentation pointer to help me implement that?

Look for post_parse_analyze_hook. Walk the parsed query tree, look for
queries without ordering clause and manufacture one.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Peter:
So I would need to create a pg extension encapsulating this hook callback?
If this is the case, it seems it will be much more complicated than
expected, and I wont be able to do it :(
But thanks for the suggestion anyway.

On Thu, Jul 25, 2019 at 12:21 PM Peter Eisentraut <
peter.eisentraut@2ndquadrant.com> wrote:

On 7/25/19 12:43 AM, Cyril Champier wrote:

*Adrian*:

If order is not an issue in the production code why test for it in the
test code?

In many cases, it would not be a problem in tests if we had an unordered
array comparison helper.
But in other cases, it is a production issue.
In ruby ActiveRecord for exemple, you can do `Patient.find_by(last_name:
'champier')`,
which translates to `SELECT  "patients".* FROM "patients" WHERE
"patients"."last_name" = 'champier' LIMIT 1`.
If last_name is not unique, the returned record will be random.

Are you really looking for a pseudo-random name?

If so would not(warning not a Ruby developer, so below is tentative):

Patient.where(["last_name = :last_name", {last_name:
"champier"}]).order('RANDOM()').first

work better?

If not why not use something that returns all possible matches?

So yes, everything as to be randomized, because the sources are
multiples and the consequences can vary to a dramatic production bug, a
failed CI 1% of the time, or to a useless test assertion.

One way I can think of doing this is write a script that walks through
your tables in the test db and does an UPDATE across the rows. It is
going to add time to your tests, but then I believe that is going to be
the case for anything you do. Or you could look at something I have
never tried, fuzzy testing. As a starting point:

https://www.guru99.com/fuzz-testing.html

Maybe other folks have suggestions on tools you could use for fuzzy
testing.

*Peter*:

It might be an interesting exercise to implement this as a post-parsing
hook.

I known nothing about that, but that sounds interesting, do you have any
documentation pointer to help me implement that?

On Wed, Jul 24, 2019 at 10:36 PM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com
<mailto:peter.eisentraut@2ndquadrant.com>> wrote:

On 2019-07-23 17:43, Cyril Champier wrote:

In this documentation
<https://www.postgresql.org/docs/9.1/queries-order.html&gt;, 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.

It might be an interesting exercise to implement this as a post-parsing
hook.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian:

Are you really looking for a pseudo-random name?

No, the code I pasted was an existing production bug: the last_name should
have been unique, so the selected patient would always be the same.
This should have been detected in tests, but since the order was "almost
always the same", our test was green 99% of the time, so we discarded it as
flaky.

Fuzzy testing could be an option, but this would go too far, as for Peter
extension suggestion.
We have huge existing codebase with more than 10K tests, and I do not want
to modify our whole testing strategy.

Meanwhile, I went for an ORM patch (ActiveRecord) and forbid usages that
can workaround it.
If you are interested, here is a gist:
https://gist.github.com/cyrilchampier/fdb945e8a09f93d50c7e89305c2f53f0

I wish there was a simple flag to activate in PostgreSQL to do that!

Thanks for your ideas!

On Thu, Jul 25, 2019 at 4:55 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On Fri, Jul 26, 2019 at 9:53 AM Cyril Champier
<cyril.champier@doctolib.com> wrote:

Adrian:

Are you really looking for a pseudo-random name?

No, the code I pasted was an existing production bug: the last_name should have been unique, so the selected patient would always be the same.
This should have been detected in tests, but since the order was "almost always the same", our test was green 99% of the time, so we discarded it as flaky.

If the filter should return at most 1 row, why put a LIMIT in the
first place? Even with a forced random() you won't get a failure
every time, while asserting there's at most 1 row returned is
guaranteed to fail?

Julien,

Because it's production code generated by our ORM for this command:
`Patient.find_by(last_name: 'champier')`.
Of course this was not intended by the developer that though the last_name
was unique.

On Fri, Jul 26, 2019 at 10:10 AM Julien Rouhaud <rjuju123@gmail.com> wrote:

On 2019-07-26 09:52:52 +0200, Cyril Champier wrote:

No, the code I pasted was an existing production bug: the last_name
should have been unique, so the selected patient would always be the
same. This should have been detected in tests, but since the order was
"almost always the same", our test was green 99% of the time, so we
discarded it as flaky.

Fuzzy testing could be an option, but this would go too far, as for
Peter extension suggestion. We have huge existing codebase with more
than 10K tests, and I do not want to modify our whole testing
strategy.

Meanwhile, I went for an ORM patch (ActiveRecord) and forbid usages
that can workaround it.

Another idea:

How do ypu prepare your test data? Do you have a (possibly large) test
database or do you populate a test database with test-specific data in a
fixture?

If you do the latter, you might be able insert the data in random order.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/&gt;