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

On Tue, 22 Apr 2025 at 18:40, Pavel Borisov <pashkin(dot)elfe(at)gmail(dot)com> wrote:
>
> Hi, Tom!
>
> On Tue, 22 Apr 2025 at 18:23, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > Pavel Borisov <pashkin(dot)elfe(at)gmail(dot)com> writes:
> > > It's not a big problem, but propose a simple fix for the tests. It
> > > just adds ORDER BY 1 to all relevant float4 and floa8 queries.
> >
> > You do realize that this problem is hardly confined to the
> > float4 and float8 tests? To a first approximation, a table AM
> > that fails to preserve insertion order would break every single
> > one of our regression tests. (I speak from experience, as
> > Salesforce had to deal with this when I was there...)
> >
> > The reason why we don't simply add ORDER BY to everything is
> > explained at [1]:
> >
> > You might wonder why we don't order all the regression test
> > queries explicitly to get rid of this issue once and for all. The
> > reason is that that would make the regression tests less useful,
> > not more, since they'd tend to exercise query plan types that
> > produce ordered results to the exclusion of those that don't.
> >
> > At some point we will probably have to think through what we
> > want to do about running the regression tests with table AMs that
> > don't wish to preserve ordering as much as heapam does. It's going
> > to need careful balancing of multiple concerns, and I don't think
> > "blindly slap ORDER BY everywhere" is going to be an acceptable
> > answer.
> >
>
> I agree we might need to elaborate different AM support in regression tests.
> Also, I agree that these are not the only tests to be fixed.
>
> What I hardly agree with, is that we suppose it's right for regression
> tests to require some fixed results ordering for so simple cases.
> Maybe for more complicated plans it's useful, but for the float tests
> mentioned in the patch it's this requirement is a total loss IMO.
>
> I understand your sentiment against blindly slapping order by's, but I
> don't see a useful alternative for this time. Also a large number of
> tests in PG were already fortified with ORDER BY 1.

I forgot to mention that it's not only a question of INSERTs ordering.
Extension AM can have some internal ordering e.g. index-organized
tables. And besides SELECT query results following internal AM
ordering just being allowed, more importantly they are good
performance-wise and justify table AM extensibility.

Regards,
Pavel.