0

I am building a datawarehouse using Postgresql (12) and think I should be using partitions on the most populated tables, for performance and maintanability reasons.

I have something along the lines of:

CREATE TABLE sale (id varchar,
                    vers varchar,
                    CONSTRAINT sale_pkey PRIMARY KEY (id, vers)
                    )
                   PARTITION BY LIST (vers);

CREATE TABLE sale_line (id varchar,
                    sale_id varchar,
                    vers varchar,
                    CONSTRAINT sale_line_pkey PRIMARY KEY (id, vers),
                    CONSTRAINT sale_fk FOREIGN KEY (sale_id, vers) REFERENCES sale(id, vers)
                    )
                   PARTITION BY LIST (vers);

CREATE TABLE sale_v1 PARTITION OF sale FOR VALUES IN ('v1');
CREATE TABLE sale_line_v1 PARTITION OF sale_line FOR VALUES IN ('v1');
CREATE TABLE sale_v2 PARTITION OF sale FOR VALUES IN ('v2');
CREATE TABLE sale_line_v2 PARTITION OF sale_line FOR VALUES IN ('v2');

Now, I want to get rid of data in version v2, and I try this:

truncate sale_line_v2, sale_v2;

and get this error:

SQL Error [0A000]: ERROR: cannot truncate a table referenced in a foreign key constraint
  Détail : Table "sale_line" references "sale_v2".
  Indice : Truncate table "sale_line" at the same time, or use TRUNCATE ... CASCADE.

Notice that using 'truncate ... cascade' is not an option. As stated in the documentation (and experimented the hard way), it will also truncate my version v1:

Note in particular that when the table to be truncated is a partition, siblings partitions are left untouched, but cascading occurs to all referencing tables and all their partitions with no distinction.

I ahve seen similar questions on this site (truncate table with foreign keys, Truncate tables with dependent foreign key constraints), but no answer working for me. The best approach would be disabling the constraints, but this does NOT work:

BEGIN;
ALTER TABLE sale_line disable TRIGGER ALL;
TRUNCATE sale_v2, sale_line_v2;
ALTER TABLE sale_line enable TRIGGER ALL;
COMMIT;

results again in:

SQL Error [0A000]: ERROR: cannot truncate a table referenced in a foreign key constraint
  Détail : Table "sale_line" references "sale_v2".
  Indice : Truncate table "sale_line" at the same time, or use TRUNCATE ... CASCADE.

I also tried disabling contraints on the partitions I want to truncate in addition to the main table, or even on all partitions and the main table, up to the above, but the result is always the same...

BEGIN;
ALTER TABLE sale_line disable TRIGGER ALL;
ALTER TABLE sale disable TRIGGER ALL;
ALTER TABLE sale_line_v1 disable TRIGGER ALL;
ALTER TABLE sale_line_v2 disable TRIGGER ALL;
ALTER TABLE sale_v1 disable TRIGGER ALL;
ALTER TABLE sale_v2 disable TRIGGER ALL;
TRUNCATE sale_v2, sale_line_v2;
ALTER TABLE sale_line enable TRIGGER ALL;
ALTER TABLE sale enable TRIGGER ALL;
ALTER TABLE sale_line_v1 enable TRIGGER ALL;
ALTER TABLE sale_line_v2 enable TRIGGER ALL;
ALTER TABLE sale_v1 enable TRIGGER ALL;
ALTER TABLE sale_v2 enable TRIGGER ALL;
COMMIT;

SQL Error [0A000]: ERROR: cannot truncate a table referenced in a foreign key constraint
  Détail : Table "sale_line" references "sale_v2".
  Indice : Truncate table "sale_line" at the same time, or use TRUNCATE ... CASCADE.

So contraints regarding partitions must have some sort of special status... but this defeats the purpose of being able to truncate partitions...

Any hint ?

3
  • 1
    Drop and re-create the constraint. May 6 '20 at 12:39
  • @LaurenzAlbe any idea why disable trigger does not work ?
    – alci
    May 7 '20 at 6:34
  • 1
    The reason is that PostgreSQL first checks if there are any triggers (not only enabled ones) on the table, and if yes, it checks pg_constraint for foreign key constraints (see heap_truncate_check_FKs in src/backend/catalog/heap.c). May 7 '20 at 7:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.