Re: unlogged sequences

On Thu, Mar 31, 2022 at 6:03 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Thu, Mar 31, 2022 at 8:44 PM David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> > The "give the user power" argument is also valid. But since they
> already have power through unowned sequences, having the owned sequences
> more narrowly defined doesn't detract from usability, and in many ways
> enhances it by further reinforcing the fact that the sequence internally
> used when you say "GENERATED ALWAYS AS IDENTITY" is an implementation
> detail - one that has the same persistence as the table.
>
> I think there's a question about what happens in the GENERATED ALWAYS
> AS IDENTITY case. The DDL commands that create such sequences are of
> the form ALTER TABLE something ALTER COLUMN somethingelse GENERATED
> ALWAYS AS (sequence_parameters), and if we need to specify somewhere
> in the whether the sequence should be logged or unlogged, how do we do
> that?

I give answers for the "owned sequences match their owning table's
persistence" model below:

You would not need to specify it - the table is specified and that is
sufficient to know what value to choose.

> Consider:
>
> rhaas=# create unlogged table xyz (a int generated always as identity);
> CREATE TABLE
> rhaas=# \d+ xyz
> Unlogged table "
> public.xyz"
> Column | Type | Collation | Nullable | Default
> | Storage | Compression | Stats target | Description
>
> --------+---------+-----------+----------+------------------------------+---------+-------------+--------------+-------------
> a | integer | | not null | generated always as
> identity | plain | | |
> Access method: heap
>
> rhaas=# \d+ xyz_a_seq
> Sequence "public.xyz_a_seq"
> Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
> ---------+-------+---------+------------+-----------+---------+-------
> integer | 1 | 1 | 2147483647 | 1 | no | 1
> Sequence for identity column: public.xyz.a
>
> In this new system, does the user still get a logged sequence?

No

> If they
> get an unlogged sequence, how does dump-and-restore work?

As described in the first response, since ALTER COLUMN is used during
dump-and-restore, the sequence creation occurs in a command where we know
the owning table is unlogged so the created sequence is unlogged.

> What if they
> want to still have a logged sequence?

I was expecting the following to work, though it does not presently:

ALTER SEQUENCE yetanotherthing OWNED BY NONE;
ERROR: cannot change ownership of identity sequence

ALTER SEQUENCE yetanotherthing SET LOGGED;

IMO, the generated case is the stronger one for not allowing them to be
different. They can fall back onto the DEFAULT
nextval('sequence_that_is_unowned') option to get the desired behavior.

David J.