Enable real time aggregation for CAggs with joins #5243

RafiaSabih · 2023-01-30T09:25:28Z

No description provided.

codecov · 2023-01-30T09:49:16Z

Codecov Report

Merging #5243 (fb3ad7d) into main (206056c) will increase coverage by 0.00%.
The diff coverage is n/a.

❗ Current head fb3ad7d differs from pull request most recent head 350af01. Consider uploading reports for the commit 350af01 to get more accurate results

@@           Coverage Diff           @@
##             main    #5243   +/-   ##
=======================================
  Coverage   89.35%   89.35%           
=======================================
  Files         225      225           
  Lines       51925    51925           
=======================================
+ Hits        46396    46399    +3     
+ Misses       5529     5526    -3

Impacted Files	Coverage Δ
tsl/src/nodes/data_node_dispatch.c	`93.24% <0.00%> (+0.21%)`	⬆️
tsl/src/reorder.c	`85.71% <0.00%> (+0.21%)`	⬆️
src/bgw/job_stat.c	`92.18% <0.00%> (+0.31%)`	⬆️

Continue to review full report at Codecov.

Legend - Click here to learn more
Δ = absolute <relative> (impact), ø = not affected, ? = missing data
Powered by Codecov. Last update 8132908...350af01. Read the comment docs.

fabriziomello

Would be nice to show in tests all the CAggs definition (i.e. \d+ conditions_summary_daily_realtime)

mkindahl · 2023-01-31T11:01:06Z

tsl/test/expected/cagg_joins-12.out

-DETAIL:  drop cascades to table _timescaledb_internal._hyper_6_35_chunk
-drop cascades to table _timescaledb_internal._hyper_6_36_chunk
+DETAIL:  drop cascades to table _timescaledb_internal._hyper_7_37_chunk
+drop cascades to table _timescaledb_internal._hyper_7_38_chunk


Maybe you want to turn off the detailed output for this part of the test. It will change more times, and potentially generate false positives. It is sufficient to know the number of objects dropped.

mkindahl · 2023-01-31T11:02:25Z

tsl/test/expected/cagg_joins-12.out

-NOTICE:  drop cascades to 9 other objects
-DETAIL:  drop cascades to view _timescaledb_internal._partial_view_3
+NOTICE:  drop cascades to 12 other objects
+DETAIL:  drop cascades to view conditions_summary_daily_realtime
+drop cascades to view _timescaledb_internal._partial_view_3
 drop cascades to view _timescaledb_internal._direct_view_3
 drop cascades to view _timescaledb_internal._partial_view_4
 drop cascades to view _timescaledb_internal._direct_view_4
-drop cascades to view conditions_summary_daily_2
 drop cascades to view _timescaledb_internal._partial_view_5
 drop cascades to view _timescaledb_internal._direct_view_5
-drop cascades to view _timescaledb_internal._partial_view_7
-drop cascades to view _timescaledb_internal._direct_view_7
+drop cascades to view conditions_summary_daily_2
+drop cascades to view _timescaledb_internal._partial_view_6
+drop cascades to view _timescaledb_internal._direct_view_6
+drop cascades to view _timescaledb_internal._partial_view_8
+drop cascades to view _timescaledb_internal._direct_view_8


Same here, this do not add anything to the test, so you can just as well remove this output by not printing the detailed error output.

mkindahl · 2023-01-31T11:04:13Z

tsl/test/sql/cagg_joins.sql.in

+--Create a cagg with join between a hypertable and a normal table
+-- with equality condition on inner join type
+CREATE MATERIALIZED VIEW conditions_summary_daily_realtime
+WITH (timescaledb.continuous) AS
+SELECT time_bucket(INTERVAL '1 day', day) AS bucket,
+   AVG(temperature),
+   MAX(temperature),
+   MIN(temperature),
+   name
+FROM conditions, devices
+WHERE conditions.device_id = devices.device_id
+GROUP BY name, bucket;


Agree with @fabriziomello here. Show the definition of the cagg so that the test demonstrates that one with real-time aggregate was actually created.

jnidzwetzki

Overall it looks good to me. Please see the comment on the test improvement and add a proper commit/PR description and (if needed) also a changelog entry.

jnidzwetzki · 2023-02-07T16:09:20Z

tsl/test/expected/cagg_joins-12.out

+    devices
+  WHERE conditions.device_id = devices.device_id AND devices.device_id >= COALESCE(_timescaledb_internal.to_date(_timescaledb_internal.cagg_watermark(3)), '-infinity'::date)
+  GROUP BY devices.name, (time_bucket('@ 1 day'::interval, conditions.day));
+


Could you insert data into the real-time part of the CAGG and do a SELECT on the CAGG to show that real-time aggregation works?

mkindahl

Looks good.

mkindahl · 2023-02-10T08:54:35Z

tsl/src/continuous_aggs/create.c

+	 * If there is join in CAgg definition then adjust varno
+	 * to get time column from hypertable only.


Which hypertable? Both the materialized table and the "underlying" table are hypertables. Or are you thinking about the hypertable in the join? Would be good to clarify which one it is.

mkindahl · 2023-02-10T09:06:14Z

tsl/test/expected/cagg_joins-12.out

+DETAIL:  drop cascades to table _timescaledb_internal._hyper_8_41_chunk
+drop cascades to table _timescaledb_internal._hyper_8_42_chunk


Would suggest to not print the detailed message here, so set verbosity to terse.

This release contains new features and bug fixes since the 2.9.3 release. This release is high priority for upgrade. We strongly recommend that you upgrade as soon as possible. **Features** * #5241 Allow RETURNING clause when inserting into compressed chunks * #5245 Manage life-cycle of connections via memory contexts * #5246 Make connection establishment interruptible * #5253 Make data node command execution interruptible * #5243 Enable real-time aggregation for continuous aggregates with joins * #5262 Extend enabling compression on a continuous aggregrate with 'compress_segmentby' and 'compress_orderby' parameters **Bugfixes** * #4926 Fix corruption when inserting into compressed chunks * #5218 Add role-level security to job error log * #5214 Fix use of prepared statement in async module * #5290 Compression can't be enabled on continuous aggregates when segmentby/orderby columns need quotation * #5239 Fix next_start calculation for fixed schedules

RafiaSabih self-assigned this Jan 30, 2023

RafiaSabih requested review from fabriziomello and mkindahl January 30, 2023 09:25

RafiaSabih force-pushed the real_time_joins branch from e09d667 to 689feaa Compare January 30, 2023 10:12

fabriziomello reviewed Jan 30, 2023

View reviewed changes

mkindahl reviewed Jan 31, 2023

View reviewed changes

RafiaSabih force-pushed the real_time_joins branch 4 times, most recently from 350af01 to 1f4efa0 Compare February 7, 2023 11:31

jnidzwetzki approved these changes Feb 7, 2023

View reviewed changes

jnidzwetzki reviewed Feb 7, 2023

View reviewed changes

RafiaSabih force-pushed the real_time_joins branch 3 times, most recently from fc57aa3 to 328e7d8 Compare February 9, 2023 09:34

horzsolt added this to the TimescaleDB 2.10 milestone Feb 9, 2023

RafiaSabih force-pushed the real_time_joins branch from 328e7d8 to 9f10b65 Compare February 9, 2023 14:30

mkindahl approved these changes Feb 10, 2023

View reviewed changes

RafiaSabih force-pushed the real_time_joins branch from 9f10b65 to f941045 Compare February 10, 2023 10:53

RafiaSabih enabled auto-merge (rebase) February 10, 2023 11:05

Enable real time aggregation for caggs with joins

ee3e2c1

RafiaSabih force-pushed the real_time_joins branch from f941045 to ee3e2c1 Compare February 10, 2023 13:49

RafiaSabih merged commit ece15d6 into timescale:main Feb 10, 2023

RafiaSabih deleted the real_time_joins branch February 10, 2023 20:56

mahipv mentioned this pull request Feb 14, 2023

Release 2.10.0 #5324

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Enable real time aggregation for CAggs with joins #5243

Enable real time aggregation for CAggs with joins #5243

RafiaSabih commented Jan 30, 2023

codecov bot commented Jan 30, 2023 •

edited

fabriziomello left a comment

mkindahl Jan 31, 2023

mkindahl Jan 31, 2023

mkindahl Jan 31, 2023

jnidzwetzki left a comment •

edited

jnidzwetzki Feb 7, 2023

mkindahl left a comment

mkindahl Feb 10, 2023

mkindahl Feb 10, 2023

		* If there is join in CAgg definition then adjust varno
		* to get time column from hypertable only.

		DETAIL: drop cascades to table _timescaledb_internal._hyper_8_41_chunk
		drop cascades to table _timescaledb_internal._hyper_8_42_chunk

Enable real time aggregation for CAggs with joins #5243

Enable real time aggregation for CAggs with joins #5243

Conversation

RafiaSabih commented Jan 30, 2023

codecov bot commented Jan 30, 2023 • edited

Codecov Report

fabriziomello left a comment

Choose a reason for hiding this comment

mkindahl Jan 31, 2023

Choose a reason for hiding this comment

mkindahl Jan 31, 2023

Choose a reason for hiding this comment

mkindahl Jan 31, 2023

Choose a reason for hiding this comment

jnidzwetzki left a comment • edited

Choose a reason for hiding this comment

jnidzwetzki Feb 7, 2023

Choose a reason for hiding this comment

mkindahl left a comment

Choose a reason for hiding this comment

mkindahl Feb 10, 2023

Choose a reason for hiding this comment

mkindahl Feb 10, 2023

Choose a reason for hiding this comment

codecov bot commented Jan 30, 2023 •

edited

jnidzwetzki left a comment •

edited