Re: unlogged sequences - Mailing list pgsql-hackers

From David G. Johnston
Subject Re: unlogged sequences
Date
Msg-id CAKFQuwYUQAktx9bEJJyY2PC_AGOxbvHKOjTB0HFMPs0izwXZsA@mail.gmail.com
Whole thread Raw
In response to Re: unlogged sequences  (Tomas Vondra <tomas.vondra@enterprisedb.com>)
Responses Re: unlogged sequences
Re: unlogged sequences
List pgsql-hackers
On Thu, Mar 31, 2022 at 1:05 PM Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:

I agree the first part is not contentious, so shall we extract this part
of the patch and get that committed for PG15? Or is that too late to
make such changes to the patch?


The minimum viable feature for me, given the written goal for the patch and the premise of not changing any existing behavior, is:

DB State: Allow a sequence to be unlogged.
Command: ALTER SEQUENCE SET UNLOGGED
Limitation: The above command fails if the sequence is unowned, or it is owned and the table owning it is not UNLOGGED

(optional safety) Limitation: Changing a table from unlogged to logged while owning unlogged sequences would be prohibited
(optional safety) Compensatory Behavior: Add the ALTER SEQUENCE SET LOGGED command for owned sequences to get them logged again in preparation for changing the table to being logged.

In particular, I don't see CREATE UNLOGGED SEQUENCE to be all that valuable since CREATE UNLOGGED TABLE wouldn't leverage it.

The above, possibly only half-baked, patch scope does not change any existing behavior but allows for the stated goal: an unlogged table having an unlogged sequence.  The DBA just has to execute the ALTER SEQUENCE command on all relevant sequences.  They can't even really get it wrong since only relevant sequences can be altered.  Not having CREATE TABLE make an unlogged sequence by default is annoying though and likely should be changed - though it can leverage ALTER SEQUENCE too.

Anything else they wish to do can be done via a combination of ownership manipulation and, worse case, dropping and recreating the sequence.  Though allowed for unowned unlogged sequences, while outside the explicit goal of the patch, would be an easy add (just don't error on the ALTER SEQUENCE SET UNLOGGED when the sequence is unowned).

David J.

pgsql-hackers by date:

Previous
From: Greg Stark
Date:
Subject: Re: Keep notnullattrs in RelOptInfo (Was part of UniqueKey patch series)
Next
From: Greg Stark
Date:
Subject: Commitfest closing