[Jehan-Guillaume de Rorthais <jgdr@dalibo.com>]

Hello,

A colleague found the bug described in $subject while testing partitioning.

Consider two partitioned tables with a FK between them. Issuing a TRUNCATE
CASCADE on a *partition* of the referenced side does not propagate to the
referencing side. Eg.:

  CREATE TABLE trunc_a (a INT PRIMARY KEY) PARTITION BY RANGE (a);
  CREATE TABLE trunc_a1 PARTITION OF trunc_a FOR VALUES FROM (0) TO (10);
  CREATE TABLE trunc_a2 PARTITION OF trunc_a FOR VALUES FROM (10) TO (20);
  INSERT INTO trunc_a VALUES (0), (5), (10), (15);

  CREATE TABLE ref_b (
    b INT PRIMARY KEY,
    a INT REFERENCES trunc_a(a) ON DELETE CASCADE
  ) PARTITION BY RANGE (b);
  CREATE TABLE ref_b1 PARTITION OF ref_b FOR VALUES FROM (0) TO (100);
  INSERT INTO ref_b VALUES (10, 0), (50, 5);

  TRUNCATE TABLE trunc_a1 CASCADE;
  -- NOTICE:  truncate cascades to table "ref_b"

  SELECT a FROM trunc_a;
  --  a  
  -- ----
  --  10
  --  15
  -- (2 rows)

  SELECT a FROM ref_b;
  --  a 
  -- ---
  --  0
  --  5
  -- (2 rows)

heap_truncate_find_FKs returns only relations that are directly referencing
the given referenced part of the FK. However, when considering two partitioned
relation with a FK between them, there's no child to child relation in
pg_constraint. They only point toward parent tables. Cascading FK is indirectly
done through the parent table.

Unfortunately, in ExecuteTruncateGuts, when relations are actually
truncated, parents are ignored as they are empty relations:

	/*
	 * OK, truncate each table.
	 */
	mySubid = GetCurrentSubTransactionId();

	foreach(cell, rels)
	{
		Relation	rel = (Relation) lfirst(cell);

		/* Skip partitioned tables as there is nothing to do */
		if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
			continue;

Please, find in attachment a bug fix proposal where a truncate on a
partition cascade to the referencing table and truncate all its
partitions if applicable.

The patch make sure heap_truncate_find_FKs find all referencing relations,
directly **and indirectly** through their parent table.

When considering the various way of fixing this, I thought about calling
find_all_inheritors on all relations returned by heap_truncate_find_FKs to add
them to the list or relation to truncate (I have a working patch for this as
well). However, I felt like heap_truncate_find_FKs was the real suspect here
and was responsible to find all referencing relations.

Regards,

Attachments:

  [text/x-patch] 0001-v1-Fix-TRUNCATE-on-a-partition-to-cascade-correctly-to-.patch (0B, ../20200204183906.115f693e@firost/2-0001-v1-Fix-TRUNCATE-on-a-partition-to-cascade-correctly-to-.patch)
  download