Re: Partitioned tables and [un]loggedness

On Wed, Apr 24, 2024 at 03:26:40PM -0500, Nathan Bossart wrote:
> 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?

The POC implements ALTER TABLE .. SET LOGGED/UNLOGGED so as the change
only reflects on the relation defined on the query.  In short, if
specifying a partitioned table as relation, only its relpersistence is
changed in the patch.  If sub-partitioning is involved, the POC
behaves the same way, relpersistence for partitioned table A1 attached
to partitioned table A does not change under ALTER TABLE A.

Recursing to all the partitions and partitioned tables attached to a
partitioned table A when using an ALTER TABLE A, when ONLY is not
specified, is OK by me if that's the consensus folks prefer.  I just
wanted to gather some opinions first about what people find the most
natural.

>> - 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.

Noted.  That's a second piece.  This part felt natural to me, but it
would not fly far without a LOGGED keyword and a way to attach a new
"undefined" RELPERSISTENCE_ in gram.y's OptTemp, likely a '\0'.
That's going to require some tweaks for CTAS as these cannot be
partitioned, but that should be a few lines to fall back to a
permanent if the query is parsed with the new "undefined".

>> - 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...

Yeah.  A burst of WAL caused by a switch to LOGGED for a few thousand
partitions would never be fun, perhaps I'm just worrying to much as
that should not be a regular operation.

>> - 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?

I don't see why not if people agree with it, that's a patch of its own
that would help greatly in making the [un]logged attribute be
inherited for new partitions, because it is them possible to force a
persistence to be permanent as much as unlogged at query level, easing
the manipulation of partition trees.
--
Michael