Re: unlogged sequences

On Thu, Mar 31, 2022 at 9:28 AM Andres Freund <andres(at)anarazel(dot)de> wrote:

> I agree it makes sense to have logged sequences with unlogged tables. We
> should call out the behavioural change somewhere prominent in the release
> notes.
>
>
We can/do already support that unlikely use case by allowing one to remove
the OWNERSHIP dependency between the table and the sequence.

I'm fine with owned sequences tracking the persistence attribute of the
owning table.

I don't think we should make pg_upgrade change the loggedness of sequences.
>
>
We are willing to change the default behavior here so it is going to affect
dump/restore anyway, might as well fully commit and do the same for
pg_upgrade. The vast majority of users will benefit from the new default
behavior.

I don't actually get, though, how that would play with pg_dump since it
always emits an unowned, and thus restored as logged, sequence first and
then alters the sequence to be owned by the table. Thus restoring an old
SQL dump into the v15 is going to fail if we prohibit
unlogged-table/logged-sequence; unless we actively change the logged-ness
of the sequence when subordinating it to a table.

Thus, the choices seem to be:

1) implement forced persistence agreement for owned sequences, changing the
sequence to match the table when the alter table happens, and during
pg_upgrade.
2) do not force persistence agreement for owned sequences

If choosing option 2, are you on board with changing the behavior of CREATE
UNLOGGED TABLE with respect to any auto-generated sequences?

David J.