Re: unlogged sequences - Mailing list pgsql-hackers
| From | David G. Johnston |
|---|---|
| Subject | Re: unlogged sequences |
| Date | |
| Msg-id | CAKFQuwZyFSsSG7-PL_UO3Tb5FHUBLqR=hfYde7R=7UCy+j=0xA@mail.gmail.com Whole thread Raw |
| In response to | Re: unlogged sequences (Tomas Vondra <tomas.vondra@enterprisedb.com>) |
| List | pgsql-hackers |
On Thu, Mar 31, 2022 at 3:43 PM Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:
On 3/31/22 22:40, David G. Johnston wrote:
> On Thu, Mar 31, 2022 at 1:05 PM Tomas Vondra
> <tomas.vondra@enterprisedb.com <mailto:tomas.vondra@enterprisedb.com>>
> wrote:
>
>
> I agree the first part is not contentious, so shall we extract this part
> of the patch and get that committed for PG15? Or is that too late to
> make such changes to the patch?
>
>
> The minimum viable feature for me, given the written goal for the patch
> and the premise of not changing any existing behavior, is:
>
> DB State: Allow a sequence to be unlogged.
> Command: ALTER SEQUENCE SET UNLOGGED
> Limitation: The above command fails if the sequence is unowned, or it is
> owned and the table owning it is not UNLOGGED
>
> (optional safety) Limitation: Changing a table from unlogged to logged
> while owning unlogged sequences would be prohibited
> (optional safety) Compensatory Behavior: Add the ALTER SEQUENCE SET
> LOGGED command for owned sequences to get them logged again in
> preparation for changing the table to being logged.
>
> In particular, I don't see CREATE UNLOGGED SEQUENCE to be all that
> valuable since CREATE UNLOGGED TABLE wouldn't leverage it.
>
Hmm, so what about doing a little bit different thing:
1) owned sequences inherit persistence of the table by default
This is the contentious point. If we are going to do it by default - thus changing existing behavior - I would rather just do it always. This is also underspecified, there are multiple ways for a sequence to become owned.
Personally I'm for the choice to effectively remove the sequence's own concept of logged/unlogged when it is owned by a table and to always just use the table's value.
2) allow ALTER SEQUENCE to change persistence for all sequences (no
restriction for owned sequences)
A generalization that is largely incontrovertible.
3) ALTER TABLE ... SET [UN]LOGGED changes persistence for sequences
matching the initial table persistence
I'm leaning against this, leaving users to set each owned sequence to logged/unlogged as they see fit if they want something other than all-or-nothing. I would stick to only providing an easy method to get the assumed desired all-same behavior.
ALTER TABLE SET [UN]LOGGED, SET ALL SEQUENCES TO [UN]LOGGED;
IMHO (1) would address vast majority of cases, which simply want the
same persistence for the whole table and all auxiliary objects. (2)
would address use cases requiring different persistence for sequences
(including owned ones).
I'm not sure about (3) though, maybe that's overkill.
Of course, we'll always have problems with older releases, as it's not
clear whether a logged sequence on unlogged table would be desirable or
is used just because unlogged sequences were not supported. (We do have
the same issue for logged tables too, but I doubt anyone really needs
defining unlogged sequences on logged tables.)
So no matter what we do, we'll make the wrong decision in some cases.
Again, I don't have too much concern here because you lose very little by having an unowned sequence. Which is why I'm fine with owned sequences becoming even moreso implementation details that adhere to the persistence mode of the owning relation. But if the goal here is to defer such a decision then the tradeoff is the DBA is given control and they get to enforce consistency even if they are not benefitting from the flexibility.
> Not having
> CREATE TABLE make an unlogged sequence by default is annoying though and
> likely should be changed - though it can leverage ALTER SEQUENCE too.
Yeah. I think my proposal is pretty close to that, except that the
sequence would first inherit persistence from the table, and there'd be
an ALTER SEQUENCE for owned sequences where it differs. (And non-owned
sequences would be created as logged/unlogged explicitly.)
I don't have any real problem with 1 or 2, they fill out the feature so it is generally designed as opposed to solving a very specific problem.
For 1:
The "ADD COLUMN" (whether in CREATE TABLE or ALTER TABLE) pathway will produce a new sequence whose persistence matches that of the target table. While a behavior change it is one aligned with the goal of the patch for typical ongoing behavior and should benefit way more people than it may inconvenience. The "sequence not found" error that would be generated seems minimally impactful.
The "ALTER SEQUENCE OWNED BY" pathway will not change the sequence's persistence. This is what pg_dump will use for serial/bigserial
The "ALTER TABLE ALTER COLUMN" pathway will not change the sequence's persistence. This is what pg_dump will use for generated always as identity
Provide a general purpose ALTER SEQUENCE SET [UN]LOGGED command
Provide an SQL Command to change all owned sequences of a table to be UNLOGGED or LOGGED (I mentioned a function as well if someone thinks it worth the time - in lieu of a function a psql script leveraging \gexec may be nice to reference).
I don't think we need to worry about old pg_dump versions on new PG
versions, because that's not really supported.
Correct.
And for old PG versions the behavior would differ a bit depending on the
pg_dump version used. With old pg_dump version, the ALTER SEQUENCE would
not be emitted,
Correct, nothing else is emitted either...
That's need to be documented, of course.
It (the general promises for pg_dump) is documented.
David J.
pgsql-hackers by date: