Re: notes from transition to relkind='p'

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