Re: unlogged sequences

On Thu, Mar 31, 2022 at 6:03 PM Robert Haas <robertmhaas@gmail.com> wrote:

> On Thu, Mar 31, 2022 at 8:44 PM David G. Johnston
> <david.g.johnston@gmail.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.