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.
