On Wed, Apr 08, 2020 at 11:44:33AM -0500, Justin Pryzby wrote: > On Wed, Apr 08, 2020 at 12:02:39PM -0400, Alvaro Herrera wrote: > > On 2020-Apr-08, Justin Pryzby wrote: > > > > > This seems to be a bug in master, v12, and (probably) v11, where "FOR > > > EACH FOR" > > > was first allowed on partition tables (86f575948). > > > > > > I thought this would work like partitioned indexes (8b08f7d48), where > > > detaching > > > a partition makes its index non-inherited, and attaching a partition > > > marks a > > > pre-existing, matching partition as inherited rather than creating a new > > > one. > > > > Hmm. Let's agree to what behavior we want, and then we implement that. > > It seems to me there are two choices: > > > > 1. on detach, keep the trigger but make it independent of the trigger on > > parent. (This requires that the trigger is made dependent on the > > trigger on parent, if the table is attached as partition again; > > otherwise you'd end up with multiple copies of the trigger if you > > detach/attach multiple times). > > > > 2. on detach, remove the trigger from the partition. > > > > I think (2) is easier to implement, but (1) is the more convenient > > behavior. > > At telsasoft, we don't care (we uninherit tables before ALTERing parents to > avoid disruptive locking and to avoid worst-case disk use).
I realized that I was wrong about what would be most desirable for us, for an uncommon case: Our loader INSERTs into the child table, not the parent (I think I did that to try to implement UPSERT before partitioned indexes in v11). All but the newest partitions are DETACHed when we need to promote a column. It's probably rare that we'd be inserting into a table old enough to be detached, and normally that would be ok, but if a trigger were missing, it would misbehave. In our use-case, we're creating trigger on the parent as a convenient way to maintain them on the partitions, which doesn't work if a table exists but detached.. So we'd actually prefer the behavior of indexes/constraints, where the trigger is preserved if the child is detached. I'm not requesting to do that just for our use case, which may be atypical or not a good model, but adding our one data point. -- Justin
