Re: notes from transition to relkind='p' - Mailing list pgsql-general

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