Обсуждение: notes from transition to relkind='p'
Before I forget any more, this is a brain of issues/considerations/concerns with our (partial) transition to partitioned tables over the last ~9 months. I believe these are all documented behaviors, but could be seen by users as a gratuitious/unexpected change or rough edge and the differences could perhaps be mitigated. I realize there's maybe no good time or way to change most of these, but maybe the list will be helpful to somebody in avoiding unexpected transitional issues. . DROP TABLE on a parent no longer fails without CASCADE (dependencies of relkind='p' are deptype='a' "soft" dependencies) 8b4d582d279d784616c228be58af1e39aa430402 . ANALYZE relkind_p also updates stats of child (whereas ANALYZE relkind_r only updates stats for parent); it's unclear if there's any reason why it wasn't always done this way(?). I end up having to conditionize processing based on relkind. 3c3bb99330aa9b4c2f6258bfa0265d806bf365c3 . The docs say: if detaching/re-attach a partition, should first ADD CHECK to avoid a slow ATTACH operation. Perhaps DETACHing a partition could implicitly CREATE a constraint which is usable when reATTACHing? . relkind_p has no entry in pg_stat_user_tables (last_analyze, etc). Maybe the view could do the needed CASE ... (SELECT min() FROM pg_inherits JOIN psut) . ALTER TABLE ATTACH requires specifying bounds: Maybe it sounds naive to suggest one would want to avoid that; but consider: we ended up adding both shell and python logic to parse the table name to allow detaching and reattaching partitions. I think it'd be a nice if the bounds were inferred if there was a single constraint on the partition key. . ALTER TABLE ATTACH has reversed order of child vs parent relative to NO/INHERIT. . And actually, having both ALTER TABLE DE/TACH vs NO/INHERIT is itself messy: we ended up having branches (both shell and python) to handle both cases (at least for a transitional period, but probably we'll need to continue handling both into the indeterminate future). Cheers, Justin
On 06/01/2018 03:14 PM, Justin Pryzby wrote: > Before I forget any more, this is a brain of issues/considerations/concerns > with our (partial) transition to partitioned tables over the last ~9 months. I > believe these are all documented behaviors, but could be seen by users as a > gratuitious/unexpected change or rough edge and the differences could perhaps > be mitigated. I realize there's maybe no good time or way to change most of > these, but maybe the list will be helpful to somebody in avoiding unexpected > transitional issues. It is not clear from above what you are transitioning from. From comments below you seem to be transitioning to the new syntax in version 10 as shown here: https://www.postgresql.org/docs/10/static/release-10.html#id-1.11.6.9.3 " E.5.3.4. Utility Commands Add table partitioning syntax that automatically creates partition constraints and handles routing of tuple insertions and updates (Amit Langote) The syntax supports range and list partitioning. " Is that the case? More comments in line below. > > . DROP TABLE on a parent no longer fails without CASCADE (dependencies of > relkind='p' are deptype='a' "soft" dependencies) > 8b4d582d279d784616c228be58af1e39aa430402 > . ANALYZE relkind_p also updates stats of child (whereas ANALYZE relkind_r > only updates stats for parent); it's unclear if there's any reason why it > wasn't always done this way(?). I end up having to conditionize processing > based on relkind. 3c3bb99330aa9b4c2f6258bfa0265d806bf365c3 Because there is a clear partition dependency in the declarative partitioning scheme. In the INHERIT scheme(which I assume is what you are talking about) the partitioning is optional. > . The docs say: if detaching/re-attach a partition, should first ADD CHECK to > avoid a slow ATTACH operation. Perhaps DETACHing a partition could > implicitly CREATE a constraint which is usable when reATTACHing? I personally would not like that. If I wanted to maintain the partition constraint I would keep the table in the partition. If I was DETACHing it then it would be to get rid of it or have it exist as a stand alone table. If I where to keep it deciding what constraints to maintain should be up to me. > . relkind_p has no entry in pg_stat_user_tables (last_analyze, etc). Maybe > the view could do the needed CASE ... (SELECT min() FROM pg_inherits JOIN psut) > . ALTER TABLE ATTACH requires specifying bounds: Maybe it sounds naive to > suggest one would want to avoid that; but consider: we ended up adding both > shell and python logic to parse the table name to allow detaching and > reattaching partitions. I think it'd be a nice if the bounds were inferred > if there was a single constraint on the partition key. The above I am not sure how you envision that working, especially the inferring the key part. Having the program guess at what I want a partition to be constrained by is something I would need fully explained to me. > . ALTER TABLE ATTACH has reversed order of child vs parent relative to > NO/INHERIT. Makes sense to me as they show the direction of operation. ATTACH is hooking a child table onto the parent table. INHERIT is a child table pulling from a parent table. > . And actually, having both ALTER TABLE DE/TACH vs NO/INHERIT is itself messy: > we ended up having branches (both shell and python) to handle both cases (at > least for a transitional period, but probably we'll need to continue > handling both into the indeterminate future). Well they are two different cases, so I am not sure they could be combined. > > Cheers, > Justin > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sun, Jun 03, 2018 at 12:13:49PM -0700, Adrian Klaver wrote: > On 06/01/2018 03:14 PM, Justin Pryzby wrote: > >Before I forget any more, this is a brain of issues/considerations/concerns Should have said brain DUMP Keep in mind, I've phrased these as notes, maybe useful to someone else, maybe not an issue to most people, and maybe not possible or worth changing behavior for, since these are already in last year's published release. > >with our (partial) transition to partitioned tables over the last ~9 months. I > >believe these are all documented behaviors, but could be seen by users as a > >gratuitious/unexpected change or rough edge and the differences could perhaps > >be mitigated. I realize there's maybe no good time or way to change most of > >these, but maybe the list will be helpful to somebody in avoiding unexpected > >transitional issues. > > It is not clear from above what you are transitioning from. Transitioning from inheritence based partitioning with empty relkind='r' parents to PG10 declarative partitioning. > > . ANALYZE relkind_p also updates stats of child (whereas ANALYZE relkind_r > > only updates stats for parent); it's unclear if there's any reason why it > > wasn't always done this way(?). I end up having to conditionize processing > > based on relkind. 3c3bb99330aa9b4c2f6258bfa0265d806bf365c3 > > Because there is a clear partition dependency in the declarative > partitioning scheme. In the INHERIT scheme(which I assume is what you are > talking about) the partitioning is optional. I see your point - but AFAICT there's no reason why ANALYZE relkind_r couldn't have always acted "recursively" on any children, or if there's any reason it couldn't conceivably have been changed in v10, for consistency, if that was determined to outweigh the disruptiveness/inconvenience of changing an existing behavior. > > . The docs say: if detaching/re-attach a partition, should first ADD CHECK to > > avoid a slow ATTACH operation. Perhaps DETACHing a partition could > > implicitly CREATE a constraint which is usable when reATTACHing? > > I personally would not like that. If I wanted to maintain the partition > constraint I would keep the table in the partition. If I was DETACHing it > then it would be to get rid of it or have it exist as a stand alone table. > If I where to keep it deciding what constraints to maintain should be up to > me. Noted; for us, the reasons we uninherit/detach (and then inherit/attach) are: - to avoid massive space use and long, disruptive table lock while executing a typo-promoting ALTER. - when ALTERing to a new tablespace, to avoid locking parent table, which is more disruptive than losing visibility of a partition's tuples; > > . relkind_p has no entry in pg_stat_user_tables (last_analyze, etc). Maybe > > the view could do the needed CASE ... (SELECT min() FROM pg_inherits JOIN psut) > > . ALTER TABLE ATTACH requires specifying bounds: Maybe it sounds naive to > > suggest one would want to avoid that; but consider: we ended up adding both > > shell and python logic to parse the table name to allow detaching and > > reattaching partitions. I think it'd be a nice if the bounds were inferred > > if there was a single constraint on the partition key. > > The above I am not sure how you envision that working, especially the > inferring the key part. Having the program guess at what I want a partition > to be constrained by is something I would need fully explained to me. I mean the partition bounds of an partition of a relkind='p' could (perhaps) be changed into a CHECK constraint at DETACH time, avoiding full table scan and long lock if it's later re-ATTACHED. If implemented, it could be requested rather than strictly implied.. ALTER TABLE p DETACH r ADD CONSTRAINT c; ... ALTER TABLE p ATTACH r FOR VALUES INFERRED BY CONSTRAINT c; -- must be a check constraint, must be a "simple" list or range with a single column, etc. Maybe not feasible for hash partitioning? Justin