Re: unlogged sequences - Mailing list pgsql-hackers
| From | David G. Johnston |
|---|---|
| Subject | Re: unlogged sequences |
| Date | |
| Msg-id | CAKFQuwYfxHCZX=u7ibhYLmxMc+0X4kWpYx2NwvPdr6x7zRYn9w@mail.gmail.com Whole thread Raw |
| In response to | Re: unlogged sequences (Robert Haas <robertmhaas@gmail.com>) |
| List | pgsql-hackers |
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.
pgsql-hackers by date: