Hi,
First time PostgreSQL contributor here :)
I wanted to be able to allocate a bunch of numbers from a sequence at
once. Multiple people seem to be struggling with this
(https://stackoverflow.com/questions/896274/select-multiple-ids-from-a-postgresql-sequence,
https://www.depesz.com/2008/03/20/getting-multiple-values-from-sequences/).
I propose to add an extra argument to nextval() that specifies how many
numbers you want to allocate (default 1).
The attached patch (based on master) passes `./configure
--enable-cassert --enable-debug && make && make check`, including the
newly added regression tests.
It does change the signature of nextval_internal(), not sure if that's
considered backwards compatibility breaking (for extensions?).
-- Jille
From 403993dfea71068070185dd14fa3f5ff26d5f791 Mon Sep 17 00:00:00 2001
From: Jille Timmermans <ji...@quis.cx>
Date: Sun, 27 Feb 2022 10:20:22 +0100
Subject: [PATCH] Add an argument to nextval() to grab multiple consecutive
sequence numbers
---
doc/src/sgml/func.sgml | 8 +++--
src/backend/commands/sequence.c | 46 +++++++++++++++++++++-----
src/backend/executor/execExprInterp.c | 2 +-
src/backend/optimizer/util/clauses.c | 2 +-
src/include/catalog/pg_proc.dat | 3 ++
src/include/commands/sequence.h | 2 +-
src/test/regress/expected/sequence.out | 41 +++++++++++++++++++++++
src/test/regress/sql/sequence.sql | 12 +++++++
8 files changed, 102 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index df3cd5987b..5923ecc38e 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17605,7 +17605,7 @@ $.* ? (@ like_regex "^\\d+$")
<indexterm>
<primary>nextval</primary>
</indexterm>
- <function>nextval</function> ( <type>regclass</type> )
+ <function>nextval</function> ( <type>regclass</type> <optional>, <type>bigint</type> </optional> )
<returnvalue>bigint</returnvalue>
</para>
<para>
@@ -17618,7 +17618,11 @@ $.* ? (@ like_regex "^\\d+$")
values beginning with 1. Other behaviors can be obtained by using
appropriate parameters in the <xref linkend="sql-createsequence"/>
command.
- </para>
+ </para>
+ <para>
+ To grab multiple values you can pass an integer to <function>nextval</function>.
+ It will allocate that many consecutive numbers from the sequence and return the last value.
+ </para>
<para>
This function requires <literal>USAGE</literal>
or <literal>UPDATE</literal> privilege on the sequence.
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index ab592ce2f1..79e2a1e7c0 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -570,7 +570,7 @@ nextval(PG_FUNCTION_ARGS)
*/
relid = RangeVarGetRelid(sequence, NoLock, false);
- PG_RETURN_INT64(nextval_internal(relid, true));
+ PG_RETURN_INT64(nextval_internal(relid, true, 1));
}
Datum
@@ -578,11 +578,20 @@ nextval_oid(PG_FUNCTION_ARGS)
{
Oid relid = PG_GETARG_OID(0);
- PG_RETURN_INT64(nextval_internal(relid, true));
+ PG_RETURN_INT64(nextval_internal(relid, true, 1));
+}
+
+Datum
+nextval_oid_num(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ int64 num = PG_GETARG_INT64(1);
+
+ PG_RETURN_INT64(nextval_internal(relid, true, num));
}
int64
-nextval_internal(Oid relid, bool check_permissions)
+nextval_internal(Oid relid, bool check_permissions, int64 request)
{
SeqTable elm;
Relation seqrel;
@@ -605,6 +614,17 @@ nextval_internal(Oid relid, bool check_permissions)
bool cycle;
bool logit = false;
+ if (request < 1)
+ {
+ char buf[100];
+
+ snprintf(buf, sizeof(buf), INT64_FORMAT, request);
+ ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("nextval: can't request %s values from a sequence",
+ buf)));
+ }
+
/* open and lock sequence */
init_sequence(relid, &elm, &seqrel);
@@ -627,11 +647,10 @@ nextval_internal(Oid relid, bool check_permissions)
*/
PreventCommandIfParallelMode("nextval()");
- if (elm->last != elm->cached) /* some numbers were cached */
+ if (elm->increment != 0 && (elm->cached - elm->last) / elm->increment >= request) /* enough numbers were cached */
{
Assert(elm->last_valid);
- Assert(elm->increment != 0);
- elm->last += elm->increment;
+ elm->last += elm->increment * request;
relation_close(seqrel, NoLock);
last_used_seq = elm;
return elm->last;
@@ -652,8 +671,17 @@ nextval_internal(Oid relid, bool check_permissions)
seq = read_seq_tuple(seqrel, &buf, &seqdatatuple);
page = BufferGetPage(buf);
+ if (elm->cached != elm->last && elm->cached == seq->last_value) {
+ /*
+ * There are some numbers in the cache, and we can grab the numbers directly following those.
+ * We can fetch fewer new numbers and claim the numbers from the cache.
+ */
+ request -= elm->cached - elm->last;
+ }
+
elm->increment = incby;
last = next = result = seq->last_value;
+ cache += request-1;
fetch = cache;
log = seq->log_cnt;
@@ -703,7 +731,7 @@ nextval_internal(Oid relid, bool check_permissions)
if ((maxv >= 0 && next > maxv - incby) ||
(maxv < 0 && next + incby > maxv))
{
- if (rescnt > 0)
+ if (rescnt >= request)
break; /* stop fetching */
if (!cycle)
{
@@ -726,7 +754,7 @@ nextval_internal(Oid relid, bool check_permissions)
if ((minv < 0 && next < minv - incby) ||
(minv >= 0 && next + incby < minv))
{
- if (rescnt > 0)
+ if (rescnt >= request)
break; /* stop fetching */
if (!cycle)
{
@@ -749,7 +777,7 @@ nextval_internal(Oid relid, bool check_permissions)
log--;
rescnt++;
last = next;
- if (rescnt == 1) /* if it's first result - */
+ if (rescnt == request) /* if this is the Nth result when we're requesting N numbers - */
result = next; /* it's what to return */
}
}
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index d6f7d7c2d7..f600a4ea9f 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -2547,7 +2547,7 @@ ExecEvalCurrentOfExpr(ExprState *state, ExprEvalStep *op)
void
ExecEvalNextValueExpr(ExprState *state, ExprEvalStep *op)
{
- int64 newval = nextval_internal(op->d.nextvalueexpr.seqid, false);
+ int64 newval = nextval_internal(op->d.nextvalueexpr.seqid, false, 1);
switch (op->d.nextvalueexpr.seqtypid)
{
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index a707dc9f26..417000277a 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -565,7 +565,7 @@ contain_volatile_functions_not_nextval(Node *clause)
static bool
contain_volatile_functions_not_nextval_checker(Oid func_id, void *context)
{
- return (func_id != F_NEXTVAL &&
+ return (func_id != F_NEXTVAL_REGCLASS && func_id != F_NEXTVAL_REGCLASS_INT8 &&
func_volatile(func_id) == PROVOLATILE_VOLATILE);
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 7de8cfc7e9..85453627c3 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3259,6 +3259,9 @@
{ oid => '1574', descr => 'sequence next value',
proname => 'nextval', provolatile => 'v', proparallel => 'u',
prorettype => 'int8', proargtypes => 'regclass', prosrc => 'nextval_oid' },
+{ oid => '1568', descr => 'sequence multiple next values',
+ proname => 'nextval', provolatile => 'v', proparallel => 'u',
+ prorettype => 'int8', proargtypes => 'regclass int8', prosrc => 'nextval_oid_num' },
{ oid => '1575', descr => 'sequence current value',
proname => 'currval', provolatile => 'v', proparallel => 'u',
prorettype => 'int8', proargtypes => 'regclass', prosrc => 'currval_oid' },
diff --git a/src/include/commands/sequence.h b/src/include/commands/sequence.h
index 9fecc41954..4cad144574 100644
--- a/src/include/commands/sequence.h
+++ b/src/include/commands/sequence.h
@@ -52,7 +52,7 @@ typedef struct xl_seq_rec
/* SEQUENCE TUPLE DATA FOLLOWS AT THE END */
} xl_seq_rec;
-extern int64 nextval_internal(Oid relid, bool check_permissions);
+extern int64 nextval_internal(Oid relid, bool check_permissions, int64 request);
extern Datum nextval(PG_FUNCTION_ARGS);
extern List *sequence_options(Oid relid);
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 71c2b0f1df..e6b739dfca 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -822,4 +822,45 @@ SELECT nextval('test_seq1');
3
(1 row)
+SELECT nextval('test_seq1', 2);
+ nextval
+---------
+ 5
+(1 row)
+
+SELECT nextval('test_seq1', 30);
+ nextval
+---------
+ 35
+(1 row)
+
DROP SEQUENCE test_seq1;
+-- grab multiple values
+CREATE SEQUENCE multi_seq;
+SELECT nextval('multi_seq', 2);
+ nextval
+---------
+ 2
+(1 row)
+
+SELECT nextval('multi_seq', -1); -- error
+ERROR: nextval: can't request -1 values from a sequence
+SELECT nextval('multi_seq', 6);
+ nextval
+---------
+ 8
+(1 row)
+
+CREATE SEQUENCE multi_reverse_seq INCREMENT BY -1;
+SELECT nextval('multi_reverse_seq', 2);
+ nextval
+---------
+ -2
+(1 row)
+
+SELECT nextval('multi_reverse_seq', 2);
+ nextval
+---------
+ -4
+(1 row)
+
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 7928ee23ee..591dc74d84 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -404,5 +404,17 @@ CREATE SEQUENCE test_seq1 CACHE 10;
SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
SELECT nextval('test_seq1');
+SELECT nextval('test_seq1', 2);
+SELECT nextval('test_seq1', 30);
DROP SEQUENCE test_seq1;
+
+-- grab multiple values
+CREATE SEQUENCE multi_seq;
+SELECT nextval('multi_seq', 2);
+SELECT nextval('multi_seq', -1); -- error
+SELECT nextval('multi_seq', 6);
+
+CREATE SEQUENCE multi_reverse_seq INCREMENT BY -1;
+SELECT nextval('multi_reverse_seq', 2);
+SELECT nextval('multi_reverse_seq', 2);
--
2.32.0
