Re: BUG #15954: Unable to alter partitioned table to set logged - Mailing list pgsql-bugs

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.rowley@2ndquadrant.com> wrote:

On Wed, 14 Aug 2019 at 15:42, Keith Fiske <keith.fiske@crunchydata.com> wrote:
>
>
>
> On Tue, Aug 13, 2019 at 11:17 PM David Rowley <david.rowley@2ndquadrant.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