Re: unlogged sequences - Mailing list pgsql-hackers

On Thu, Mar 31, 2022 at 8:44 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> It seems reasonable to extend the definition of "ownership of a sequence" in this way.  We always let you create
unownedsequences with whatever persistence you like if you need flexibility. 

I'd say it doesn't seem to have any benefit, and therefore seems
unreasonable. Right now, OWNED BY is documented as a way of getting
the sequence to automatically be dropped if the table column goes
away. If it already did five things, maybe you could argue that this
thing is just like the other five and therefore changing it is the
right idea. But going from one thing to two that don't seem to have
much to do with each other seems much less reasonable, especially
since it doesn't seem to buy anything.

> The "give the user power" argument is also valid.  But since they already have power through unowned sequences,
havingthe owned sequences more narrowly defined doesn't detract from usability, and in many ways enhances it by further
reinforcingthe 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? 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? If they
get an unlogged sequence, how does dump-and-restore work? What if they
want to still have a logged sequence? But for sequences that are
simply owned, there is no problem here, and I think that inventing one
would not be a good plan.

--
Robert Haas
EDB: http://www.enterprisedb.com