Thread: 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