[Bug]: Locking all the tables with shared foreign key when dropping chunks

[geri-blocktech]

What type of bug is this?

Locking issue

What subsystems and features are affected?

Configuration, Partitioning, User-Defined Action (UDA)

What happened?

When we try to drop chunks (with drop_chunk) from a table that has foreign key on a non-hyper table, postgresql locks all the other tables that also has the same foreign key. Since we are inserting continuously to many of these related tables, postgres waits until all these inserts are done, then it drops the chunks, and only after that it can start inserting to all these related tables again that makes significant delays and potential data loss.

The issue also persist when we try to add a retention policy making this problem difficult to solve for us unless we drop the foreign key.

I do see that postgresql already have a similar problem when it has shared foreign keys towards one table, as dropping a table creates these same locks. However, I think this could be solved if drop_chunk function would first drop the foreign keys before dropping the underlying chunk.

TimescaleDB version affected

2.17.1

PostgreSQL version used

14.13

What operating system did you use?

Debian 12

What installation method did you use?

Deb/Apt

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

-

How can we reproduce the bug?

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL
);

INSERT INTO products (product_name) VALUES
('Product A'),
('Product B'),
('Product C'),


CREATE TABLE orders (
    product_id INT NOT NULL REFERENCES products(id),
    ts TIMESTAMPTZ NOT NULL,
    customer_id INT NOT NULL
);

SELECT create_hypertable('orders', 'ts', chunk_time_interval := INTERVAL '1 day');


CREATE TABLE storage (
    product_id INT NOT NULL REFERENCES products(id),
    ts TIMESTAMPTZ NOT NULL
);

SELECT create_hypertable('storage', 'ts', chunk_time_interval := INTERVAL '1 day');




CREATE OR REPLACE FUNCTION insert_random_orders(n INT)
RETURNS VOID AS $$
BEGIN
 
    INSERT INTO orders (product_id, ts, customer_id)
    SELECT 
        (SELECT id FROM products ORDER BY RANDOM() LIMIT 1), -- Random product_id
        NOW() - (random() * interval '1 days'),             -- Random timestamp within the last 1 days
        trunc(random() * 100 + 1)::INT                       -- Random customer_id (1 to 100)
    FROM generate_series(1, n);
END;
$$ LANGUAGE plpgsql;


-- while running big insert statement
SELECT insert_random_orders(5000000);



--from another connection we try to drop (not even existing) chunks
SELECT drop_chunks(
    'storage', 
    newer_than => '2024-01-01', 
    older_than => '2024-01-05');


-- from a third connection we can see the locks 
     select relation::regclass, * from pg_locks where not granted;

[erimatnor]

@geri-blocktech can you explain in more detail how you think this issue would be solved by first dropping the foreign keys? I guess that also requires taking locks. Can you test it in a multi-statement transaction?

[geri-blocktech]

hmm indeed dropping the foreign key also creates a lock. But what is the reason for this? And is there any workaround if we want to keep the foreign key?

[geri-blocktech]

We just figured out that compression works actually on these same hypertables, which was very surprising to me. I assumed you would use the same drop_chunk functionality to remove the chunks once compression finished so I expected the same locks. Couldn't you use the same trick for drop_chunk which you used to make compression work?
@erimatnor