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@xxxxxxxxxxx
