[Bug]: Delete sql from hypertable failed - continuous agg trigger function must be called on hypertable chunks only

cchengubnt commented 9 months ago

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Query executor

What happened?

I just run a delete sql to clean history data, but met unexpected error:continuous agg trigger function must be called on hypertable chunks only

master@127:systemlog> delete from "logs" where time < '2023-08-10'
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
continuous agg trigger function must be called on hypertable chunks only
Time: 0.432s
master@127:systemlog> \dx
+-------------+---------+------------+-------------------------------------------------------------------+
| Name        | Version | Schema     | Description                                                       |
|-------------+---------+------------+-------------------------------------------------------------------|
| pgcrypto    | 1.3     | public     | cryptographic functions                                           |
| plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language                                      |
| timescaledb | 2.9.3   | public     | Enables scalable inserts and complex queries for time-series data |
+-------------+---------+------------+-------------------------------------------------------------------+
> select version()
| PostgreSQL 13.9 on x86_64-pc-linux-musl, compiled by gcc (Alpine 12.2.1_git20220924-r4) 12.2.1 20220924, 64-bit |

TimescaleDB version affected

2.9.3

PostgreSQL version used

13.9

What operating system did you use?

x86_64-pc-linux-musl

What installation method did you use?

Docker

What platform did you run on?

Amazon Web Services (AWS)

Relevant log output and stack trace

No response

How can we reproduce the bug?

delete from "xxx" where time < 'xxx' in my environment

akuzm commented 9 months ago

Thanks for reporting the problem! My colleagues couldn't reproduce this on 2.9.3 using the following test case:

CREATE TABLE metrics(time timestamptz NOT NULL, device_id int, value float);
SELECT create_hypertable('metrics', 'time');

INSERT INTO metrics (time, device_id, value)
SELECT t, s, 1 FROM generate_series('2023-11-09 00:00:00'::timestamptz, '2023-11-09 00:00:00'::timestamptz + interval '1 day', '10s') t CROSS JOIN generate_series(1, 10, 1) s;

SELECT count(*) FROM metrics;

CREATE MATERIALIZED VIEW metrics_by_hour WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket, count(*) FROM metrics GROUP BY 1;

SELECT count(*) FROM metrics_by_hour;

DELETE FROM metrics WHERE "time" < '2023-11-09 12:00:00'::timestamptz;
SELECT count(*) FROM metrics WHERE "time" < '2023-11-09 12:00:00'::timestamptz;
SELECT count(*) FROM metrics;

CALL refresh_continuous_aggregate('metrics_by_hour', NULL, NULL);
SELECT count(*) FROM metrics_by_hour;

In the future release 2.13, the error message will contain more information that will help us debug the problem. In the meantime, could you please provide the following info:

Hypertable and Continuous Aggregate definitions using \d+.
EXPLAIN output for the DELETE query
Does the error reproduce reliably each time you execute the query, or is it a one-off thing?

cchengubnt commented 9 months ago

@akuzm Appreciates for your work!

This error can reproduce reliably in my test env. Here are the information I can paste:

definitions using \d+

db> \d+ table_xxx
+--------------+-----------------------------+---------------------------------------------------------+----------+--------------+-------------+
| Column       | Type                        | Modifiers                                               | Storage  | Stats target | Description |
|--------------+-----------------------------+---------------------------------------------------------+----------+--------------+-------------|
| id           | bigint                      |  not null default nextval('logs_main_id_seq'::regclass) | plain    | <null>       | <null>      |
| event        | jsonb                       |  not null                                               | extended | <null>       | <null>      |
| time         | timestamp without time zone |  not null                                               | plain    | <null>       | <null>      |
| unique_id    | character varying(36)       |  default public.gen_random_uuid()                       | extended | <null>       | <null>      |
+--------------+-----------------------------+---------------------------------------------------------+----------+--------------+-------------+
Indexes:
  .....
Triggers:
ts_cagg_invalidation_trigger AFTER INSERT OR DELETE OR UPDATE ON "table_xxx" FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.continuous_agg_invalidation_trigger('35')
ts_insert_blocker BEFORE INSERT ON "table_xxx" FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Child tables: _timescaledb_internal._hyper_35_7493_chunk,
          _timescaledb_internal._hyper_35_8130_chunk,
          _timescaledb_internal._hyper_35_8681_chunk,
          _timescaledb_internal._hyper_35_9112_chunk
Has OIDs: no

explain the sql

db > DELETE FROM "table_xxx" WHERE time < '2023-08-23 07:00:00';
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
continuous agg trigger function must be called on hypertable chunks only
Time: 0.204s
db> explain analyze DELETE FROM "table_xxx" WHERE time < '2023-08-23 07:00:00';
continuous agg trigger function must be called on hypertable chunks only
Time: 0.208s
db> explain DELETE FROM "table_xxx" WHERE time < '2023-08-23 07:00:00';
+-------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                      |
|-------------------------------------------------------------------------------------------------|
| Delete on "table_xxx"  (cost=0.00..1.04 rows=1 width=6)         |
|   ->  Seq Scan on "table_xxx"  (cost=0.00..1.04 rows=1 width=6) |
|         Filter: ("time" < '2023-08-23 07:00:00'::timestamp without time zone)                   |
+-------------------------------------------------------------------------------------------------+
EXPLAIN 3
Time: 0.427s
db> select min(time) from "table_xxx"
+-------------------------+
| min                     |
|-------------------------|
| 2023-08-24 03:32:46.645 |
+-------------------------+
SELECT 1
Time: 0.241s

mahipv commented 8 months ago

Hi @cchengubnt, Unable to reproduce the bug. The query plan above does not list any chunks , can you please run vacuum analyze on the table and check if error still exists.

nikkhils commented 7 months ago

@cchengubnt can you also provide the definition of the cagg?

Also, are you still seeing this error? Did you try with newer timescaledb version?

fabriziomello commented 4 months ago

@cchengubnt can u please check if the following query returns something?

SELECT count(*) FROM pg_catalog.pg_class r JOIN pg_catalog.pg_namespace n ON n.oid = r.relnamespace 
WHERE n.nspname = '_timescaledb_internal' AND r.relname ~ '^_hyper_1_[0-9]*_chunk' AND r.relkind = 'r'
AND NOT EXISTS (
    SELECT FROM _timescaledb_catalog.chunk
    WHERE chunk.schema_name = n.nspname AND chunk.table_name = r.relname AND dropped IS FALSE);

So if the result of previous query is >0 then you can try to INSERT back the missing chunks in TSDB metadata:

INSERT INTO _timescaledb_catalog.chunk (id, hypertable_id, schema_name, table_name, creation_time)
WITH chunks_not_found AS (
  SELECT r.oid AS relid, n.nspname, r.relname FROM pg_catalog.pg_class r JOIN pg_catalog.pg_namespace n ON n.oid = r.relnamespace 
  WHERE n.nspname = '_timescaledb_internal' AND r.relname ~ '^_hyper_[0-9]*_[0-9]*_chunk' AND r.relkind = 'r'
  AND NOT EXISTS (
      SELECT FROM _timescaledb_catalog.chunk
      WHERE chunk.schema_name = n.nspname AND chunk.table_name = r.relname AND dropped IS FALSE)
)
SELECT split_part(relname, '_', 4)::INTEGER, split_part(relname, '_', 3)::INTEGER, nspname, relname, (pg_catalog.pg_stat_file(pg_catalog.pg_relation_filepath(relid))).modification
FROM chunks_not_found;

timescale / timescaledb