Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit673bcea

Browse files
committed
Docs: improve warnings about nextval() not producing gapless sequences.
In the documentation for nextval(), point out explicitly that INSERT ...ON CONFLICT will call nextval() if needed for the insertion case, whetheror not it ends up following the ON CONFLICT path. This seems to be amatter of some confusion, cf bug #14126, so let's be clear about it.Also mention the issue in the CREATE SEQUENCE reference page, since thatis another place where people might expect such things to be covered.Minor wording improvements nearby, as well.Back-patch to 9.5 where ON CONFLICT was introduced.
1 parenta3c17b2 commit673bcea

File tree

2 files changed

+24
-8
lines changed

2 files changed

+24
-8
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 14 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -11186,13 +11186,19 @@ nextval('foo'::text) <lineannotation><literal>foo</literal> is looked up at
1118611186

1118711187
<important>
1118811188
<para>
11189-
To avoid blocking concurrent transactions that obtain numbers from the
11190-
same sequence, a <function>nextval</function> operation is never
11189+
To avoid blocking concurrent transactions that obtain numbers from
11190+
thesame sequence, a <function>nextval</function> operation is never
1119111191
rolled back; that is, once a value has been fetched it is considered
11192-
used, even if the transaction that did the
11193-
<function>nextval</function> later aborts. This means that aborted
11194-
transactions might leave unused <quote>holes</quote> in the sequence
11195-
of assigned values.
11192+
used and will not be returned again. This is true even if the
11193+
surrounding transaction later aborts, or if the calling query ends
11194+
up not using the value. For example an <command>INSERT</> with
11195+
an <literal>ON CONFLICT</> clause will compute the to-be-inserted
11196+
tuple, including doing any required <function>nextval</function>
11197+
calls, before detecting any conflict that would cause it to follow
11198+
the <literal>ON CONFLICT</> rule instead. Such cases will leave
11199+
unused <quote>holes</quote> in the sequence of assigned values.
11200+
Thus, <productname>PostgreSQL</> sequence objects <emphasis>cannot
11201+
be used to obtain <quote>gapless</> sequences</emphasis>.
1119611202
</para>
1119711203
</important>
1119811204

@@ -11221,8 +11227,8 @@ nextval('foo'::text) <lineannotation><literal>foo</literal> is looked up at
1122111227
Return the value most recently returned by
1122211228
<function>nextval</> in the current session. This function is
1122311229
identical to <function>currval</function>, except that instead
11224-
of taking the sequence name as an argument itfetches the
11225-
value of the lastsequenceused by<function>nextval</function>
11230+
of taking the sequence name as an argument itrefers to whichever
11231+
sequence <function>nextval</function> was most recently applied to
1122611232
in the current session. It is an error to call
1122711233
<function>lastval</function> if <function>nextval</function>
1122811234
has not yet been called in the current session.

‎doc/src/sgml/ref/create_sequence.sgml

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -239,6 +239,16 @@ SELECT * FROM <replaceable>name</replaceable>;
239239
(-9223372036854775808 to 9223372036854775807).
240240
</para>
241241

242+
<para>
243+
Because <function>nextval</> and <function>setval</> calls are never
244+
rolled back, sequence objects cannot be used if <quote>gapless</>
245+
assignment of sequence numbers is needed. It is possible to build
246+
gapless assignment by using exclusive locking of a table containing a
247+
counter; but this solution is much more expensive than sequence
248+
objects, especially if many transactions need sequence numbers
249+
concurrently.
250+
</para>
251+
242252
<para>
243253
Unexpected results might be obtained if a <replaceable
244254
class="parameter">cache</replaceable> setting greater than one is

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp