Обсуждение: Order dependency in function test

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? :)

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

On 08.04.21 12:04, Magnus Hagander 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? :)

I added some ORDER BY clauses to fix this.

On Thu, Apr 8, 2021 at 12:22 PM Peter Eisentraut
<peter.eisentraut@enterprisedb.com> wrote:
>
> On 08.04.21 12:04, Magnus Hagander 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? :)
>
> I added some ORDER BY clauses to fix this.

Thanks!

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

On Thu, Apr 8, 2021 at 3:34 PM Magnus Hagander <magnus@hagander.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.

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

On Thu, Apr 8, 2021 at 3:53 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
>
> On Thu, Apr 8, 2021 at 3:34 PM Magnus Hagander <magnus@hagander.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