Re: unlogged sequences

On Fri, Apr 1, 2022 at 9:22 AM Peter Eisentraut <
peter(dot)eisentraut(at)enterprisedb(dot)com> wrote:

>
> On 01.04.22 00:43, Tomas Vondra wrote:
> > Hmm, so what about doing a little bit different thing:
> >
> > 1) owned sequences inherit persistence of the table by default
> >
> > 2) allow ALTER SEQUENCE to change persistence for all sequences (no
> > restriction for owned sequences)
> >
> > 3) ALTER TABLE ... SET [UN]LOGGED changes persistence for sequences
> > matching the initial table persistence
>
> Consider that an identity sequence creates an "internal" dependency and
> a serial sequence creates an "auto" dependency.
>
> An "internal" dependency means that the internal object shouldn't really
> be operated on directly. (In some cases it's allowed for convenience.)
> So I think in that case the sequence must follow the table's persistence
> in all cases. This is accomplished by setting the initial persistence
> to the table's, making ALTER TABLE propagate persistence changes, and
> prohibiting direct ALTER SEQUENCE SET.
>
> An "auto" dependency is looser, so manipulating both objects
> independently can be allowed. In that case, I would do (1), (2), and (3).
>
> (I think your (3) is already the behavior in the patch, since there are
> only two persistence levels in play at that point.)
>

I would support having a serial sequence be allowed to be changed
independently while an identity sequence is made to match the table it is
owned by. Older version restores would produce a logged serial sequence
(since the sequence is independently created and then attached to the
table) on unlogged tables but since identity sequences are only even
implicitly created they would become unlogged as part of the restore.
Though I suspect that pg_upgrade will need to change them explicitly.

I would support all owned sequences as well, but that seems unreachable at
the moment.

David J.