Question about row_number() ordering semantics

Hey everyone,

As I was playing around with `row_number()`s for cursor-based pagination, I
came across some ordering behavior that I didn't expect.

In particular, when I order in a way where multiple rows compete for the
same position in the result set (i.e., rows that are equivalent in terms of
the requested order), it appears that the `row_number()` column may be out
of order.

In the case of the following example query, the ordering in the OVER clause
is the opposite of the ordering of the rows, so I expected that the row
numbers would effectively be reversed. However, the example shows that the
for the first three values, the `row_number` rows are instead non-ordered.

=> SELECT
-> number,
-> row_number() OVER (ORDER BY number DESC) AS row_number
-> FROM generate_series(0, 20) number
-> INNER JOIN generate_series(2, 4) divisor
-> ON mod(number, divisor) = 0
-> ORDER BY number ASC;

number | row_number
--------+------------
0 | 22
0 | 24
0 | 23
2 | 21
3 | 20
4 | 18
4 | 19
6 | 17
6 | 16
8 | 15
8 | 14
9 | 13
10 | 12
12 | 11
12 | 10
12 | 9
14 | 8
15 | 7
16 | 6
16 | 5
18 | 4
18 | 3
20 | 2
20 | 1
(24 rows)

Curiously, if I set the row_number column to be `OVER (ORDER BY number
ASC)`, the column is returned in order.

Is this behavior to be expected, and is it safe to assume that the
out-of-order row numbers can only exist within the set of rows that have
the exact same ordering? In other words, can I expect that row numbers
being out of order would never happen between two rows that do not have
ordinal equivalence?

Best,
Fred

(If so, in these scenarios I could safely order by row_number as secondary
ordering without losing the correct primary sorting order. Of course, for
cursor-based pagination this will lead to some unpredictability in terms of
where new rows might appear in the result set. I'll probably choose to
rather impose more strict ordering based on other constant but variable
columns such as a creation timestamp.)