Performing LIMIT-ed DELETE causes timescaleDB exceptions

TimescaleDB and PostgreSQL Installation, Configuration, and Errors
1

In brief:
Performing a LIMIT-ed DELETE on hypertable, where the affected chunks have been explicitly decompressed, throws an exception.

TimescaleDB version: 2.7.0
PostgreSQL version: 14.3
OS: Ubuntu 20.04
Install method: Source
Environment: Development

We need to be able to perform a limited DELETE on a partially compressed timescaleDB hypertable.

After decompressing the related chunks and performing the DELETE, the following exception is thrown:

example_db=# DELETE FROM example_table WHERE time IN (SELECT time FROM example_table WHERE time < DATE '2021-01-3' LIMIT 5);
ERROR:  cannot update/delete rows from chunk "_hyper_1_3_chunk" as it is compressed

Looking into this further, _hyper_1_3_chunk doesn’t contain any data in the range of this delete query. The minimum time in this chunk is 2021-01-13 19:00:00-05, so we’re not really sure why this is throwing.

I’ve attached the steps to reproduce this below:

CREATE table example_table ( time TIMESTAMPTZ NOT NULL);

CREATE extension timescaledb;

SELECT create_hypertable('example_table', 'time');

ALTER TABLE example_table SET (timescaledb.compress);

INSERT INTO example_table SELECT * from generate_series(
        '2021-01-01',
    '2021-02-01', INTERVAL '1 hour');

SELECT compress_chunk(show_chunks('example_table'));

SELECT decompress_chunk(show_chunks('example_table', older_than => DATE '2021-01-15'));

DELETE FROM example_table WHERE time < DATE '2021-01-2'; -- this succeeds 
DELETE FROM example_table WHERE time IN (SELECT time FROM example_table WHERE time < DATE '2021-01-3' LIMIT 5); -- this fails

Any advice on how to achieve this LIMIT-ed DELETE query in timescaleDB would be greatly appreciated. Thanks!

2

Hi Eric :wave: welcome to the forum. Checking through some Slack chatter, it seems a common explanation is that you need to effectively delete between two dates i.e. also provide an appropriate FROM and a TO for the date range.

Could you maybe try that and report back? :pray:

3

Hey Lorraine, I appreciate your timely response.

Unfortunately, when I try adding a start time I get the same error:

example_db=# SELECT decompress_chunk(show_chunks('example_table', older_than => DATE '2021-01-15'));
            decompress_chunk            
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk
 _timescaledb_internal._hyper_1_2_chunk
example_db=# DELETE FROM example_table WHERE time in (SELECT time FROM example_table WHERE time > DATE '2021-01-01' AND time < DATE '2021-01-03' LIMIT 5);
ERROR:  cannot update/delete rows from chunk "_hyper_1_3_chunk" as it is compressed
example_db=# DELETE FROM example_table WHERE time > DATE '2021-01-01' AND time < DATE '2021-01-03' AND time IN (SELECT time FROM example_table WHERE time > DATE '2021-01-01' AND time < DATE '2021-01-03' LIMIT 5);
ERROR:  cannot update/delete rows from chunk "_hyper_1_3_chunk" as it is compressed

Thanks,

Eric

4

OK, no worries Eric, thank you for trying! I’ll ask one of our developer advocates to check in.

5

Hey @Eric_Chandrasekhar,

In essence, the issue here is query planning. When you do an explicit equals predicate on the date, TimescaleDB can do chunk exclusion as part of the planning because it knows the constraints of each chunk and can prevent the other chunks from being scanned.

With an IN clause/subquery, the planner cannot do any exclusion ahead of time, and plans/assumes that it will have to search through all chunks, which gives you the error. It can’t know until runtime what chunks the resulting subquery will return, so you get the error.

For your specific case, the simple solution is to create a more specific set of predicates so that chunk exclusion can be done first, and then the correct rows can be deleted.

delete FROM example_table WHERE time < '2021-01-03' AND time IN (SELECT time FROM example_table WHERE time < '2021-01-03' LIMIT 5);

This example :point_up: is similar to what we now do with the now() function when you use dynamic date match for a predicate. Previously, that was marked as a STABLE operation and wouldn’t be evaluated until runtime. Now, with TimescaleDB 2.7, it pre-calculates the date and adds it as a secondary predicate so that chunk exclusion can be performed first. (you can read all of the details in this blog post)

HTH

6

That DELETE query works great!

Thank you both for your help with this,

Eric Chandrasekhar

1 Like