Re: Sequence Access Methods, round two

On Tue, Jan 23, 2024 at 10:58:50AM +0100, Peter Eisentraut wrote:
> On 18.01.24 16:54, Matthias van de Meent wrote:
> The proposed sequence AM patch would support a different nextval function,
> but does it support additional parameters?  I haven't found that.

Yes and no.  Yes as in "the patch set can support it" and no as in
"the patch does not implement that yet".  You could do two things
here:
- Add support for reloptions for sequences.  The patch does not
include that on purpose because it already covers a lot of ground, and
that did not look like a strict requirement to me as a first shot.  It
can be implemented on top of the patch set.  That's not technically
complicated, actually, but there are some shenanigans to discuss with
the heap relation used under the hood by a sequence for the in-core
method or any other sequence AM that would need a sequence.
- Control that with GUCs defined in the AM, which may be weird, still
enough at relation level.  And enough with the current patch set.

reloptions would make the most sense to me here, I assume, to ease we
handle use nextval().

> Another use case I have wished for from time to time is creating sequences
> using different data types, for example uuids.  You'd just need to provide a
> data-type-specific "next" function.  However, in this patch, all the values
> and state are hardcoded to int64.

Yeah, because all the cases I've seen would be happy with being able
to map a result to 8 bytes with a controlled computation method.  The
size of the output generated, the set of data types that can be
supported by a table AM and the OID/name of the SQL function in charge
of retrieving the value could be controlled in the callbacks
themselves, and this would require a design of the callbacks.  The
thing is that you *will* need callbacks and an AM layer to be able to
achieve that.  I agree this can be useful.  Now this is a separate
clause in the SEQUENCE DDLs, so it sounds to me like an entirely
different feature.

FWIW, MSSQL has a concept of custom data types for one, though these
need to map to integers (see user-defined_integer_type).

Another thing is the SQL specification.  You or Vik will very likely
correct me here, but the spec mentions that sequences need to work on
integer values.  A USING clause means that we already diverge from it,
perhaps it is OK to diverge more.  How about DDL properties like
Min/Max or increment, then?

> I think the proposed patch covers too broad a range of abstraction levels.
> The use cases described above are very high level and are just concerned
> with how you get the next value.  The current internal sequence state would
> be stored in whatever way it is stored now.  But this patch also includes
> callbacks for very low-level-seeming concepts like table AMs and
> persistence.  Those seem like different things.  And the two levels should
> be combinable.  Maybe I want a local sequence of uuids or a global sequence
> of uuids, or a local sequence of integers or a global sequence of integers.
> I mean, I haven't thought this through, but I get the feeling that there
> should be more than one level of API around this.

That's a tricky question, and I don't really know how far this needs
to go.  FWIW, like table AMs I don't want the callbacks to be set in
stone across major releases.  Now I am worrying a bit about designing
callbacks that are generic, still impact performance because they
require more catalog lookups and/or function point manipulations for
the default cases.  Separating the computation and the in-core SQL
functions in a cleaner way is a step that helps in any case, IMO,
though I agree that the design of the callbacks influences how much is
exposed to users and AM developers.  Having only a USING clause that
gives support to integer-based results while providing a way to force
the computation is useful.  Custom data types that can be plugged into
the callbacks are also useful, still they are doing to require an AM
callback layer so as an AM can decide what it needs to do with the
data type given by the user in input of CREATE SEQUENCE.
--
Michael