citus
citus copied to clipboard
citusdata
Flaky tests to fix
I am documenting here some flaky tests I have ran into the past weeks, but none of them have extra investigation.
- [ ]
subquery_in_whereneeds order by https://github.com/citusdata/citus/actions/runs/19567084981/attempts/1#summary-56032048177
diff -dU10 -w /__w/citus/citus/src/test/regress/expected/subquery_in_where.out /__w/citus/citus/src/test/regress/results/subquery_in_where.out
--- /__w/citus/citus/src/test/regress/expected/subquery_in_where.out.modified 2025-11-21 10:16:20.148456917 +0000
+++ /__w/citus/citus/src/test/regress/results/subquery_in_where.out.modified 2025-11-21 10:16:20.175457049 +0000
@@ -747,22 +747,22 @@
IN
(SELECT
user_id
FROM
users_table);
DEBUG: generating subplan 80_1 for subquery SELECT id, value_1 FROM subquery_in_where.local_table
DEBUG: generating subplan 80_2 for subquery SELECT user_id FROM public.users_table
DEBUG: Plan 80 query after replacing subqueries and CTEs: SELECT id, value_1 FROM (SELECT intermediate_result.id, intermediate_result.value_1 FROM read_intermediate_result('80_1'::text, 'binary'::citus_copy_format) intermediate_result(id integer, value_1 integer)) sub_table WHERE (id OPERATOR(pg_catalog.=) ANY (SELECT intermediate_result.user_id FROM read_intermediate_result('80_2'::text, 'binary'::citus_copy_format) intermediate_result(user_id integer)))
id | value_1
----+---------
- 1 | 1
2 | 2
+ 1 | 1
(2 rows)
-- Use local table in WHERE clause
SELECT
COUNT(*)
FROM
(SELECT
*
FROM
users_table
- [ ] columnar_alter https://github.com/citusdata/citus/actions/runs/6821867022/attempts/1#summary-18553017466
CREATE TABLE has_volatile AS
SELECT * FROM generate_series(1,10) id;
ALTER TABLE has_volatile ADD col4 int DEFAULT (random() * 10000)::int;
SELECT id, col4 < 10000 FROM has_volatile ORDER BY id;
id | ?column?
----+----------
1 | t
- 2 | t
+ 2 | f
3 | t
4 | t
5 | t
6 | t
7 | t
8 | t
9 | t
10 | t
(10 rows)
- [ ] citus_local_tables https://app.circleci.com/pipelines/github/citusdata/citus/34199/workflows/492aea01-e562-4114-8526-75c6e3aff95a/jobs/1208653
-- verify we still preserve the child-parent hierarchy after all conversions
-- check the shard partition
select inhrelid::regclass from pg_inherits where (select inhparent::regclass::text) ~ '^parent_1_\d{7}$' order by 1;
inhrelid
--------------------------
- parent_1_child_1_1904006
+ parent_1_child_1_1904004
(1 row)
- [ ] multi_insert_select https://app.circleci.com/pipelines/github/citusdata/citus/34280/workflows/269dfa5e-df5b-4ed2-a5e4-d47b2da75024/jobs/1212730
raw_events_second.value_1
FROM raw_events_first,
raw_events_second
WHERE raw_events_first.user_id = raw_events_second.user_id) AS foo
GROUP BY id
ORDER BY id;
DEBUG: distributed statement: INSERT INTO multi_insert_select.agg_events_13300008 AS citus_table_alias (user_id, value_1_agg) SELECT foo.id, sum(foo.value_1) AS sum FROM (SELECT raw_events_second.user_id AS id, raw_events_second.value_1 FROM multi_insert_select.raw_events_first_13300000 raw_events_first, multi_insert_select.raw_events_second_13300004 raw_events_second WHERE (raw_events_first.user_id OPERATOR(pg_catalog.=) raw_events_second.user_id)) foo WHERE (foo.id IS NOT NULL) GROUP BY foo.id ORDER BY foo.id
DEBUG: distributed statement: INSERT INTO multi_insert_select.agg_events_13300009 AS citus_table_alias (user_id, value_1_agg) SELECT foo.id, sum(foo.value_1) AS sum FROM (SELECT raw_events_second.user_id AS id, raw_events_second.value_1 FROM multi_insert_select.raw_events_first_13300001 raw_events_first, multi_insert_select.raw_events_second_13300005 raw_events_second WHERE (raw_events_first.user_id OPERATOR(pg_catalog.=) raw_events_second.user_id)) foo WHERE (foo.id IS NOT NULL) GROUP BY foo.id ORDER BY foo.id
DEBUG: distributed statement: INSERT INTO multi_insert_select.agg_events_13300010 AS citus_table_alias (user_id, value_1_agg) SELECT foo.id, sum(foo.value_1) AS sum FROM (SELECT raw_events_second.user_id AS id, raw_events_second.value_1 FROM multi_insert_select.raw_events_first_13300002 raw_events_first, multi_insert_select.raw_events_second_13300006 raw_events_second WHERE (raw_events_first.user_id OPERATOR(pg_catalog.=) raw_events_second.user_id)) foo WHERE (foo.id IS NOT NULL) GROUP BY foo.id ORDER BY foo.id
DEBUG: distributed statement: INSERT INTO multi_insert_select.agg_events_13300011 AS citus_table_alias (user_id, value_1_agg) SELECT foo.id, sum(foo.value_1) AS sum FROM (SELECT raw_events_second.user_id AS id, raw_events_second.value_1 FROM multi_insert_select.raw_events_first_13300003 raw_events_first, multi_insert_select.raw_events_second_13300007 raw_events_second WHERE (raw_events_first.user_id OPERATOR(pg_catalog.=) raw_events_second.user_id)) foo WHERE (foo.id IS NOT NULL) GROUP BY foo.id ORDER BY foo.id
-ERROR: duplicate key value violates unique constraint "agg_events_user_id_value_1_agg_key_13300008"
+ERROR: duplicate key value violates unique constraint "agg_events_user_id_value_1_agg_key_13300009"
-- subquery one more level depth
INSERT INTO agg_events
(value_4_agg,
- [ ] isolation_ref2ref_foreign_keys https://app.circleci.com/pipelines/github/citusdata/citus/34552/workflows/67998346-ae15-48cc-b426-24c52d948c5b/jobs/1229317
regexp_replace(query, E'[\\n\\r\\u2028]+', ' ', 'g' ) query
FROM pg_locks l
JOIN pg_stat_activity a
ON l.pid = a.pid
WHERE locktype='advisory'
AND application_name <> 'Citus Maintenance Daemon'
ORDER BY 1, 2, 3, 4;
classid| objid|objsubid|mode |application_name |backend_type |query
-------+-------+--------+-------------+-------------------------------------------+--------------+-------------------------------------------------
- 0|8429800| 5|ExclusiveLock|isolation/isolation_ref2ref_foreign_keys/s2|client backend| UPDATE ref_table_3 SET id = 2 WHERE id = 1;
- 0|8429801| 5|ExclusiveLock|isolation/isolation_ref2ref_foreign_keys/s2|client backend| UPDATE ref_table_3 SET id = 2 WHERE id = 1;
0|8429802| 4|ShareLock |isolation/isolation_ref2ref_foreign_keys/s2|client backend| UPDATE ref_table_3 SET id = 2 WHERE id = 1;
0|8429802| 5|ExclusiveLock|isolation/isolation_ref2ref_foreign_keys/s2|client backend| UPDATE ref_table_3 SET id = 2 WHERE id = 1;
-(4 rows)
+(2 rows)
step s1-rollback:
ROLLBACK;
step s2-rollback:
ROLLBACK;
step s1-view-locks:
-- The following output changed in PG versions 13.6 and 14.2. The output is expected
-- to change in earlier versions of PG as the and application_name format did not use
