Re: unlogged sequences

On Thu, Mar 31, 2022 at 3:43 PM Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>
wrote:

> On 3/31/22 22:40, David G. Johnston wrote:
> > On Thu, Mar 31, 2022 at 1:05 PM Tomas Vondra
> > <tomas(dot)vondra(at)enterprisedb(dot)com <mailto:tomas(dot)vondra(at)enterprisedb(dot)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.
> >
>
> Hmm, so what about doing a little bit different thing:
>
> 1) owned sequences inherit persistence of the table by default
>

This is the contentious point. If we are going to do it by default - thus
changing existing behavior - I would rather just do it always. This is
also underspecified, there are multiple ways for a sequence to become owned.

Personally I'm for the choice to effectively remove the sequence's own
concept of logged/unlogged when it is owned by a table and to always just
use the table's value.

> 2) allow ALTER SEQUENCE to change persistence for all sequences (no
> restriction for owned sequences)
>

A generalization that is largely incontrovertible.

>
> 3) ALTER TABLE ... SET [UN]LOGGED changes persistence for sequences
> matching the initial table persistence
>

I'm leaning against this, leaving users to set each owned sequence to
logged/unlogged as they see fit if they want something other than
all-or-nothing. I would stick to only providing an easy method to get the
assumed desired all-same behavior.
ALTER TABLE SET [UN]LOGGED, SET ALL SEQUENCES TO [UN]LOGGED;

> IMHO (1) would address vast majority of cases, which simply want the
> same persistence for the whole table and all auxiliary objects. (2)
> would address use cases requiring different persistence for sequences
> (including owned ones).
>
> I'm not sure about (3) though, maybe that's overkill.
>
> Of course, we'll always have problems with older releases, as it's not
> clear whether a logged sequence on unlogged table would be desirable or
> is used just because unlogged sequences were not supported. (We do have
> the same issue for logged tables too, but I doubt anyone really needs
> defining unlogged sequences on logged tables.)
>
> So no matter what we do, we'll make the wrong decision in some cases.
>

Again, I don't have too much concern here because you lose very little by
having an unowned sequence. Which is why I'm fine with owned sequences
becoming even moreso implementation details that adhere to the persistence
mode of the owning relation. But if the goal here is to defer such a
decision then the tradeoff is the DBA is given control and they get to
enforce consistency even if they are not benefitting from the flexibility.

> > 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.
>
> Yeah. I think my proposal is pretty close to that, except that the
> sequence would first inherit persistence from the table, and there'd be
> an ALTER SEQUENCE for owned sequences where it differs. (And non-owned
> sequences would be created as logged/unlogged explicitly.)
>

I don't have any real problem with 1 or 2, they fill out the feature so it
is generally designed as opposed to solving a very specific problem.

For 1:
The "ADD COLUMN" (whether in CREATE TABLE or ALTER TABLE) pathway will
produce a new sequence whose persistence matches that of the target table.
While a behavior change it is one aligned with the goal of the patch for
typical ongoing behavior and should benefit way more people than it may
inconvenience. The "sequence not found" error that would be generated
seems minimally impactful.

The "ALTER SEQUENCE OWNED BY" pathway will not change the sequence's
persistence. This is what pg_dump will use for serial/bigserial
The "ALTER TABLE ALTER COLUMN" pathway will not change the sequence's
persistence. This is what pg_dump will use for generated always as identity

Provide a general purpose ALTER SEQUENCE SET [UN]LOGGED command

Provide an SQL Command to change all owned sequences of a table to be
UNLOGGED or LOGGED (I mentioned a function as well if someone thinks it
worth the time - in lieu of a function a psql script leveraging \gexec may
be nice to reference).

> I don't think we need to worry about old pg_dump versions on new PG
> versions, because that's not really supported.
>

Correct.

>
> And for old PG versions the behavior would differ a bit depending on the
> pg_dump version used. With old pg_dump version, the ALTER SEQUENCE would
> not be emitted,

Correct, nothing else is emitted either...

> That's need to be documented, of course.
>
>
It (the general promises for pg_dump) is documented.

https://www.postgresql.org/docs/current/app-pgdump.html : Notes

David J.