Re: Partitioned tables and [un]loggedness

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

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.

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

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

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Wed, Apr 24, 2024 at 1:26 PM Nathan Bossart <nathandbossart(at)gmail(dot)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.

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

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(at)gmail(dot)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

On Wed, Apr 24, 2024 at 4:35 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:

>
> 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
>
[ not ] ?

> be able to mix them.
>

My point is that if you feel that treating logged as a copy-able property
is OK then doing the following should also just work:

postgres=# create temp table parentt ( id integer ) partition by range (id);
CREATE TABLE
postgres=# create table child10t partition of parentt for values from (0)
to (9);
ERROR: cannot create a permanent relation as partition of temporary
relation "parentt"

i.e., child10t should be created as a temporary partition under parentt.

David J.

On Thu, Apr 25, 2024 at 08:35:32AM +0900, Michael Paquier wrote:
> That's why the current restrictions are in place: you should
> be able to mix them.

Correction due to a ENOCOFFEE: you should *not* be able to mix them.
--
Michael

On Wed, Apr 24, 2024 at 04:43:58PM -0700, David G. Johnston wrote:
> My point is that if you feel that treating logged as a copy-able property
> is OK then doing the following should also just work:
>
> postgres=# create temp table parentt ( id integer ) partition by range (id);
> CREATE TABLE
> postgres=# create table child10t partition of parentt for values from (0)
> to (9);
> ERROR: cannot create a permanent relation as partition of temporary
> relation "parentt"
>
> i.e., child10t should be created as a temporary partition under parentt.

Ah, indeed, I've missed your point here. Lifting the error and
inheriting temporary in this case would make sense.
--
Michael

On Thu, Apr 25, 2024 at 08:55:27AM +0900, Michael Paquier wrote:
> On Wed, Apr 24, 2024 at 04:43:58PM -0700, David G. Johnston wrote:
>> My point is that if you feel that treating logged as a copy-able property
>> is OK then doing the following should also just work:
>>
>> postgres=# create temp table parentt ( id integer ) partition by range (id);
>> CREATE TABLE
>> postgres=# create table child10t partition of parentt for values from (0)
>> to (9);
>> ERROR: cannot create a permanent relation as partition of temporary
>> relation "parentt"
>>
>> i.e., child10t should be created as a temporary partition under parentt.
>
> Ah, indeed, I've missed your point here. Lifting the error and
> inheriting temporary in this case would make sense.

The case of a temporary persistence is actually *very* tricky. The
namespace, where the relation is created, is guessed and locked with
permission checks done based on the RangeVar when the CreateStmt is
transformed, which is before we try to look at its inheritance tree to
find its partitioned parent. So we would somewhat need to shortcut
the existing RangeVar lookup and include the parents in the loop to
find out the correct namespace. And this is much earlier than now.
The code complexity is not trivial, so I am getting cold feet when
trying to support this case in a robust fashion. For now, I have
discarded this case and focused on the main problem with SET LOGGED
and UNLOGGED.

Switching between logged <-> unlogged does not have such
complications, because the namespace where the relation is created is
going to be the same. So we won't lock or perform permission checks
on an incorrect namespace.

The addition of LOGGED makes the logic deciding how the loggedness of
a partition table based on its partitioned table or the query quite
easy to follow, but this needs some safety nets in the sequence, view
and CTAS code paths to handle with the case where the query specifies
no relpersistence.

I have also looked at support for ONLY, and I've been surprised that
it is not that complicated. tablecmds.c has a ATSimpleRecursion()
that is smart enough to do an inheritance tree lookup and apply the
rewrites where they should happen in the step 3 of ALTER TABLE, while
handling ONLY on its own. The relpersistence of partitioned tables is
updated in step 2, with the catalog changes.

Attached is a new patch series:
- 0001 refactors some code around ATPrepChangePersistence() that I
found confusing after applying the operation to partitioned tables.
- 0002 adds support for a LOGGED keyword.
- 0003 expands ALTER TABLE SET [UN]LOGGED to partitioned tables,
without recursion to partitions.
- 0004 adds the recursion logic, expanding regression tests to show
the difference.

0003 and 0004 should be merged together, I think. Still, splitting
them makes reviews a bit easier.
--
Michael

On Thu, May 02, 2024 at 03:06:51PM +0900, Michael Paquier wrote:
> The case of a temporary persistence is actually *very* tricky. The
> namespace, where the relation is created, is guessed and locked with
> permission checks done based on the RangeVar when the CreateStmt is
> transformed, which is before we try to look at its inheritance tree to
> find its partitioned parent. So we would somewhat need to shortcut
> the existing RangeVar lookup and include the parents in the loop to
> find out the correct namespace. And this is much earlier than now.
> The code complexity is not trivial, so I am getting cold feet when
> trying to support this case in a robust fashion. For now, I have
> discarded this case and focused on the main problem with SET LOGGED
> and UNLOGGED.
>
> Switching between logged <-> unlogged does not have such
> complications, because the namespace where the relation is created is
> going to be the same. So we won't lock or perform permission checks
> on an incorrect namespace.

I've been thinking about this thread some more, and I'm finding myself -0.5
for adding relpersistence inheritance for UNLOGGED. There are a few
reasons:

* Existing partitioned tables may be marked UNLOGGED, and after upgrade,
new partitions would be UNLOGGED unless the user discovers that they need
to begin specifying LOGGED or change the persistence of the partitioned
table. I've seen many problems with UNLOGGED over the years, so I am
wary about anything that might increase the probability of someone using
it accidentally.

* I don't think partitions inheriting persistence is necessarily intuitive.
IIUC there's nothing stopping you from having a mix of LOGGED and
UNLOGGED partitions, so it's not clear to me why we should assume that
users want them to be the same by default. IMHO UNLOGGED is dangerous
enough that we really want users to unambiguously indicate that's what
they want.

* Inheriting certain persistences (e.g., UNLOGGED) and not others (e.g.,
TEMPORARY) seems confusing. Furthermore, if a partitioned table is
marked TEMPORARY, its partitions must also be marked TEMPORARY. There is
no such restriction when a partitioned table is marked UNLOGGED.

My current thinking is that it would be better to disallow marking
partitioned tables as LOGGED/UNLOGGED and continue to have users explicitly
specify what they want for each partition. It'd still probably be good to
expand the documentation, but a clear ERROR when trying to set a
partitioned table as UNLOGGED would hopefully clue folks in.

--
nathan

On Tue, Aug 27, 2024 at 04:01:58PM -0500, Nathan Bossart wrote:
> I've been thinking about this thread some more, and I'm finding myself -0.5
> for adding relpersistence inheritance for UNLOGGED. There are a few
> reasons:
>
> * Existing partitioned tables may be marked UNLOGGED, and after upgrade,
> new partitions would be UNLOGGED unless the user discovers that they need
> to begin specifying LOGGED or change the persistence of the partitioned
> table. I've seen many problems with UNLOGGED over the years, so I am
> wary about anything that might increase the probability of someone using
> it accidentally.
>
> * I don't think partitions inheriting persistence is necessarily intuitive.
> IIUC there's nothing stopping you from having a mix of LOGGED and
> UNLOGGED partitions, so it's not clear to me why we should assume that
> users want them to be the same by default. IMHO UNLOGGED is dangerous
> enough that we really want users to unambiguously indicate that's what
> they want.

Okay. Thanks for sharing an opinion.

> * Inheriting certain persistences (e.g., UNLOGGED) and not others (e.g.,
> TEMPORARY) seems confusing. Furthermore, if a partitioned table is
> marked TEMPORARY, its partitions must also be marked TEMPORARY. There is
> no such restriction when a partitioned table is marked UNLOGGED.

The reason for temporary tables is different though: we expect
everything to be gone once the backend that created these relations is
gone. If persistence cocktails were allowed, the worse thing that
could happen would be to have a partitioned table that had temporary
partitions; its catalog state can easily get broken depending on the
DDLs issued on it. Valid partitioned index that should not be once
the partitions are gone, for example, which would require more exit
logic to flip states in pg_class, pg_index, etc.

> My current thinking is that it would be better to disallow marking
> partitioned tables as LOGGED/UNLOGGED and continue to have users explicitly
> specify what they want for each partition. It'd still probably be good to
> expand the documentation, but a clear ERROR when trying to set a
> partitioned table as UNLOGGED would hopefully clue folks in.

The addition of the new LOGGED keyword is not required if we limit
ourselves to an error when defining UNLOGGED, so if we drop this
proposal, let's also drop this part entirely and keep DefineRelation()
simpler. Actually, is really issuing an error the best thing we can
do after so many years allowing this grammar flavor to go through,
even if it is perhaps accidental? relpersistence is marked correctly
for partitioned tables, it's just useless. Expanding the
documentation sounds fine to me, one way or the other, to tell what
happens with partitioned tables.

By the way, I was looking at this patch series, and still got annoyed
with the code duplication with ALTER TABLE SET LOGGED/UNLOGGED, so
I've done something about that for now.
--
Michael

On Thu, Aug 29, 2024 at 03:44:45PM +0900, Michael Paquier wrote:
> On Tue, Aug 27, 2024 at 04:01:58PM -0500, Nathan Bossart wrote:
>> My current thinking is that it would be better to disallow marking
>> partitioned tables as LOGGED/UNLOGGED and continue to have users explicitly
>> specify what they want for each partition. It'd still probably be good to
>> expand the documentation, but a clear ERROR when trying to set a
>> partitioned table as UNLOGGED would hopefully clue folks in.
>
> The addition of the new LOGGED keyword is not required if we limit
> ourselves to an error when defining UNLOGGED, so if we drop this
> proposal, let's also drop this part entirely and keep DefineRelation()
> simpler.

+1

> Actually, is really issuing an error the best thing we can
> do after so many years allowing this grammar flavor to go through,
> even if it is perhaps accidental? relpersistence is marked correctly
> for partitioned tables, it's just useless. Expanding the
> documentation sounds fine to me, one way or the other, to tell what
> happens with partitioned tables.

IMHO continuing to allow partitioned tables to be marked UNLOGGED just
preserves the illusion that it does something. An ERROR could help dispel
that misconception.

--
nathan

On Thu, May 2, 2024 at 2:07 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Thu, Apr 25, 2024 at 08:55:27AM +0900, Michael Paquier wrote:
> > On Wed, Apr 24, 2024 at 04:43:58PM -0700, David G. Johnston wrote:
> >> My point is that if you feel that treating logged as a copy-able property
> >> is OK then doing the following should also just work:
> >>
> >> postgres=# create temp table parentt ( id integer ) partition by range (id);
> >> CREATE TABLE
> >> postgres=# create table child10t partition of parentt for values from (0)
> >> to (9);
> >> ERROR: cannot create a permanent relation as partition of temporary
> >> relation "parentt"
> >>
> >> i.e., child10t should be created as a temporary partition under parentt.
> >
> > Ah, indeed, I've missed your point here. Lifting the error and
> > inheriting temporary in this case would make sense.
>
> The case of a temporary persistence is actually *very* tricky. The
> namespace, where the relation is created, is guessed and locked with
> permission checks done based on the RangeVar when the CreateStmt is
> transformed, which is before we try to look at its inheritance tree to
> find its partitioned parent. So we would somewhat need to shortcut
> the existing RangeVar lookup and include the parents in the loop to
> find out the correct namespace. And this is much earlier than now.
> The code complexity is not trivial, so I am getting cold feet when
> trying to support this case in a robust fashion. For now, I have
> discarded this case and focused on the main problem with SET LOGGED
> and UNLOGGED.
>
> Switching between logged <-> unlogged does not have such
> complications, because the namespace where the relation is created is
> going to be the same. So we won't lock or perform permission checks
> on an incorrect namespace.
>
> The addition of LOGGED makes the logic deciding how the loggedness of
> a partition table based on its partitioned table or the query quite
> easy to follow, but this needs some safety nets in the sequence, view
> and CTAS code paths to handle with the case where the query specifies
> no relpersistence.
>
> I have also looked at support for ONLY, and I've been surprised that
> it is not that complicated. tablecmds.c has a ATSimpleRecursion()
> that is smart enough to do an inheritance tree lookup and apply the
> rewrites where they should happen in the step 3 of ALTER TABLE, while
> handling ONLY on its own. The relpersistence of partitioned tables is
> updated in step 2, with the catalog changes.
>
> Attached is a new patch series:
> - 0001 refactors some code around ATPrepChangePersistence() that I
> found confusing after applying the operation to partitioned tables.
> - 0002 adds support for a LOGGED keyword.
> - 0003 expands ALTER TABLE SET [UN]LOGGED to partitioned tables,
> without recursion to partitions.
> - 0004 adds the recursion logic, expanding regression tests to show
> the difference.
>
> 0003 and 0004 should be merged together, I think. Still, splitting
> them makes reviews a bit easier.
> --
> Michael

While reviewing the patches, I found a weird error msg:

+ALTER TABLE logged_part_1 SET UNLOGGED; -- fails as a foreign-key exists
+ERROR: could not change table "logged_part_1" to unlogged because it
references logged table "logged_part_2"

should this be *it is referenced by* here?

The error msg is from ATPrepChangePersistence, and I think we should
do something like:

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index b3cc6f8f69..30fbc3836a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -16986,7 +16986,7 @@ ATPrepChangePersistence(AlteredTableInfo *tab,
Relation rel, bool toLogged)
if (RelationIsPermanent(foreignrel))
ereport(ERROR,

(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
- errmsg("could
not change table \"%s\" to unlogged because it references logged table
\"%s\"",
+ errmsg("could
not change table \"%s\" to unlogged because it is referenced by logged
table \"%s\"",

What do you think?

--
Regards
Junwang Zhao

On Thu, Aug 29, 2024 at 09:49:44AM -0500, Nathan Bossart wrote:
> IMHO continuing to allow partitioned tables to be marked UNLOGGED just
> preserves the illusion that it does something. An ERROR could help dispel
> that misconception.

Okay. This is going to be disruptive if we do nothing about pg_dump,
unfortunately. How about tweaking dumpTableSchema() so as we'd never
issue UNLOGGED for a partitioned table? We could filter that out as
there is tbinfo->relkind.
--
Michael

On Tue, Sep 03, 2024 at 09:22:58AM +0900, Michael Paquier wrote:
> On Thu, Aug 29, 2024 at 09:49:44AM -0500, Nathan Bossart wrote:
>> IMHO continuing to allow partitioned tables to be marked UNLOGGED just
>> preserves the illusion that it does something. An ERROR could help dispel
>> that misconception.
>
> Okay. This is going to be disruptive if we do nothing about pg_dump,
> unfortunately. How about tweaking dumpTableSchema() so as we'd never
> issue UNLOGGED for a partitioned table? We could filter that out as
> there is tbinfo->relkind.

That's roughly what I had in mind.

--
nathan

On Mon, Sep 02, 2024 at 08:35:15PM -0500, Nathan Bossart wrote:
> On Tue, Sep 03, 2024 at 09:22:58AM +0900, Michael Paquier wrote:
>> Okay. This is going to be disruptive if we do nothing about pg_dump,
>> unfortunately. How about tweaking dumpTableSchema() so as we'd never
>> issue UNLOGGED for a partitioned table? We could filter that out as
>> there is tbinfo->relkind.
>
> That's roughly what I had in mind.

An idea is attached. The pgbench bit was unexpected.
--
Michael

On Tue, Sep 03, 2024 at 04:59:18PM +0900, Michael Paquier wrote:
> An idea is attached. The pgbench bit was unexpected.

This works correctly for CREATE TABLE, but ALTER TABLE still succeeds.
Interestingly, it doesn't seem to actually change relpersistence for
partitioned tables. I think we might need to adjust
ATPrepChangePersistence().

--
nathan

On Tue, Sep 03, 2024 at 10:33:02AM -0500, Nathan Bossart wrote:
> This works correctly for CREATE TABLE, but ALTER TABLE still succeeds.
> Interestingly, it doesn't seem to actually change relpersistence for
> partitioned tables. I think we might need to adjust
> ATPrepChangePersistence().

Adjusting ATPrepChangePersistence() looks incorrect to me seeing that
we have all the machinery in ATSimplePermissions() at our disposal,
and that this routine decides that ATT_TABLE should map to both
RELKIND_RELATION and RELKIND_PARTITIONED_TABLE.

How about inventing a new ATT_PARTITIONED_TABLE and make a clean split
between both relkinds? I'd guess that blocking both SET LOGGED and
UNLOGGED for partitioned tables is the best move, even if it is
possible to block only one or the other, of course.
--
Michael

On Mon, Sep 09, 2024 at 03:56:14PM +0900, Michael Paquier wrote:
> How about inventing a new ATT_PARTITIONED_TABLE and make a clean split
> between both relkinds? I'd guess that blocking both SET LOGGED and
> UNLOGGED for partitioned tables is the best move, even if it is
> possible to block only one or the other, of course.

I gave it a try, and while it is much more invasive, it is also much
more consistent with the rest of the file.

Thoughts?
--
Michael

On Tue, Sep 10, 2024 at 09:42:31AM +0900, Michael Paquier wrote:
> On Mon, Sep 09, 2024 at 03:56:14PM +0900, Michael Paquier wrote:
>> How about inventing a new ATT_PARTITIONED_TABLE and make a clean split
>> between both relkinds? I'd guess that blocking both SET LOGGED and
>> UNLOGGED for partitioned tables is the best move, even if it is
>> possible to block only one or the other, of course.
>
> I gave it a try, and while it is much more invasive, it is also much
> more consistent with the rest of the file.

This looks reasonable to me. Could we also use ATT_PARTITIONED_TABLE to
remove the partitioned table check in ATExecAddIndexConstraint()?

--
nathan

On Wed, Sep 18, 2024 at 10:17:47AM -0500, Nathan Bossart wrote:
> Could we also use ATT_PARTITIONED_TABLE to remove the partitioned table
> check in ATExecAddIndexConstraint()?

Eh, never mind. That ends up being gross because you have to redo the
relation type check in a few places.

--
nathan

On Wed, Sep 18, 2024 at 10:58:34AM -0500, Nathan Bossart wrote:
> On Wed, Sep 18, 2024 at 10:17:47AM -0500, Nathan Bossart wrote:
>> Could we also use ATT_PARTITIONED_TABLE to remove the partitioned table
>> check in ATExecAddIndexConstraint()?
>
> Eh, never mind. That ends up being gross because you have to redo the
> relation type check in a few places.

I did not notice this one. I have to admit that the error message
consistency is welcome, not the extra checks required at
transformation.
--
Michael

On Thu, Sep 19, 2024 at 08:06:19AM +0900, Michael Paquier wrote:
> I did not notice this one. I have to admit that the error message
> consistency is welcome, not the extra checks required at
> transformation.

I have applied 0001 for now to add ATT_PARTITIONED_TABLE. Attached is
the remaining piece.
--
Michael

On 2024-Sep-10, Michael Paquier wrote:

> @@ -5060,17 +5061,17 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
> pass = AT_PASS_MISC;
> break;
> case AT_AttachPartition:
> - ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_PARTITIONED_INDEX);
> + ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_PARTITIONED_TABLE | ATT_PARTITIONED_INDEX);
> /* No command-specific prep needed */
> pass = AT_PASS_MISC;
> break;
> case AT_DetachPartition:
> - ATSimplePermissions(cmd->subtype, rel, ATT_TABLE);
> + ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_PARTITIONED_TABLE);
> /* No command-specific prep needed */
> pass = AT_PASS_MISC;
> break;
> case AT_DetachPartitionFinalize:
> - ATSimplePermissions(cmd->subtype, rel, ATT_TABLE);
> + ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_PARTITIONED_TABLE);
> /* No command-specific prep needed */
> pass = AT_PASS_MISC;
> break;

It looks to me like these cases could be modified to accept only
ATT_PARTITIONED_TABLE, not ATT_TABLE anymore. The ATT_TABLE cases are
useless anyway, because they're rejected by transformPartitionCmd.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/

On Thu, Sep 19, 2024 at 10:03:09AM +0200, Alvaro Herrera wrote:
> It looks to me like these cases could be modified to accept only
> ATT_PARTITIONED_TABLE, not ATT_TABLE anymore. The ATT_TABLE cases are
> useless anyway, because they're rejected by transformPartitionCmd.

+1. If anything, this makes the error messages more consistent.

--
nathan

On Thu, Sep 19, 2024 at 10:03:09AM +0200, Alvaro Herrera wrote:
> It looks to me like these cases could be modified to accept only
> ATT_PARTITIONED_TABLE, not ATT_TABLE anymore. The ATT_TABLE cases are
> useless anyway, because they're rejected by transformPartitionCmd.

Makes sense to me, thanks for the suggestion.

What do you think about adding a test with DETACH FINALIZE when
attempting it on a normal table, its path being under a different
subcommand than DETACH [CONCURRENTLY]?

There are no tests for normal tables with DETACH CONCURRENTLY, but as
it is the same as DETACH under the AT_DetachPartition subcommand, that
does not seem worth the extra cycles.
--
Michael

On Fri, Sep 20, 2024 at 09:37:54AM +0900, Michael Paquier wrote:
> What do you think about adding a test with DETACH FINALIZE when
> attempting it on a normal table, its path being under a different
> subcommand than DETACH [CONCURRENTLY]?
>
> There are no tests for normal tables with DETACH CONCURRENTLY, but as
> it is the same as DETACH under the AT_DetachPartition subcommand, that
> does not seem worth the extra cycles.

Added an extra test for the CONCURRENTLY case at the end, and applied
the simplification.

Hmm. Should we replace the error messages in transformPartitionCmd()
with some assertions at this stage? I don't see a high cost in
keeping these even now, and keeping errors is perhaps more useful for
some extension code that builds AT_AttachPartition or
AT_DetachPartition commands?
--
Michael

On Thu, Sep 19, 2024 at 01:08:33PM +0900, Michael Paquier wrote:
> I have applied 0001 for now to add ATT_PARTITIONED_TABLE. Attached is
> the remaining piece.

And the second piece is now applied as of e2bab2d79204.
--
Michael

On Wed, Jun 4, 2025 at 6:42 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Thu, Sep 19, 2024 at 01:08:33PM +0900, Michael Paquier wrote:
> > I have applied 0001 for now to add ATT_PARTITIONED_TABLE. Attached is
> > the remaining piece.
>
> And the second piece is now applied as of e2bab2d79204.
> --
> Michael

Hi,

Should we consider preventing tab completion for PARTITION BY
immediately after CREATE TABLE name (...)? Or is it fine to leave it
as is, given that it's syntactically correct?

--
Best regards,
Shinya Kato
NTT OSS Center

On Wed, Jun 4, 2025 at 6:55 PM Shinya Kato <shinya11(dot)kato(at)gmail(dot)com> wrote:
>
> On Wed, Jun 4, 2025 at 6:42 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> >
> > On Thu, Sep 19, 2024 at 01:08:33PM +0900, Michael Paquier wrote:
> > > I have applied 0001 for now to add ATT_PARTITIONED_TABLE. Attached is
> > > the remaining piece.
> >
> > And the second piece is now applied as of e2bab2d79204.
> > --
> > Michael
>
> Hi,
>
> Should we consider preventing tab completion for PARTITION BY
> immediately after CREATE TABLE name (...)? Or is it fine to leave it
> as is, given that it's syntactically correct?

Sorry.
CREATE TABLE name (...) -> CREATE UNLOGGED TABLE name (...)

--
Best regards,
Shinya Kato
NTT OSS Center

On Wed, Jun 04, 2025 at 07:05:20PM +0900, Shinya Kato wrote:
> On Wed, Jun 4, 2025 at 6:55 PM Shinya Kato <shinya11(dot)kato(at)gmail(dot)com> wrote:
>> Should we consider preventing tab completion for PARTITION BY
>> immediately after CREATE TABLE name (...)? Or is it fine to leave it
>> as is, given that it's syntactically correct?
>
> Sorry.
> CREATE TABLE name (...) -> CREATE UNLOGGED TABLE name (...)

I see no benefit in recommending things that are guaranteed to error. In
commit 5c1ce1b, we removed tab completion for CREATE UNLOGGED MATERIALIZED
VIEW even though it is supported by the grammar. The partitioned table
case sounds like roughly the same situation.

--
nathan

On Wed, Jun 04, 2025 at 11:15:29AM -0500, Nathan Bossart wrote:
> I see no benefit in recommending things that are guaranteed to error. In
> commit 5c1ce1b, we removed tab completion for CREATE UNLOGGED MATERIALIZED
> VIEW even though it is supported by the grammar. The partitioned table
> case sounds like roughly the same situation.

Agreed to not suggest the PARTITION BY clause in the tab completion as
it is not supported by the backend for unlogged tables.
tab-complete.in.c has some handling for CREATE UNLOGGED TABLE around
line 3667, so we could just have an extra case for it, like in the
attached patch. A split already exists for temporary tables to handle
the ON COMMIT clause after the attribute list.

Thoughts?
--
Michael

On Thu, Jun 5, 2025 at 9:23 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> Agreed to not suggest the PARTITION BY clause in the tab completion as
> it is not supported by the backend for unlogged tables.
> tab-complete.in.c has some handling for CREATE UNLOGGED TABLE around
> line 3667, so we could just have an extra case for it, like in the
> attached patch. A split already exists for temporary tables to handle
> the ON COMMIT clause after the attribute list.
>
> Thoughts?

Thank you. It looks good to me.

--
Best regards,
Shinya Kato
NTT OSS Center

On Thu, Jun 05, 2025 at 10:04:23AM +0900, Shinya Kato wrote:
> Thank you. It looks good to me.

How does the RMT feel about this change? Nathan, would you be OK with
that? It's not a big problem, as well, if the code is kept as-is, but
as it's a simple change..
--
Michael

On Thu, Jun 05, 2025 at 01:57:30PM +0900, Michael Paquier wrote:
> How does the RMT feel about this change? Nathan, would you be OK with
> that? It's not a big problem, as well, if the code is kept as-is, but
> as it's a simple change..

IMHO a case can be reasonably made that this is an oversight in the related
commit. I've added the rest of the RMT here in case they see it
differently.

The patch LGTM, too.

--
nathan

On Thu, Jun 05, 2025 at 03:04:45PM -0500, Nathan Bossart wrote:
> IMHO a case can be reasonably made that this is an oversight in the related
> commit. I've added the rest of the RMT here in case they see it
> differently.
>
> The patch LGTM, too.

Okay, thanks. Let's wait for a couple of days in case there are more
opinions and/or comments. I propose to apply the patch around the
beginning of next week if there are no objections.
--
Michael

On Fri, Jun 06, 2025 at 10:55:32AM +0900, Michael Paquier wrote:
> Okay, thanks. Let's wait for a couple of days in case there are more
> opinions and/or comments. I propose to apply the patch around the
> beginning of next week if there are no objections.

It took a bit longer than wanted, but applied now.
--
Michael