Re: Partitioned tables and [un]loggedness
От | Michael Paquier |
---|---|
Тема | Re: Partitioned tables and [un]loggedness |
Дата | |
Msg-id | ZimRI2hrjGgn9x8O@paquier.xyz обсуждение исходный текст |
Ответ на | Re: Partitioned tables and [un]loggedness (Nathan Bossart <nathandbossart@gmail.com>) |
Список | pgsql-hackers |
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
Вложения
В списке pgsql-hackers по дате отправления: