Re: unlogged sequences

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: unlogged sequences
Дата
Msg-id CAKFQuwYEzPz84kYv=dme_qA1jTxLAFp08ODj2Zmnt-ZCzDCJrw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: unlogged sequences  (Peter Eisentraut <peter.eisentraut@enterprisedb.com>)
Ответы Re: unlogged sequences  (Peter Eisentraut <peter.eisentraut@enterprisedb.com>)
Список pgsql-hackers
On Sun, Apr 3, 2022 at 10:19 AM Peter Eisentraut <peter.eisentraut@enterprisedb.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.



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Peter Eisentraut
Дата:
Сообщение: Re: unlogged sequences
Следующее
От: Andres Freund
Дата:
Сообщение: Run pg_amcheck in 002_pg_upgrade.pl and 027_stream_regress.pl?