Truncate partitions with foreign key to a different partitioned table
0
votes
1
answer
60
views
I am using PostgreSQL with two tables, called records and flags, that are partitioned exactly the same -- by range. I want to truncate old partitions in both.
We use pg_partman, and they are created as so (toy example):
CREATE TABLE logging.records (
id BIGSERIAL,
record VARCHAR,
PRIMARY KEY (id)
) PARTITION BY RANGE (id);
CREATE TABLE logging.records_template (LIKE logging.records);
ALTER TABLE logging.records_template ADD PRIMARY KEY (id);
SELECT logging.create_parent(
p_parent_table := 'logging.records'
, p_control := 'id'
, p_interval := '10'
, p_premake := '20'
, p_default_table := false
, p_template_table := 'logging.records_template');
CREATE TABLE logging.flags(
id BIGSERIAL PRIMARY KEY,
record_id BIGSERIAL,
archived BOOLEAN,
FOREIGN KEY (record_id) REFERENCES logging.records(id) ON DELETE CASCADE
) PARTITION BY RANGE (id);
SELECT logging.create_parent(
p_parent_table := 'logging.flags'
, p_control := 'id'
, p_interval := '10'
, p_premake := '20'
, p_default_table := false);
If I:
TRUNCATE logging.records_p0;
I get the error:
> ERROR: cannot truncate a table referenced by a foreign key constraint
If I use CASCADE
, then it truncates all partitioned flags, which I do not want.
If I:
TRUNCATE logging.flags_p0;
it works. But of course that leaves logging.records full of stuff.
If I:
ALTER TABLE logging.records DETACH PARTITION logging.records_p0 CONCURRENTLY;
I get the error:
> ERROR: removing partition "records_p0" violates foreign key constraint "flags_record_id_fkey2"
If I:
ALTER TABLE logging.flags DETACH PARTITION logging.flags_p0 CONCURRENTLY;
ALTER TABLE logging.records DETACH PARTITION logging.records_p0 CONCURRENTLY;
TRUNCATE logging.flags_p0;
TRUNCATE logging.records_p0;
it works. However, at this point I may as well just drop the tables instead of truncating them since they won't be used again. But I have the deadlock issue with detaching, because while this is all going on, there is heavy activity with other partitions getting inserts and updates.
In my ideal world I could just truncate with cascade and it would just wipe out the partition in records and the corresponding one in flags and not have to detach.
Is there another way?
Thank you
Asked by zhackwyatt
(3 rep)
Jul 3, 2025, 07:25 PM
Last activity: Jul 7, 2025, 11:28 PM
Last activity: Jul 7, 2025, 11:28 PM