From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: FK violation in partitioned table after truncating a referenced partition
Date: Fri, 7 Feb 2020 17:19:48 -0300
Message-ID: <20200207201948.GA16783@alvherre.pgsql> (raw)
In-Reply-To: <20200207192257.1145eadd@firost>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: alvherre@2ndquadrant.com, jgdr@dalibo.com, pgsql-bugs@lists.postgresql.org
Subject: Re: FK violation in partitioned table after truncating a referenced partition
In-Reply-To: <20200207201948.GA16783@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox