Re: unlogged sequences

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

> On 01.04.22 18:22, Peter Eisentraut 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.
>
> But to make pg_upgrade work for identity sequences of unlogged tables,
> we need to allow ALTER SEQUENCE ... SET LOGGED on such sequences. Which
> I guess is not a real problem in the end.
>

Indeed, we need the syntax anyway. We can constrain it though, and error
when trying to make them different but allow making them the same. To
change a table's persistence you have to then change the table first -
putting them back into different states - then sync up the sequence again.

David J.