search

timescale / timescaledb

An open-source time-series SQL database optimized for fast ingest and complex queries. Packaged as a PostgreSQL extension.
https://www.timescale.com/
Other
17.51k stars 879 forks source link

Segfault when creating a CAGG with bucket offset. #7272

Open snyrkill opened 5 days ago

snyrkill commented 5 days ago

What type of bug is this?

Crash

What subsystems and features are affected?

Continuous aggregate

What happened?

I created a cagg:

CREATE MATERIALIZED VIEW agg_1h_w_offset
WITH (timescaledb.continuous) AS

SELECT id, time_bucket('1 hour', ts, '10 minutes'::interval) AS ts, 
.
.
.

When attempting to create a new cagg on top of the previous one:

CREATE MATERIALIZED VIEW agg_1d_without_offset
WITH (timescaledb.continuous) AS

SELECT id, time_bucket('1 day', ts ) AS ts, 
.
.
.
FROM agg_1h_w_offset
WITH NO DATA;

I get a segfault in postgres, apparently because no offset is defined.

This works however:

CREATE MATERIALIZED VIEW agg_1d_w_offset
WITH (timescaledb.continuous) AS

SELECT id, time_bucket('1 day', ts, '10 minutes'::interval) AS ts, 
.
.
.
FROM agg_1h_w_offset
WITH NO DATA;

Then I did some further testing and apparently if you build a cagg on top of a cagg w/offset the offsets need to be the same? Is this the way it is supposed to work? I changed the offset to 0 minutes and got this:

ERROR:  cannot create continuous aggregate with different bucket offset values
DETAIL:  Time origin of "agg_1d_w_offset" [00:00:00] and "agg_1h_w_offset" [00:10:00] should be the same.

TimescaleDB version affected

2.6.1

PostgreSQL version used

16.4

What operating system did you use?

Ubuntu 22.04.4 LTS

What installation method did you use?

Deb/Apt

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

No response

How can we reproduce the bug?

Easy to reproduce, see examples.
zilder commented 3 days ago

Hi @snyrkill,

Thank you for your bug report! I tried it out on the most recent timescaledb build and can confirm, that the issue is reproducible. It looks like it crashes while trying to throw an ERROR saying that bucket offsets should match (I see an attempt to dereference a null pointer when the offset value is not set). I will pass it on to the devs.

Here's the full script I used to reproduce the issue:

create extension timescaledb;
create table metrics (ts timestamp, device_id integer, val float);
select create_hypertable('metrics', 'ts', chunk_time_interval=>'7 days'::interval);
insert into metrics values ('2024-01-01 12:00', 1, 15.0), ('2024-01-10 12:00', 1, 30.0);

CREATE MATERIALIZED VIEW daily_avg_with_offset
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', ts, '1 hour'::interval) AS ts, avg(val) FROM metrics GROUP BY 1;

CREATE MATERIALIZED VIEW monthly_avg_without_offset
WITH (timescaledb.continuous) AS
SELECT time_bucket('7 day', ts) AS ts, AVG(avg)
FROM daily_avg_with_offset
GROUP BY 1
WITH NO DATA;

And the stacktrace:

#0  interval_out (fcinfo=fcinfo@entry=0x7ffc3c5d6be0) at timestamp.c:963
#1  0x00005758f1ebb0e7 in DirectFunctionCall1Coll (func=func@entry=0x5758f1e6bec0 <interval_out>,
    collation=collation@entry=0, arg1=arg1@entry=0) at fmgr.c:787
#2  0x00007187ff9162d0 in cagg_validate_query (query=<optimized out>, finalized=finalized@entry=true,
    cagg_schema=0x5758f3f69710 "public", cagg_name=0x5758f3ba5878 "monthly_avg_without_offset",
    is_cagg_create=is_cagg_create@entry=true)
    at /home/zilder/projects/timescaledb/tsl/src/continuous_aggs/common.c:1063
#3  0x00007187ff917499 in tsl_process_continuous_agg_viewstmt (node=0x5758f3ba6330, query_string=<optimized out>,
    pstmt=<optimized out>, with_clause_options=0x5758f3fbff00)
    at /home/zilder/projects/timescaledb/tsl/src/continuous_aggs/create.c:884
#4  0x00007187ffa0f66b in process_create_table_as (args=0x7ffc3c5d7310)
    at /home/zilder/projects/timescaledb/src/process_utility.c:4216
#5  0x00007187ffa0f419 in process_ddl_command_start (args=0x7ffc3c5d7310)
    at /home/zilder/projects/timescaledb/src/process_utility.c:4360
#6  timescaledb_ddl_command_start (pstmt=0x5758f3d25a88, query_string=<optimized out>, readonly_tree=<optimized out>,
    context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=<optimized out>, dest=0x5758f3d25b20,
    completion_tag=0x7ffc3c5d7650) at /home/zilder/projects/timescaledb/src/process_utility.c:4581
#7  0x00005758f1d7b3e6 in PortalRunUtility (portal=portal@entry=0x5758f3c1c9b0, pstmt=0x5758f3d25a88,
    isTopLevel=isTopLevel@entry=true, setHoldSnapshot=setHoldSnapshot@entry=false, dest=dest@entry=0x5758f3d25b20,
    qc=qc@entry=0x7ffc3c5d7650) at pquery.c:1158
#8  0x00005758f1d7b51a in PortalRunMulti (portal=portal@entry=0x5758f3c1c9b0, isTopLevel=isTopLevel@entry=true,
    setHoldSnapshot=setHoldSnapshot@entry=false, dest=dest@entry=0x5758f3d25b20,
    altdest=altdest@entry=0x5758f3d25b20, qc=qc@entry=0x7ffc3c5d7650) at pquery.c:1315
#9  0x00005758f1d7bc3c in PortalRun (portal=portal@entry=0x5758f3c1c9b0, count=count@entry=9223372036854775807,
    isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0x5758f3d25b20,
    altdest=altdest@entry=0x5758f3d25b20, qc=0x7ffc3c5d7650) at pquery.c:791
#10 0x00005758f1d7776b in exec_simple_query (
    query_string=0x5758f3ba4da0 "CREATE MATERIALIZED VIEW monthly_avg_without_offset\nWITH (timescaledb.continuous) AS\nSELECT time_bucket('7 day', ts) AS ts, AVG(avg)\nFROM daily_avg_with_offset\nGROUP BY 1\nWITH NO DATA;")
    at postgres.c:1250
#11 0x00005758f1d793b5 in PostgresMain (dbname=<optimized out>, username=<optimized out>) at postgres.c:4598
#12 0x00005758f1cdfd9a in BackendRun (port=0x5758f3bd39c0, port=0x5758f3bd39c0) at postmaster.c:4511
#13 BackendStartup (port=0x5758f3bd39c0) at postmaster.c:4239
#14 ServerLoop () at postmaster.c:1806
#15 0x00005758f1ce0e6e in PostmasterMain (argc=argc@entry=3, argv=argv@entry=0x5758f3b0c750) at postmaster.c:1478
#16 0x00005758f19ee6d4 in main (argc=3, argv=0x5758f3b0c750) at main.c:202