Re: unlogged sequences

On Sun, Apr 3, 2022 at 10:19 AM Peter Eisentraut <
peter(dot)eisentraut(at)enterprisedb(dot)com> wrote:

> Here is an updated patch that fixes this pg_dump/pg_upgrade issue and
> also adds a few more comments and documentation sentences about what
> happens and what is allowed. I didn't change any behaviors; it seems we
> didn't have consensus to do that.
>

IIUC the patch behavior with respect to migration is to have pg_upgrade
retain the current logged persistence mode for all owned sequences
regardless of the owning table's persistence. The same goes for pg_dump
for serial sequences since they will never be annotated with UNLOGGED and
simply adding an ownership link doesn't cause a table rewrite.

However, tables having an identity sequence seem to be unaddressed in this
patch. The existing (and unchanged) pg_dump.c code results in:

CREATE TABLE public.testgenid (
getid bigint NOT NULL
);

ALTER TABLE public.testgenid OWNER TO postgres;

ALTER TABLE public.testgenid ALTER COLUMN getid ADD GENERATED ALWAYS AS
IDENTITY (
SEQUENCE NAME public.testgenid_getid_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);

ISTM that we need to add the ability to specify [UN]LOGGED in those
sequence_options and have pg_dump.c output the choice explicitly instead of
relying upon a default.

Without that, the post-patch dump/restore cannot retain the existing
persistence mode value for the sequence. For the default we would want to
have ALTER TABLE ALTER COLUMN be LOGGED to match the claim that pg_dump
doesn't change the persistence mode. The main decision, then, is whether
CREATE TABLE and ALTER TABLE ADD COLUMN should default to UNLOGGED (this
combination preserves existing values via pg_dump while still letting the
user benefit from the new feature without having to specify UNLOGGED in
multiple places) or LOGGED (preserving existing values and consistency).
All UNLOGGED is an option but I think it would need to be considered along
with pg_upgrade changing them all as well. Again, limiting this decision
to identity sequences only.

David J.