Fix stage index migration failing in PostgreSQL 10
As discussed in https://www.postgresql.org/message-id/9922.1353433645%40sss.pgh.pa.us,
the PostgreSQL window function last_value may not consider the
right rows:
Note that first_value, last_value, and nth_value consider only the rows within the "window frame", which by default contains the rows from the start of the partition through the last peer of the current row. This is likely to give unhelpful results for last_value and sometimes also nth_value. You can redefine the frame by adding a suitable frame specification (RANGE or ROWS) to the OVER clause. See Section 4.2.8 for more information about frame specifications.
This query could be fixed by adding RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, but that's quite verbose. It's simpler just to
use the first_value function:
If you want the last row in the partition, you could use first_value with the opposite sort order, or use last_value with the same sort order and RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. (The current SQL committee is nothing if not verbose :-()