Re: Fortify float4 and float8 regression tests by ordering test results

Pavel Borisov <pashkin(dot)elfe(at)gmail(dot)com> writes:
> On Tue, 22 Apr 2025 at 20:22, Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
>> On Tue, Apr 22, 2025 at 7:20 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Alexander Korotkov <aekorotkov(at)gmail(dot)com> writes:
>>>> I'd like to add that float4.out not only assumes that insert-ordering is
>>>> preserved (this could be more-or-less portable between table AMs). It also
>>>> assumes the way UPDATE moves updated rows. That seems quite
>>>> heap-specific. You can see in the following fragment, updated rows jump to
>>>> the bottom.

>>> I'd be willing to consider a policy that we don't want to depend on
>>> exactly where UPDATE moves rows to. The proposed patch is not that,
>>> however.

>> OK, that makes sense for me.

> Thanks for this input!
> This was my first intention to fix only the test that was affected by
> UPDATE-order specifics, broke when runnung on an extension AM.
> Maybe I was too liberal and added ORDER BY's more than needed.
> I definitely agree to the proposal.

On further reflection, this policy makes plenty of sense because even
heapam is not all that stable about row order after UPDATE. With
tables large enough to draw the attention of autovacuum, we've had
to use ORDER BY or other techniques to stabilize the results, because
the timing of background autovacuums affected where rows got put.
These tests are different only because the tables are small enough
and the updates few enough that autovacuum doesn't get involved.
I think it's reasonable that at a project-policy level we should
not consider that an excuse. For example, a change in autovacuum's
rules could probably break these tests even with heapam.

On the other hand, as I mentioned earlier, a table AM that doesn't
reproduce original insertion order would break a whole lot more
tests than these. So that's a bridge I'd rather not cross,
at least not without a lot of consideration.

BTW, you forgot to update expected/float4-misrounded-input.out.

regards, tom lane