Re: FK violation in partitioned table after truncating a referenced partition

There's another key point I forgot -- which is that we only need to
search for constraints on the topmost partitioned table, not each of its
partitions. The reason is that pg_constraint rows exist on the other
side that reference that relation, for each partition on the other side.
So we can do this:

+ if (HeapTupleIsValid(tuple))
+ {
+ Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tuple);
+
+ /*
+ * pg_constraint rows always appear for partitioned hierarchies
+ * this way: on the each side of the constraint, one row appears
+ * for each partition that points to the top-most table on the
+ * other side.
+ *
+ * Because of this arrangement, we can correctly catch all
+ * relevant relations by adding to 'parent_cons' all rows with
+ * valid conparentid, and to the 'oids' list all rows with a
+ * zero conparentid. If any oids are added to 'oids', redo the
+ * first loop above by setting 'restart'.
+ */
+ if (OidIsValid(con->conparentid))
+ parent_cons = list_append_unique_oid(parent_cons,
+ con->conparentid);
+ else if (!list_member_oid(oids, con->confrelid))
+ {
+ oids = lappend_oid(oids, con->confrelid);
+ restart = true;
+ }
+ }

that is, keep appending to the parent_cons list, and not touch the oids
list, until we get to the top of the hierarchy. Then when we redo the
first loop, we'll get all partitions on the other side because they all
have pg_constraint rows that reference the topmost rel. (That is to
say, all the intermediate-partition OIDs should be useless in the 'oids'
list anyway.)

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services