Re: Partitioned tables and [un]loggedness

On Wed, Apr 24, 2024 at 03:36:35PM -0700, David G. Johnston wrote:
> 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:
>>> - 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.

I disagree here, actually.  Temporary tables are a different beast
because they require automated cleanup which would include interacting
with the partitionining information if temp and non-temp relations are
mixed.  That's why the current restrictions are in place: you should
be able to mix them.  Mixing unlogged and logged is OK, they retain a
state in their catalogs.

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

It depends on retention policies, for one.  I could imagine an
application where partitioning is used based on a key where we
classify records based on their persistency, and one does not care
about a portion of them, still would like some retention as long as
the application is healthy.

> 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

While we have keywords to drive that at query level for TEMP and
UNLOGGED?  Not sure to be on board with this idea.  pg_dump may need
some changes to reflect the loggedness across the partitions, now that
I think about it even if there should be an ATTACH once the table is
created to link it to its partitioned table.  There should be no
rewrites at restore.

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

Yeah, at least it looks like Nathan and you are OK with this addition.
--
Michael