Not able to drop the chunk manually

TimescaleDB and PostgreSQL
1

Hello Team,

My timescale DB is hosted in kubernetes, we have configured the dropping of old data automatically.
Result of latest run for dropping old chunks is:

SELECT * FROM timescaledb_information.job_stats WHERE hypertable_name = ‘notifications’;

total_runs: 45252
total_successes: 378
total_failures: 44874

1- We don’t know why there are 44874 failures ? not sure how to check why there are failures?

Also we have decided to drop chunks manually which are older than 1 month, but getting below error:

$ select drop_chunks(1714435200, ‘notifications’, ‘time’);
ERROR: invalid hypertable or continuous aggregate

tried the same with other columns as well
$ select drop_chunks(1714435200, ‘notifications’, ‘intent_id’);
ERROR: invalid hypertable or continuous aggregate

We have column in table intent_time and created_time , data types are Bigint.

Please help me fix this error , I am new in timescale db.

Regards,
Pankaj

2

Hi Pakky, take a look in the drop_chunk interface, it receives the chunk regname not the id.

Please, use show_chunks('notifications', older_than => '1 month'::interval) than you can try something like:

select drop_chunks(c) from show_chunks('notifications', older_than => '1 month'::interval) as c

Check examples in the official docs: Timescale documentation

3

@jonatasdp Thanks for responding.

I am able to get the output from command:

SELECT show_chunks('notifications');

But getting an error while running the command to drop chunk:

select show_chunks('notifications', older_than => '3 month'::interval);
ERROR:  can only use an INTERVAL for TIMESTAMP, TIMESTAMPTZ, and DATE types

I tried by using bigint casting but that also not helping here:

SELECT show_chunks('notifications', TO_TIMESTAMP(1717222794) , TO_TIMESTAMP(1719814794));
ERROR:  invalid time argument type "timestamp with time zone"
HINT:  Try casting the argument to "bigint".

SELECT show_chunks('notifications', TO_TIMESTAMP(1717222794::bigint) , TO_TIMESTAMP(1719814794::bigint));
ERROR:  invalid time argument type "timestamp with time zone"
HINT:  Try casting the argument to "bigint".

Below is the data of the columns present in the table.

                 Table "public.notifications"
      Column       |  Type   | Collation | Nullable | Default
-------------------+---------+-----------+----------+---------
 id                | uuid    |           | not null |
 created_time      | bigint  |           | not null |
 job_created_time  | bigint  |           | not null |
 intent_time       | bigint  |           | not null |
 notification_type | text    |           | not null |
 vendor            | text    |           |          |
 language          | text    |           | not null |
 template_id       | text    |           |          |
 message_id        | text    |           | not null |
 message_body      | text    |           | not null |
 is_unicode        | boolean |           | not null |
 message_units     | integer |           | not null |
 webhook_id        | text    |           | not null |
 physical_job_id   | uuid    |           | not null |
 country_code      | text    |           | not null |
 phone             | text    |           | not null |
 entity_type       | text    |           |          |
 entity_id         | text    |           |          |
 feature           | text    |           | not null |
 state             | text    |           | not null |
 device_id         | uuid    |           |          |
 meta              | jsonb   |           |          |
4

What version are you using?

I just tried it exactly now with a hypertable and it works:

tsdb=> select show_chunks('crypto_ticks', older_than => '3 month'::interval);
               show_chunks
------------------------------------------
 _timescaledb_internal._hyper_1_44_chunk
 _timescaledb_internal._hyper_1_45_chunk
 _timescaledb_internal._hyper_1_47_chunk
 _timescaledb_internal._hyper_1_57_chunk

Maybe the issue is with time dimension coming from bigint instead of timestamptz column?