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

On Tue, Aug 13, 2019 at 11:17 PM David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> On Wed, 14 Aug 2019 at 14:48, Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> >
> > On Wed, Aug 14, 2019 at 01:57:26PM +1200, David Rowley wrote:
> > > I'd say it's broken in a sense that we can create an unlogged
> > > partitioned table in the first place. I think that should have been
> > > blocked.
> >
> > I don't think that it is that crazy to be able to define a partitioned
> > table as unlogged, assuming that we could use that to make the
> > children inherit the same state.
>
> Since we have no persistence inheritance feature, what are you proposing
> here?
>
> 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.
>
> --
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
>
>

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.

And as I replied before, this is an assumption I made in pg_partman's
feature support well before native partitioning was implemented. I check
the status of the parent table and upon child table creation, I set it to
be either logged or unlogged depending on the parent state. The way things
are now, this is broken and an unlogged partition set can never be
permanently made into a logged on since the parent will never be able to
have that state. All new child tables will continue to be unlogged.

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