Re: Order dependency in function test

On Thu, Apr 8, 2021 at 3:53 PM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> On Thu, Apr 8, 2021 at 3:34 PM Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> >
> > Looking at https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=prion&dt=2021-04-08%2009%3A43%3A13
> > which broke with the patch to add pg_wait_backend_termination().
> >
> > AFAICT the change is that the order of rows coming back from "SELECT
> > routine_name, sequence_name FROM
> > information_schema.routine_sequence_usage" has changed. This test was
> > added in f40c6969d0e ("Routine usage information schema tables"),
> >
> > It does not change consistently, as it works fine on my machine and
> > has also passed on other buildfarm animals (including other archs and
> > compilers).
> >
> > My guess is that maybe the query plan is different, ending up with a
> > different order, since there is no explicit ORDER BY in the query.
> >
> > Is there a particular thing we want to check on it that requires it to
> > run without ORDER BY, or should we add one to solve the problem? Or,
> > of course, am I completely misunderstanding it? :)
>
> The buildfarm failure is due to lack of ORDER BY clause. Upon
> searching in that file, I found below statements are returning more
> than one row but doesn't have ORDER BY clause which can make output
> quite unstable.
>
> SELECT routine_name, sequence_name FROM
> information_schema.routine_sequence_usage;
> SELECT routine_name, table_name, column_name FROM
> information_schema.routine_column_usage;
> SELECT routine_name, table_name FROM information_schema.routine_table_usage;
> SELECT * FROM functest_sri1();
> SELECT * FROM functest_sri2();
> TABLE sometable;
>
> I added a ORDER BY 1 clause for each of the above statements and
> replaced TABLE sometable; with SELECT * FROM sometable ORDER BY 1;
>
> Here's the patch.

I realized that the ORDER BY is added. Isn't it good if we add ORDER
BY for SELECT * FROM functest_sri2();, SELECT * FROM functest_sri1();
and replace TABLE sometable; with SELECT * FROM sometable ORDER BY 1;
? Otherwise they might become unstable at some other time?

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com