Order dependency in function test

Started by Magnus Haganderalmost 5 years ago5 messages

magnus@hagander.net

almost 5 years ago

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/

Peter Eisentraut

peter.eisentraut@enterprisedb.com

almost 5 years ago

In reply to: Magnus Hagander (#1)

Re: Order dependency in function test

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.

Magnus Hagander

magnus@hagander.net

almost 5 years ago

In reply to: Peter Eisentraut (#2)

Re: Order dependency in function test

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/

Bharath Rupireddy

bharath.rupireddyforpostgres@gmail.com

almost 5 years ago

In reply to: Magnus Hagander (#1)

1 attachment(s)

Re: Order dependency in function test

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

Attachments:

v1-0001-Stabilize-tests-in-create_function_3.sql-with-ORD.patchapplication/octet-stream; name=v1-0001-Stabilize-tests-in-create_function_3.sql-with-ORD.patchDownload

From f444b962ebb985b4c8352f92c4707de65b04498c Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddy@enterprisedb.com>
Date: Thu, 8 Apr 2021 15:52:04 +0530
Subject: [PATCH v1] Stabilize tests in create_function_3.sql with ORDER BY
 clause

---
 src/test/regress/expected/create_function_3.out | 10 +++++-----
 src/test/regress/sql/create_function_3.sql      | 10 +++++-----
 2 files changed, 10 insertions(+), 10 deletions(-)

diff --git a/src/test/regress/expected/create_function_3.out b/src/test/regress/expected/create_function_3.out
index 1fbaebcc72..d5331d2b39 100644
--- a/src/test/regress/expected/create_function_3.out
+++ b/src/test/regress/expected/create_function_3.out
@@ -480,20 +480,20 @@ SELECT r0.routine_name, r1.routine_name
  functest_is_4b | functest_is_4a
 (1 row)
 
-SELECT routine_name, sequence_name FROM information_schema.routine_sequence_usage;
+SELECT routine_name, sequence_name FROM information_schema.routine_sequence_usage ORDER BY 1;
  routine_name  | sequence_name 
 ---------------+---------------
  functest_is_5 | functest1
  functest_is_6 | functest1
 (2 rows)
 
-SELECT routine_name, table_name, column_name FROM information_schema.routine_column_usage;
+SELECT routine_name, table_name, column_name FROM information_schema.routine_column_usage ORDER BY 1;
  routine_name  | table_name | column_name 
 ---------------+------------+-------------
  functest_is_7 | functest2  | a
 (1 row)
 
-SELECT routine_name, table_name FROM information_schema.routine_table_usage;
+SELECT routine_name, table_name FROM information_schema.routine_table_usage ORDER BY 1;
  routine_name  | table_name 
 ---------------+------------
  functest_is_7 | functest2
@@ -532,7 +532,7 @@ STABLE
 AS '
     VALUES (1), (2), (3);
 ';
-SELECT * FROM functest_sri1();
+SELECT * FROM functest_sri1() ORDER BY 1;
  functest_sri1 
 ---------------
              1
@@ -610,7 +610,7 @@ SELECT voidtest4(39);
  
 (1 row)
 
-TABLE sometable;
+SELECT * FROM sometable ORDER BY 1;
  f1 
 ----
  18
diff --git a/src/test/regress/sql/create_function_3.sql b/src/test/regress/sql/create_function_3.sql
index 695ee3413f..93fa7535f1 100644
--- a/src/test/regress/sql/create_function_3.sql
+++ b/src/test/regress/sql/create_function_3.sql
@@ -277,9 +277,9 @@ SELECT r0.routine_name, r1.routine_name
   FROM information_schema.routine_routine_usage rru
        JOIN information_schema.routines r0 ON r0.specific_name = rru.specific_name
        JOIN information_schema.routines r1 ON r1.specific_name = rru.routine_name;
-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 routine_name, sequence_name FROM information_schema.routine_sequence_usage ORDER BY 1;
+SELECT routine_name, table_name, column_name FROM information_schema.routine_column_usage ORDER BY 1;
+SELECT routine_name, table_name FROM information_schema.routine_table_usage ORDER BY 1;
 
 DROP FUNCTION functest_IS_4a CASCADE;
 DROP SEQUENCE functest1 CASCADE;
@@ -312,7 +312,7 @@ AS '
     VALUES (1), (2), (3);
 ';
 
-SELECT * FROM functest_sri1();
+SELECT * FROM functest_sri1() ORDER BY 1;
 EXPLAIN (verbose, costs off) SELECT * FROM functest_sri1();
 
 CREATE FUNCTION functest_sri2() RETURNS SETOF int
@@ -349,7 +349,7 @@ CREATE FUNCTION voidtest4(a int) RETURNS VOID LANGUAGE SQL AS
 $$ INSERT INTO sometable VALUES(a - 1) RETURNING f1 $$;
 SELECT voidtest4(39);
 
-TABLE sometable;
+SELECT * FROM sometable ORDER BY 1;
 
 CREATE FUNCTION voidtest5(a int) RETURNS SETOF VOID LANGUAGE SQL AS
 $$ SELECT generate_series(1, a) $$ STABLE;
-- 
2.25.1

Bharath Rupireddy

bharath.rupireddyforpostgres@gmail.com

almost 5 years ago

In reply to: Bharath Rupireddy (#4)

Re: Order dependency in function test

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