Re: Sequence Access Methods, round two

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].

>
>> However, it adds a massive overhead and is designed only for very
>> narrow cases.
>
> Are you referring to the function redirections required for the
> callbacks here? Or are you referring to something more specific?

Yes, it is more specific - strictly growing cross-node sequence where we
need to acquire a global lock. I have written about it by accident -
just to demonstrate that some Postgres-inherited implementations exist
and are used in production.

>
>> I skimmed your patches. It seems that 0003 and 0007 should be the first
>> patches in the set.
>
> Patch 0003 is named "Sequence access methods - backend support",
> relying on two refactoring pieces. Patch 0007 is the snowflake
> contrib module, which is based on all the other patches. So I don't
> quite see what would be the point here based on how the patch is
> currently written?

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.

>
>> Additionally, the approach itself appears overly complicated to me.
>> Generally, I need a kinda of type parameter (like typmod) where I can
>> propose the OID of the nextval routine and extra parameters - it may be
>> serialised somewhere in pg_attribute.
>> It seems even more flexible than a default_sequence_access_method, doesn't
>> it?
>
> 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.

>
> 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.

[1]
https://github.com/postgrespro/mmts/blob/baab9238f784d428481ecfa1294e3f9a3910b2d2/src/multimaster.c#L578

--
regards, Andrei Lepikhov,
pgEdge