search

hydradatabase / hydra

Hydra: Column-oriented Postgres. Add scalable analytics to your project in minutes.
https://www.hydra.so
Apache License 2.0
2.83k stars 76 forks source link

[Bug]: columnar_vacuum_udf and columnar_cache tests failed #202

Closed japinli closed 11 months ago

japinli commented 11 months ago

What's wrong?

Hi,

When I try to use hydra columnar on PostgreSQL 14.10, the regressions tests columnar_vacuum_udf and columnar_cache cannot pass, here is the output:

diff -U3 /home/japin/Codes/hydra/columnar/src/test/regress/expected/columnar_vacuum_udf.out /home/japin/Codes/hydra/columnar/src/test/regress/results/columnar_vacuum_udf.out
--- /home/japin/Codes/hydra/columnar/src/test/regress/expected/columnar_vacuum_udf.out  2023-11-15 16:59:11.336567094 +0800
+++ /home/japin/Codes/hydra/columnar/src/test/regress/results/columnar_vacuum_udf.out   2023-11-22 11:23:48.162978710 +0800
@@ -5,32 +5,32 @@
 SELECT * FROM columnar.stats('t1'::regclass);
  stripeid | fileoffset | rowcount | deletedrows | chunkcount | datalength
 ----------+------------+----------+-------------+------------+------------
-        1 |      16336 |   150000 |           0 |         15 |     788928
-        2 |     808632 |   150000 |           0 |         15 |     815091
-        3 |    1625432 |   150000 |           0 |         15 |     814184
-        4 |    2442232 |   150000 |           0 |         15 |     813607
-        5 |    3259032 |   150000 |           0 |         15 |     813550
-        6 |    4075832 |   150000 |           0 |         15 |     815135
-        7 |    4892632 |   100000 |           0 |         10 |     543291
+        1 |      16336 |   150000 |           0 |         15 |     788988
+        2 |     808632 |   150000 |           0 |         15 |     815078
+        3 |    1625432 |   150000 |           0 |         15 |     814175
+        4 |    2442232 |   150000 |           0 |         15 |     813594
+        5 |    3259032 |   150000 |           0 |         15 |     813547
+        6 |    4075832 |   150000 |           0 |         15 |     815119
+        7 |    4892632 |   100000 |           0 |         10 |     543279
 (7 rows)

 SELECT * FROM columnar.stats('t2'::regclass);
  stripeid | fileoffset | rowcount | deletedrows | chunkcount | datalength
 ----------+------------+----------+-------------+------------+------------
-        1 |      16336 |   150000 |           0 |         15 |     788928
-        2 |     808632 |   150000 |           0 |         15 |     815091
-        3 |    1625432 |   150000 |           0 |         15 |     814184
-        4 |    2442232 |   150000 |           0 |         15 |     813607
-        5 |    3259032 |   150000 |           0 |         15 |     813550
-        6 |    4075832 |   150000 |           0 |         15 |     815135
-        7 |    4892632 |   150000 |           0 |         15 |     815582
-        8 |    5709432 |   150000 |           0 |         15 |     812780
-        9 |    6526232 |   150000 |           0 |         15 |     814376
-       10 |    7343032 |   150000 |           0 |         15 |     814484
-       11 |    8159832 |   150000 |           0 |         15 |     815362
-       12 |    8976632 |   150000 |           0 |         15 |     813439
-       13 |    9793432 |   150000 |           0 |         15 |     814608
-       14 |   10610232 |    50000 |           0 |          5 |     272361
+        1 |      16336 |   150000 |           0 |         15 |     788988
+        2 |     808632 |   150000 |           0 |         15 |     815078
+        3 |    1625432 |   150000 |           0 |         15 |     814175
+        4 |    2442232 |   150000 |           0 |         15 |     813594
+        5 |    3259032 |   150000 |           0 |         15 |     813547
+        6 |    4075832 |   150000 |           0 |         15 |     815119
+        7 |    4892632 |   150000 |           0 |         15 |     815566
+        8 |    5709432 |   150000 |           0 |         15 |     812772
+        9 |    6526232 |   150000 |           0 |         15 |     814363
+       10 |    7343032 |   150000 |           0 |         15 |     814474
+       11 |    8159832 |   150000 |           0 |         15 |     815346
+       12 |    8976632 |   150000 |           0 |         15 |     813426
+       13 |    9793432 |   150000 |           0 |         15 |     814589
+       14 |   10610232 |    50000 |           0 |          5 |     272356
 (14 rows)

 SELECT * FROM columnar.vacuum('t1'::regclass);
@@ -42,32 +42,32 @@
 SELECT * FROM columnar.stats('t1'::regclass);
  stripeid | fileoffset | rowcount | deletedrows | chunkcount | datalength
 ----------+------------+----------+-------------+------------+------------
-        1 |      16336 |   150000 |           0 |         15 |     788928
-        2 |     808632 |   150000 |           0 |         15 |     815091
-        3 |    1625432 |   150000 |           0 |         15 |     814184
-        4 |    2442232 |   150000 |           0 |         15 |     813607
-        5 |    3259032 |   150000 |           0 |         15 |     813550
-        6 |    4075832 |   150000 |           0 |         15 |     815135
-        7 |    4892632 |   100000 |           0 |         10 |     543291
+        1 |      16336 |   150000 |           0 |         15 |     788988
+        2 |     808632 |   150000 |           0 |         15 |     815078
+        3 |    1625432 |   150000 |           0 |         15 |     814175
+        4 |    2442232 |   150000 |           0 |         15 |     813594
+        5 |    3259032 |   150000 |           0 |         15 |     813547
+        6 |    4075832 |   150000 |           0 |         15 |     815119
+        7 |    4892632 |   100000 |           0 |         10 |     543279
 (7 rows)

 SELECT * FROM columnar.stats('t2'::regclass);
  stripeid | fileoffset | rowcount | deletedrows | chunkcount | datalength
 ----------+------------+----------+-------------+------------+------------
-        1 |      16336 |   150000 |           0 |         15 |     788928
-        2 |     808632 |   150000 |           0 |         15 |     815091
-        3 |    1625432 |   150000 |           0 |         15 |     814184
-        4 |    2442232 |   150000 |           0 |         15 |     813607
-        5 |    3259032 |   150000 |           0 |         15 |     813550
-        6 |    4075832 |   150000 |           0 |         15 |     815135
-        7 |    4892632 |   150000 |           0 |         15 |     815582
-        8 |    5709432 |   150000 |           0 |         15 |     812780
-        9 |    6526232 |   150000 |           0 |         15 |     814376
-       10 |    7343032 |   150000 |           0 |         15 |     814484
-       11 |    8159832 |   150000 |           0 |         15 |     815362
-       12 |    8976632 |   150000 |           0 |         15 |     813439
-       13 |    9793432 |   150000 |           0 |         15 |     814608
-       14 |   10610232 |    50000 |           0 |          5 |     272361
+        1 |      16336 |   150000 |           0 |         15 |     788988
+        2 |     808632 |   150000 |           0 |         15 |     815078
+        3 |    1625432 |   150000 |           0 |         15 |     814175
+        4 |    2442232 |   150000 |           0 |         15 |     813594
+        5 |    3259032 |   150000 |           0 |         15 |     813547
+        6 |    4075832 |   150000 |           0 |         15 |     815119
+        7 |    4892632 |   150000 |           0 |         15 |     815566
+        8 |    5709432 |   150000 |           0 |         15 |     812772
+        9 |    6526232 |   150000 |           0 |         15 |     814363
+       10 |    7343032 |   150000 |           0 |         15 |     814474
+       11 |    8159832 |   150000 |           0 |         15 |     815346
+       12 |    8976632 |   150000 |           0 |         15 |     813426
+       13 |    9793432 |   150000 |           0 |         15 |     814589
+       14 |   10610232 |    50000 |           0 |          5 |     272356
 (14 rows)

 SELECT * FROM columnar.vacuum('t2'::regclass);
@@ -79,32 +79,32 @@
 SELECT * FROM columnar.stats('t1'::regclass);
  stripeid | fileoffset | rowcount | deletedrows | chunkcount | datalength
 ----------+------------+----------+-------------+------------+------------
-        1 |      16336 |   150000 |           0 |         15 |     788928
-        2 |     808632 |   150000 |           0 |         15 |     815091
-        3 |    1625432 |   150000 |           0 |         15 |     814184
-        4 |    2442232 |   150000 |           0 |         15 |     813607
-        5 |    3259032 |   150000 |           0 |         15 |     813550
-        6 |    4075832 |   150000 |           0 |         15 |     815135
-        7 |    4892632 |   100000 |           0 |         10 |     543291
+        1 |      16336 |   150000 |           0 |         15 |     788988
+        2 |     808632 |   150000 |           0 |         15 |     815078
+        3 |    1625432 |   150000 |           0 |         15 |     814175
+        4 |    2442232 |   150000 |           0 |         15 |     813594
+        5 |    3259032 |   150000 |           0 |         15 |     813547
+        6 |    4075832 |   150000 |           0 |         15 |     815119
+        7 |    4892632 |   100000 |           0 |         10 |     543279
 (7 rows)

 SELECT * FROM columnar.stats('t2'::regclass);
  stripeid | fileoffset | rowcount | deletedrows | chunkcount | datalength
 ----------+------------+----------+-------------+------------+------------
-        1 |      16336 |   150000 |           0 |         15 |     788928
-        2 |     808632 |   150000 |           0 |         15 |     815091
-        3 |    1625432 |   150000 |           0 |         15 |     814184
-        4 |    2442232 |   150000 |           0 |         15 |     813607
-        5 |    3259032 |   150000 |           0 |         15 |     813550
-        6 |    4075832 |   150000 |           0 |         15 |     815135
-        7 |    4892632 |   150000 |           0 |         15 |     815582
-        8 |    5709432 |   150000 |           0 |         15 |     812780
-        9 |    6526232 |   150000 |           0 |         15 |     814376
-       10 |    7343032 |   150000 |           0 |         15 |     814484
-       11 |    8159832 |   150000 |           0 |         15 |     815362
-       12 |    8976632 |   150000 |           0 |         15 |     813439
-       13 |    9793432 |   150000 |           0 |         15 |     814608
-       14 |   10610232 |    50000 |           0 |          5 |     272361
+        1 |      16336 |   150000 |           0 |         15 |     788988
+        2 |     808632 |   150000 |           0 |         15 |     815078
+        3 |    1625432 |   150000 |           0 |         15 |     814175
+        4 |    2442232 |   150000 |           0 |         15 |     813594
+        5 |    3259032 |   150000 |           0 |         15 |     813547
+        6 |    4075832 |   150000 |           0 |         15 |     815119
+        7 |    4892632 |   150000 |           0 |         15 |     815566
+        8 |    5709432 |   150000 |           0 |         15 |     812772
+        9 |    6526232 |   150000 |           0 |         15 |     814363
+       10 |    7343032 |   150000 |           0 |         15 |     814474
+       11 |    8159832 |   150000 |           0 |         15 |     815346
+       12 |    8976632 |   150000 |           0 |         15 |     813426
+       13 |    9793432 |   150000 |           0 |         15 |     814589
+       14 |   10610232 |    50000 |           0 |          5 |     272356
 (14 rows)

 DELETE FROM t1 WHERE a % 2 = 0;
@@ -112,32 +112,32 @@
 SELECT * FROM columnar.stats('t1'::regclass);
  stripeid | fileoffset | rowcount | deletedrows | chunkcount | datalength
 ----------+------------+----------+-------------+------------+------------
-        1 |      16336 |   150000 |       75000 |         15 |     788928
-        2 |     808632 |   150000 |       75000 |         15 |     815091
-        3 |    1625432 |   150000 |       75000 |         15 |     814184
-        4 |    2442232 |   150000 |       75000 |         15 |     813607
-        5 |    3259032 |   150000 |       75000 |         15 |     813550
-        6 |    4075832 |   150000 |       75000 |         15 |     815135
-        7 |    4892632 |   100000 |       50000 |         10 |     543291
+        1 |      16336 |   150000 |       75000 |         15 |     788988
+        2 |     808632 |   150000 |       75000 |         15 |     815078
+        3 |    1625432 |   150000 |       75000 |         15 |     814175
+        4 |    2442232 |   150000 |       75000 |         15 |     813594
+        5 |    3259032 |   150000 |       75000 |         15 |     813547
+        6 |    4075832 |   150000 |       75000 |         15 |     815119
+        7 |    4892632 |   100000 |       50000 |         10 |     543279
 (7 rows)

 SELECT * FROM columnar.stats('t2'::regclass);
  stripeid | fileoffset | rowcount | deletedrows | chunkcount | datalength
 ----------+------------+----------+-------------+------------+------------
-        1 |      16336 |   150000 |           0 |         15 |     788928
-        2 |     808632 |   150000 |           0 |         15 |     815091
-        3 |    1625432 |   150000 |           0 |         15 |     814184
-        4 |    2442232 |   150000 |           0 |         15 |     813607
-        5 |    3259032 |   150000 |           0 |         15 |     813550
-        6 |    4075832 |   150000 |           0 |         15 |     815135
-        7 |    4892632 |   150000 |           0 |         15 |     815582
-        8 |    5709432 |   150000 |           0 |         15 |     812780
-        9 |    6526232 |   150000 |           0 |         15 |     814376
-       10 |    7343032 |   150000 |           0 |         15 |     814484
-       11 |    8159832 |   150000 |           0 |         15 |     815362
-       12 |    8976632 |   150000 |           0 |         15 |     813439
-       13 |    9793432 |   150000 |           0 |         15 |     814608
-       14 |   10610232 |    50000 |           0 |          5 |     272361
+        1 |      16336 |   150000 |           0 |         15 |     788988
+        2 |     808632 |   150000 |           0 |         15 |     815078
+        3 |    1625432 |   150000 |           0 |         15 |     814175
+        4 |    2442232 |   150000 |           0 |         15 |     813594
+        5 |    3259032 |   150000 |           0 |         15 |     813547
+        6 |    4075832 |   150000 |           0 |         15 |     815119
+        7 |    4892632 |   150000 |           0 |         15 |     815566
+        8 |    5709432 |   150000 |           0 |         15 |     812772
+        9 |    6526232 |   150000 |           0 |         15 |     814363
+       10 |    7343032 |   150000 |           0 |         15 |     814474
+       11 |    8159832 |   150000 |           0 |         15 |     815346
+       12 |    8976632 |   150000 |           0 |         15 |     813426
+       13 |    9793432 |   150000 |           0 |         15 |     814589
+       14 |   10610232 |    50000 |           0 |          5 |     272356
 (14 rows)

 SELECT * FROM columnar.vacuum('t1'::regclass);
@@ -155,29 +155,29 @@
 SELECT * FROM columnar.stats('t1'::regclass);
  stripeid | fileoffset | rowcount | deletedrows | chunkcount | datalength
 ----------+------------+----------+-------------+------------+------------
-        8 |      16336 |   150000 |           0 |         15 |     808748
-        9 |     825084 |   150000 |           0 |         15 |     822409
-       10 |    1647493 |   150000 |           0 |         15 |     821020
-       11 |    2468513 |    50000 |           0 |          5 |     273902
+        8 |      16336 |   150000 |           0 |         15 |     808740
+        9 |     825076 |   150000 |           0 |         15 |     822399
+       10 |    1647475 |   150000 |           0 |         15 |     821012
+       11 |    2468487 |    50000 |           0 |          5 |     273900
 (4 rows)

 SELECT * FROM columnar.stats('t2'::regclass);
  stripeid | fileoffset | rowcount | deletedrows | chunkcount | datalength
 ----------+------------+----------+-------------+------------+------------
-        1 |      16336 |   150000 |           0 |         15 |     788928
-        2 |     808632 |   150000 |           0 |         15 |     815091
-        3 |    1625432 |   150000 |           0 |         15 |     814184
-        4 |    2442232 |   150000 |           0 |         15 |     813607
-        5 |    3259032 |   150000 |           0 |         15 |     813550
-        6 |    4075832 |   150000 |           0 |         15 |     815135
-        7 |    4892632 |   150000 |           0 |         15 |     815582
-        8 |    5709432 |   150000 |           0 |         15 |     812780
-        9 |    6526232 |   150000 |           0 |         15 |     814376
-       10 |    7343032 |   150000 |           0 |         15 |     814484
-       11 |    8159832 |   150000 |           0 |         15 |     815362
-       12 |    8976632 |   150000 |           0 |         15 |     813439
-       13 |    9793432 |   150000 |           0 |         15 |     814608
-       14 |   10610232 |    50000 |           0 |          5 |     272361
+        1 |      16336 |   150000 |           0 |         15 |     788988
+        2 |     808632 |   150000 |           0 |         15 |     815078
+        3 |    1625432 |   150000 |           0 |         15 |     814175
+        4 |    2442232 |   150000 |           0 |         15 |     813594
+        5 |    3259032 |   150000 |           0 |         15 |     813547
+        6 |    4075832 |   150000 |           0 |         15 |     815119
+        7 |    4892632 |   150000 |           0 |         15 |     815566
+        8 |    5709432 |   150000 |           0 |         15 |     812772
+        9 |    6526232 |   150000 |           0 |         15 |     814363
+       10 |    7343032 |   150000 |           0 |         15 |     814474
+       11 |    8159832 |   150000 |           0 |         15 |     815346
+       12 |    8976632 |   150000 |           0 |         15 |     813426
+       13 |    9793432 |   150000 |           0 |         15 |     814589
+       14 |   10610232 |    50000 |           0 |          5 |     272356
 (14 rows)

 SELECT COUNT(*) FROM t1;
@@ -209,17 +209,17 @@
 SELECT * FROM columnar.stats('t1'::regclass);
  stripeid | fileoffset | rowcount | deletedrows | chunkcount | datalength
 ----------+------------+----------+-------------+------------+------------
-        8 |      16336 |   150000 |           0 |         15 |     808748
-        9 |     825084 |   150000 |           0 |         15 |     822409
-       10 |    1647493 |   150000 |           0 |         15 |     821020
-       11 |    2468513 |    50000 |           0 |          5 |     273902
-       12 |    2744448 |   150000 |           0 |         15 |     815176
-       13 |    3561248 |   150000 |           0 |         15 |     813187
-       14 |    4378048 |   150000 |           0 |         15 |     814516
-       15 |    5194848 |   150000 |           0 |         15 |     814283
-       16 |    6011648 |   150000 |           0 |         15 |     815497
-       17 |    6828448 |   150000 |           0 |         15 |     813704
-       18 |    7645248 |   100000 |           0 |         10 |     543338
+        8 |      16336 |   150000 |           0 |         15 |     808740
+        9 |     825076 |   150000 |           0 |         15 |     822399
+       10 |    1647475 |   150000 |           0 |         15 |     821012
+       11 |    2468487 |    50000 |           0 |          5 |     273900
+       12 |    2744448 |   150000 |           0 |         15 |     815167
+       13 |    3561248 |   150000 |           0 |         15 |     813174
+       14 |    4378048 |   150000 |           0 |         15 |     814504
+       15 |    5194848 |   150000 |           0 |         15 |     814272
+       16 |    6011648 |   150000 |           0 |         15 |     815479
+       17 |    6828448 |   150000 |           0 |         15 |     813690
+       18 |    7645248 |   100000 |           0 |         10 |     543327
 (11 rows)

 DELETE FROM t1 WHERE a BETWEEN 500000 AND 1500000;
@@ -233,12 +233,12 @@
 SELECT * FROM columnar.stats('t1'::regclass);
  stripeid | fileoffset | rowcount | deletedrows | chunkcount | datalength
 ----------+------------+----------+-------------+------------+------------
-        8 |      16336 |   150000 |           0 |         15 |     808748
-       16 |     825084 |   150000 |           0 |         15 |     815497
-       17 |    1640581 |   150000 |           0 |         15 |     813704
-       18 |    2454285 |   100000 |           0 |         10 |     543338
-       19 |    2997623 |   150000 |           0 |         15 |     819245
-       20 |    3816868 |    50000 |           0 |          5 |     271619
+        8 |      16336 |   150000 |           0 |         15 |     808740
+       16 |     825076 |   150000 |           0 |         15 |     815479
+       17 |    1640555 |   150000 |           0 |         15 |     813690
+       18 |    2454245 |   100000 |           0 |         10 |     543327
+       19 |    2997572 |   150000 |           0 |         15 |     819237
+       20 |    3816809 |    50000 |           0 |          5 |     271612
 (6 rows)

 DROP TABLE t1;
diff -U3 /home/japin/Codes/hydra/columnar/src/test/regress/expected/columnar_cache.out /home/japin/Codes/hydra/columnar/src/test/regress/results/columnar_cache.out
--- /home/japin/Codes/hydra/columnar/src/test/regress/expected/columnar_cache.out       2023-11-15 16:59:11.332567130 +0800
+++ /home/japin/Codes/hydra/columnar/src/test/regress/results/columnar_cache.out        2023-11-22 11:24:08.826827298 +0800
@@ -3131,7 +3131,7 @@
    ->  Gather  (cost=1471.54..1472.15 rows=6 width=8)
          Workers Planned: 6
          ->  Parallel Custom Scan (VectorAggNode)  (cost=471.54..471.55 rows=1 width=8)
-               ->  Parallel Custom Scan (ColumnarScan) on t1  (cost=0.00..54.88 rows=166667 width=0)
+               ->  Parallel Custom Scan (ColumnarScan) on t1  (cost=0.00..54.87 rows=166667 width=0)
                      Columnar Projected Columns: <columnar optimized out all columns>
 (6 rows)

I'm not sure how this happend, any suggestions?

JerrySievert commented 11 months ago

which OS and OS version are you running on?

there are differences to some of the results based on OS and OS version, we've tried to catch the differences and give alternate versions of the expected output, but currently have versions for ubuntu/debian and macos.

japinli commented 11 months ago

@JerrySievert Here is my OS information.

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 20.04.6 LTS
Release:        20.04
Codename:       focal
japinli commented 11 months ago

which OS and OS version are you running on?

there are differences to some of the results based on OS and OS version, we've tried to catch the differences and give alternate versions of the expected output, but currently have versions for ubuntu/debian and macos.

I retried on Ubuntu 22.04, all tests passed. Why it is depend on OS?

JerrySievert commented 11 months ago

data length and alignment are specific to OS, which is why the numbers weren't matching.

we have two different test results: https://github.com/hydradatabase/hydra/blob/main/columnar/src/test/regress/expected/columnar_vacuum_udf.out and https://github.com/hydradatabase/hydra/blob/main/columnar/src/test/regress/expected/columnar_vacuum_udf_1.out - one is for macOS, and the other for ubuntu 22.04, which is what we run CI on.

japinli commented 11 months ago

@JerrySievert Thanks for your explanation! Got it.