Re: Partitioned tables and [un]loggedness
| От | David G. Johnston |
|---|---|
| Тема | Re: Partitioned tables and [un]loggedness |
| Дата | |
| Msg-id | CAKFQuwY3RTTrp7x4OmEruMmOghdorhq4=eC0ZfG0NM+HnBWFsw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Partitioned tables and [un]loggedness (Nathan Bossart <nathandbossart@gmail.com>) |
| Ответы |
Re: Partitioned tables and [un]loggedness
(Michael Paquier <michael@paquier.xyz>)
|
| Список | pgsql-hackers |
On Wed, Apr 24, 2024 at 1:26 PM Nathan Bossart <nathandbossart@gmail.com> 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?
> - 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.
I don't see this being desirable at this point. And especially not by itself. It is an error to not specify TEMP on the partition create table command when the parent is temporary, and that one is a no-brainer for having the persistence mode of the child be derived from the parent. The current policy of requiring the persistence of the child to be explicitly specified seems perfectly reasonable. Or, put differently, the specific current persistence of the parent doesn't get copied or even considered when creating children.
In any case we aren't going to be able to do exactly what it means by marking a partitioned table unlogged - namely that we execute the truncate command on it after a crash. Forcing the implementation here just so that our existing decision to ignore unlogged on the parent table is, IMO, letting optics corrupt good design.
I do agree with having an in-core way for the DBA to communicate that they wish for partitions to be created with a known persistence unless the create table command specifies something different. The way I would approach this is to add something like the following to the syntax/system:
CREATE [ persistence_mode ] TABLE ...
...
WITH (partition_default_persistence = { logged, unlogged, temporary }) -- storage_parameter, logged is effectively the default
This method is more explicit and has zero implications for existing backups or upgrading.
> - 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?
I'd leave it to the community to develop and maintain scripts that iterate over the partition hierarchy and toggle the persistence mode between logged and unlogged on the individual partitions using whatever throttling and batching strategy each individual user requires for their situation.
> - 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 do agree with adding LOGGED to the list of optional persistence_mode specifiers, possibly regardless of whether any of this happens. Seems desirable to give our default mode a name.
David J.
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Nathan BossartДата:
Сообщение: Re: Extend ALTER DEFAULT PRIVILEGES for large objects