Subject: regression tests fails

From: Pavel Stehule <[email-masked]>
Date: Wed, 16 Nov 2016 05:54:34 +0100

Hi

I have a repeated problem with regress tests

master, Fedora 25,

running on port 50848 with PID 5548
============== creating database "regression"         ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test ddl                      ... ok
test xact                     ... ok
test rewrite                  ... ok
test toast                    ... ok
test permissions              ... ok
test decoding_in_xact         ... ok
test decoding_into_rel        ... ok
test binary                   ... ok
test prepared                 ... ok
test replorigin               ... ok
test time                     ... ok
test messages                 ... ok
test spill                    ... FAILED
============== shutting down postmaster               ==============

The result is in unstable order.

Regards

Pavel

-- 
Sent via pgsql-hackers mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

---

From: Pavel Stehule <[email-masked]>
Date: Mon, 21 Nov 2016 07:45:25 +0100

> The result is in unstable order.

I was wrong - there is ORDER BY clause

 SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*),
(array_agg(data))[1], (array_agg(data))[count(*)]
  FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data
~ 'INSERT'
  GROUP BY 1 ORDER BY 1;

but result is sensitive on locales setting - doesn't work well with czech
locales.

Regards

Pavel

---

From: Craig Ringer <[email-masked]>
Date: Mon, 21 Nov 2016 15:09:30 +0800

> but result is sensitive on locales setting - doesn't work well with czech

Simple fix here is to append COLLATE "C" after the ORDER BY.

-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

---

From: Pavel Stehule <[email-masked]>
Date: Mon, 21 Nov 2016 08:13:56 +0100

> Simple fix here is to append COLLATE "C" after the ORDER BY.
it needs little bit bigger change - COLLATE cannot be used with positional
ORDER BY

Regards

Pavel

---

From: Pavel Stehule <[email-masked]>
Date: Mon, 21 Nov 2016 08:25:40 +0100

> it needs little bit bigger change - COLLATE cannot be used with positional

here is a patch

Regards

Pavel

> Pavel
diff --git a/contrib/test_decoding/expected/spill.out b/contrib/test_decoding/expected/spill.out
index 363e9a3..10734bd 100644
--- a/contrib/test_decoding/expected/spill.out
+++ b/contrib/test_decoding/expected/spill.out
@@ -164,7 +164,7 @@ SAVEPOINT s2;
 INSERT INTO spill_test SELECT 'serialize-subsmall-subbig--2:'||g.i FROM generate_series(2, 5001) g(i);
 RELEASE SAVEPOINT s2;
 COMMIT;
-SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
+SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
 FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
 GROUP BY 1 ORDER BY 1;
      regexp_split_to_array     | count |                                  array_agg                                   |                                    array_agg                                    
@@ -182,7 +182,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subbig-subbig--2:'||g.i FROM gen
 RELEASE SAVEPOINT s2;
 RELEASE SAVEPOINT s1;
 COMMIT;
-SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
+SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
 FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
 GROUP BY 1 ORDER BY 1;
        regexp_split_to_array        | count |                                      array_agg                                       |                                       array_agg                                       
@@ -200,7 +200,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subbig-subsmall--2:'||g.i FROM g
 RELEASE SAVEPOINT s2;
 RELEASE SAVEPOINT s1;
 COMMIT;
-SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
+SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
 FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
 GROUP BY 1 ORDER BY 1;
         regexp_split_to_array         | count |                                       array_agg                                        |                                       array_agg                                        
@@ -218,7 +218,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subsmall-subbig--2:'||g.i FROM g
 RELEASE SAVEPOINT s2;
 RELEASE SAVEPOINT s1;
 COMMIT;
-SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
+SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
 FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
 GROUP BY 1 ORDER BY 1;
         regexp_split_to_array         | count |                                      array_agg                                      |                                       array_agg                                        
@@ -238,7 +238,7 @@ SAVEPOINT s3;
 INSERT INTO spill_test SELECT 'serialize-nested-subbig-subbigabort-subbig-3:'||g.i FROM generate_series(5001, 10000) g(i);
 RELEASE SAVEPOINT s1;
 COMMIT;
-SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
+SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
 FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
 GROUP BY 1 ORDER BY 1;
              regexp_split_to_array             | count |                                            array_agg                                            |                                            array_agg                                             
diff --git a/contrib/test_decoding/sql/spill.sql b/contrib/test_decoding/sql/spill.sql
index 358af0b..e638cac 100644
--- a/contrib/test_decoding/sql/spill.sql
+++ b/contrib/test_decoding/sql/spill.sql
@@ -116,7 +116,7 @@ SAVEPOINT s2;
 INSERT INTO spill_test SELECT 'serialize-subsmall-subbig--2:'||g.i FROM generate_series(2, 5001) g(i);
 RELEASE SAVEPOINT s2;
 COMMIT;
-SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
+SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
 FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
 GROUP BY 1 ORDER BY 1;
 
@@ -129,7 +129,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subbig-subbig--2:'||g.i FROM gen
 RELEASE SAVEPOINT s2;
 RELEASE SAVEPOINT s1;
 COMMIT;
-SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
+SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
 FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
 GROUP BY 1 ORDER BY 1;
 
@@ -142,7 +142,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subbig-subsmall--2:'||g.i FROM g
 RELEASE SAVEPOINT s2;
 RELEASE SAVEPOINT s1;
 COMMIT;
-SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
+SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
 FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
 GROUP BY 1 ORDER BY 1;
 
@@ -155,7 +155,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subsmall-subbig--2:'||g.i FROM g
 RELEASE SAVEPOINT s2;
 RELEASE SAVEPOINT s1;
 COMMIT;
-SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
+SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
 FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
 GROUP BY 1 ORDER BY 1;
 
@@ -170,7 +170,7 @@ SAVEPOINT s3;
 INSERT INTO spill_test SELECT 'serialize-nested-subbig-subbigabort-subbig-3:'||g.i FROM generate_series(5001, 10000) g(i);
 RELEASE SAVEPOINT s1;
 COMMIT;
-SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
+SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
 FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
 GROUP BY 1 ORDER BY 1;

---

From: Tom Lane <[email-masked]>
Date: Mon, 21 Nov 2016 20:40:16 -0500

> here is a patch

Pushed, thanks.

			regards, tom lane