Re: BUG #15954: Unable to alter partitioned table to set logged

On Wed, Aug 14, 2019 at 8:46 AM Efrain J. Berdecia <ejberdecia(at)yahoo(dot)com>
wrote:

> How about a compromise;
>
> Allow us to ALTER the properties of the parent table without any
> expectation of a "cascading" effect UNLESS we use the already established
> CASCADE option.
>
> So for example, if I create a parent table the "regular" way, I can still
> specify at the time of creating the underlying partitions weather I want
> those to be created with any added properties or options; regardless of how
> the parent looks like. Is okay for tools like pg_partman to let the users
> know that the tool will use the parent table as the "template" when
> creating the partitions.
>
> But, if I ALTER any property of the parent table those properties will
> only affet the parent table UNLESS I use the CASCADE option at which point
> it will apply the property changes to all the underlying partitions.
>
> It would look something like this;
>
> ALTER TABLE parent_table SET UNLOGGED; -- this will only change the
> properties on the parent table
>
> ALTER TABLE parent_table SET UNLOGGED CASCADE; -- this would populate the
> change throughout the partition set and to all the partitions
>
> Thanks,
> Efrain J. Berdecia
>
>
> On Wednesday, August 14, 2019, 01:32:22 AM EDT, David Rowley <
> david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>
>
> On Wed, 14 Aug 2019 at 15:42, Keith Fiske <keith(dot)fiske(at)crunchydata(dot)com>
> wrote:
> >
> >
> >
> > On Tue, Aug 13, 2019 at 11:17 PM David Rowley <
> david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> >> If we allow SET [UN]LOGGED on a partitioned table to cascade down to
> >> each partition, then do we need to insist that the child partition's
> >> persistence setting does not deviate from the parents? Or would
> >> altering the parent just change the partitions that were not already
> >> set that way?
> >>
> >> What would the behaviour be of doing ATTACH PARTITION on a logged
> >> table onto an unlogged partitioned table?
> >>
> >> Also, since there is no CREATE LOGGED TABLE syntax, what would users
> >> do if that wanted to create a logged partition on an unlogged
> >> partition hierarchy? For this to work ATTACH PARTITION would have to
> >> not mess with the persistence setting but the user would have to
> >> CREATE TABLE ... (LIKE partitioned_table); then ATTACH PARTITION.
> >> That seems a bit messy to me, it's inevitable that someone would
> >> eventually complain and ask for a CREATE LOGGED TABLE syntax.
> >>
> >> I think if we don't allow mixed persistence partition hierarchies
> >> we'll get complaints. I think it's valid to have them, just imagine
> >> implementing a highspeed queue that does not require durability on
> >> non-processed items. Processing an item updates the "processed" flag
> >> which moves the tuple into a logged partition, thus making it durable.
> >>
> >> So my thoughts are that unless someone is proposing to think of all
> >> the corner cases for partitions inheriting their persistence from
> >> their partitioned table, then allowing UNLOGGED partitioned tables is
> >> busted.
> >>
> >
> >
> > To me it seems that if someone sets the UNLOGGED status on the parent,
> that should indicate what the child state should be. Same as nearly every
> other feature of the partition set (indexes, constraints, defaults, etc).
> If someone wants to change the child tables later to be in a different
> state, that's fine. But there's got to be some sort of sane configuration
> defaults here for what state a child table should be in when it's
> immediately attached to a parent upon creation.
>
> If you think it should work this way, then it would be good if you
> could chime in with how you think it should work exactly. I pointed
> out a series of problems above. Adding your +1 to mention you want
> such a feature does not really help to work out exactly how those
> problems should be solved.
>
> Also, keep in mind there's a bunch of other things that we don't
> inherit from the partitioned table; reloptions, for one, tableam is
> another. Are those broken/missing features too? I'm unsure where the
> line should be drawn.
>
> How we determine a tablespace for a partition is a pretty good example
> of how complex the semantics of such inheritance can be. Alvaro worked
> pretty hard to make that work in a reasonable way. Some people still
> think it's unreasonable.
>
>
> --
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

So in the mean time I've added a fix to v4.2.0 of pg_partman to have it get
the unlogged status of the partition set to the template table system I've
put in place to handle inheritance properties not handled by native
(primary/unique keys, etc).

As the others have stated, I think we just need to get some sort of
consistent method of handling this. Currently running an ALTER TABLE on the
parent to change the UNLOGGED state simply does nothing which is not
intuitive whatsoever. Even if it's just throwing an error saying you cannot
change this property, that would be better until a more thorough solution
can be implemented in the future.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com