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.