On 3/31/22 19:35, David G. Johnston wrote:
> On Thu, Mar 31, 2022 at 9:28 AM Andres Freund <andres@anarazel.de
> <mailto:andres@anarazel.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.
>
I'm not sure I follow. If we allow logged sequences with unlogged
tables, there's be no behavioral change, no?
>
> 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.
>
So essentially an independent sequence, used in a default value.
> 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.
>
Whatever we do, I think we should keep the pg_dump and pg_upgrade
behavior as consistent as possible.
> 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.
>
Yeah. I guess we'd need to either automatically switch the sequence to
the right persistence when linking it to the table, or maybe we could
modify pg_dump to emit UNLOGGED when the table is unlogged (but that
would work only when using the new pg_dump).
> 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?
>
What behavior change, exactly? To create the sequences as UNLOGGED, but
we'd not update the persistence after that?
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company