Re: Partitioned tables and [un]loggedness

On Wed, Apr 24, 2024 at 04:17:44PM +0900, Michael Paquier wrote:
> - Support ALTER TABLE .. SET LOGGED/UNLOGGED for partitioned tables,
> where the command only works on partitioned tables so that's only a
> catalog switch.

I'm not following what this means. Does SET [UN]LOGGED on a partitioned
table recurse to its partitions? Does this mean that you cannot changed
whether a single partition is [UN]LOGGED? How does this work with
sub-partitioning?

> - CREATE TABLE PARTITION OF would make a new partition inherit the
> logged ness of the parent if UNLOGGED is not directly specified.

This one seems generally reasonable to me, provided it's properly noted in
the docs.

> - How about ONLY? Would it make sense to support it so as ALTER TABLE
> ONLY on a partitioned table does not touch any of its partitions?
> Would not specifying ONLY mean that the loggedness of the partitioned
> table and all its partitions is changed? That would mean a burst in
> WAL when switching to LOGGED, which was a concern when this feature
> was first implemented even for a single table, so for potentially
> hundreds of them, that would really hurt if a DBA is not careful.

I guess ONLY could be a way of changing the default for new partitions
without changing whether existing ones were logged. I'm not tremendously
concerned about the burst-of-WAL problem. Or, at least, I'm not any more
concerned about it for partitioned tables than I am for regular tables. I
do wonder if we can improve the performance of setting tables LOGGED, but
that's for a separate thread...

> - CREATE TABLE does not have a LOGGED keyword, hence it is not
> possible through the parser to force a partition to have a permanent
> persistence even if its partitioned table uses UNLOGGED.

Could we add LOGGED for CREATE TABLE?

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com