Home Explore Blog CI



postgresql

4th chunk of `doc/src/sgml/ref/create_sequence.sgml`
603f0f35eb0b3e21ccbcf2cf846e2ced0bb4ada0627bceb10000000100000b71
 clause <literal>CACHE <replaceable
      class="parameter">cache</replaceable></literal> specifies how
      many sequence numbers are to be preallocated and stored in
      memory for faster access. The minimum value is 1 (only one value
      can be generated at a time, i.e., no cache), and this is also the
      default.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>OWNED BY</literal> <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable></term>
    <term><literal>OWNED BY NONE</literal></term>
    <listitem>
     <para>
      The <literal>OWNED BY</literal> option causes the sequence to be
      associated with a specific table column, such that if that column
      (or its whole table) is dropped, the sequence will be automatically
      dropped as well.  The specified table must have the same owner and be in
      the same schema as the sequence.
      <literal>OWNED BY NONE</literal>, the default, specifies that there
      is no such association.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

 <refsect1>
  <title>Notes</title>

  <para>
   Use <command>DROP SEQUENCE</command> to remove a sequence.
  </para>

  <para>
   Sequences are based on <type>bigint</type> arithmetic, so the range
   cannot exceed the range of an eight-byte integer
   (-9223372036854775808 to 9223372036854775807).
  </para>

  <para>
   Because <function>nextval</function> and <function>setval</function> calls are never
   rolled back, sequence objects cannot be used if <quote>gapless</quote>
   assignment of sequence numbers is needed.  It is possible to build
   gapless assignment by using exclusive locking of a table containing a
   counter; but this solution is much more expensive than sequence
   objects, especially if many transactions need sequence numbers
   concurrently.
  </para>

  <para>
   Unexpected results might be obtained if a <replaceable
   class="parameter">cache</replaceable> setting greater than one is
   used for a sequence object that will be used concurrently by
   multiple sessions.  Each session will allocate and cache successive
   sequence values during one access to the sequence object and
   increase the sequence object's <literal>last_value</literal> accordingly.
   Then, the next <replaceable class="parameter">cache</replaceable>-1
   uses of <function>nextval</function> within that session simply return the
   preallocated values without touching the sequence object.  So, any
   numbers allocated but not used within a session will be lost when
   that session ends, resulting in <quote>holes</quote> in the
   sequence.
  </para>

  <para>
   Furthermore, although multiple sessions are guaranteed to allocate
   distinct sequence values, the values might be generated out of
   sequence when all the sessions are considered.  For example,

Title: CREATE SEQUENCE: Cache, Ownership, Notes on Usage
Summary
This section describes the 'CACHE' and 'OWNED BY' options for sequences, with 'CACHE' determining pre-allocated sequence numbers and 'OWNED BY' linking the sequence to a table column for automatic deletion. It also includes important notes about sequence usage, such as limitations due to bigint arithmetic, the lack of rollback for nextval/setval, and potential issues with caching in concurrent sessions that can lead to gaps and out-of-sequence generation.