Re: Sequence Access Methods, round two

On Tue, Nov 11, 2025 at 09:59:40AM +0100, Andrei Lepikhov wrote:
> On 11/11/2025 02:43, Michael Paquier wrote:
>> On Mon, Nov 10, 2025 at 04:31:07PM +0100, Andrei Lepikhov wrote:
>>> In multimaster, you may find an implementation of strictly monotone
>>> sequences. This method is covered by a GUC, impolitely named as 'Volkswagen
>>> method'.
>>
>> I'm afraid that I cannot understand what you mean with this reference.
>
> I mean sequence values generation mode, regulated by the GUC [1].

This is an entirely different product and project.

> Hmm. In v24, 0003 introduces AM, 0007 - an example of how to use it. Ok,
> maybe I need to dive into the code a little more.
> The 0001 and 0002 introduce additional attributes to sequences and code
> refactoring, which were partly rewritten by subsequent patches. It seems to
> me that 0001 and 0002 might be reviewed and committed after the main code.

Err, no.  It's the opposite here: code cleanups and file splits are
cleaner if they happen first, not after the implementations as these
lead to less code churn overall.  Splitting the sequence "core" logic
and WAL logic make sense in the long-term to me anyway, as a separate
refactoring piece.  It's true that 0002 could be slightly different,
though, we could for example keep sequence.c where it is now in
src/backend/commands/ without forcing the use of the term "AM" in the
file names, and extract the WAL pieces of it into a new file (aka the
redo and marking routines).  Then it's only a game of moving the files
around depending on the follow-up pieces.  I should probably post a
patch for that separately, this has been bugging me a bit in terms of
code separation clarity for the sequence RMGR.

>> Hmm.  How would this fit into a DDL model?  The advantage of the GUC
>> is to be able to hide from the client which sequence computation
>> method is used internally, with something like the following to forget
>> about the knowledge of the computation, so as it's fully transparent:
>> CREATE ACCESS METHOD popo
>>    TYPE SEQUENCE HANDLER popo_sequenceam_handler;
>> SET default_sequence_access_method = popo;
>> CREATE TABLE tab (a int GENERATED ALWAYS AS IDENTITY, b int);
>
> Yes, I have no idea how to implement my imaginary example. I just wanted to
> say that the implementation looks massive and isn't flexible enough to let
> two columns of a table have different AM. Not sure if it is a critical case,
> though, but I constantly keep in mind benchmarking with sophisticated
> distributions.

Okay.

>> It sounds like your case does really care much about these dealing
>> with these, where you would only need a shortcut to pass a nextval()
>> for a sequence, but I cannot say for sure if these two properties
>> matter for you, as well.
>
> Absolutely. I would like to utilise sequence AM for such sort of tasks,
> particularly to simplify demonstration of how the optimiser behaves when
> there are changes in the data distribution law. However, it does seem quite
> specific.

Hmm.  Not sure how the optimizer needs to be related to the
cost computation of the optimizer.  You could already mess up with
that with some of planner hooks anyway, it does not have to be related
to this patch set specifically.
--
Michael