Hi all,
As a recent poke on a thread of 2019 has reminded me, the current
situation of partitioned tables with unlogged is kind of weird:
https://www.postgresql.org/message-id/15954-b61523bed4b110c4%40postgresql.org
To sum up the situation:
- ALTER TABLE .. SET LOGGED/UNLOGGED does not affect partitioned
tables.
- New partitions don't inherit the loggedness of the partitioned
table.
One of the things that could be done is to forbid the use of UNLOGGED
in partitioned tables, but I'm wondering if we could be smarter than
that to provide a more natural user experience. I've been
experiencing with that and finished with the POC attached, that uses
the following properties:
- Support ALTER TABLE .. SET LOGGED/UNLOGGED for partitioned tables,
where the command only works on partitioned tables so that's only a
catalog switch.
- CREATE TABLE PARTITION OF would make a new partition inherit the
logged ness of the parent if UNLOGGED is not directly specified.
There are some open questions that need attention:
- 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.
- 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.
Like tablespaces or even recently access methods, the heuristics can
be fun to define depending on the impact of the table rewrites. The
attached has the code and regression tests to make the rewrites
cheaper, which is to make new partitions inherit the loggedness of the
parent while altering a parent's property leaves the partitions
untouched. With the lack of a LOGGED keyword to force a partition to
be permanent when its partitioned table is unlogged, that's a bit
funny but feel free to comment.
Thanks,
--
Michael