Hi all
I'm seeing enough questions on pgsql-general and stack overflow to
suggest that the docs for how sequences interact with transaction
rollback. Take the most recent post on -general, where the person read
at least the tutorial, but had no idea about the exemption.
The attached patch:
- Moves the note about nextval() from the footer to be inside the
nextval description
- Adds an xref from the advanced-transactions tutorial where the poster
noted their point of confusion, noting the exemption and pointing to the
docs on nextval.
- A pointer from the docs on SERIAL types to the nextval notes on tx
rollback.
Comments would be appreciated.
--
Craig Ringer
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index 218988e..423f09e 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -237,6 +237,16 @@ COMMIT;
<command>COMMIT</>, and all our updates so far will be canceled.
</para>
+ <note>
+ <para>
+ A few things in the database are exempt from rollback. The most
+ important are <literal>SEQUENCE</>s - which are used the counters in
+ <literal>SERIAL</> columns. See <xref linkend="functions-sequence">. Any
+ function or type with special transactional behavior will have an explanatory
+ note in its documentation.
+ </para>
+ </note>
+
<para>
<productname>PostgreSQL</> actually treats every SQL statement as being
executed within a transaction. If you do not issue a <command>BEGIN</>
@@ -251,8 +261,8 @@ COMMIT;
<para>
Some client libraries issue <command>BEGIN</> and <command>COMMIT</>
commands automatically, so that you might get the effect of transaction
- blocks without asking. Check the documentation for the interface
- you are using.
+ blocks without asking. Client libraries often call this "autocommit".
+ Check the documentation for the interface you are using.
</para>
</note>
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index afc82a2..cbde801 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -800,7 +800,19 @@ NUMERIC
<type>bigserial</type> are not true types, but merely
a notational convenience for creating unique identifier columns
(similar to the <literal>AUTO_INCREMENT</literal> property
- supported by some other databases). In the current
+ supported by some other databases).
+ </para>
+
+ <important>
+ <para>
+ Because they use <literal>SEQUENCE</literal>s, serial data types are
+ exempt from transactional rollback. This means they can have "holes"
+ or gaps where values are discarded. See <literal>nexval()</literal> in
+ <xref linkend="functions-sequence"> for details.
+ </para>
+ </important>
+
+ <para>In the current
implementation, specifying:
<programlisting>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 157de09..0296d3a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -9820,6 +9820,27 @@ nextval('foo'::text) <lineannotation><literal>foo</literal> is looked up at
execute <function>nextval</function> concurrently, each will safely receive
a distinct sequence value.
</para>
+
+ <important>
+ <para>
+ To avoid blocking concurrent transactions that obtain numbers from the
+ same sequence, a <function>nextval</function> operation is never rolled back;
+ that is, once a value has been fetched it is considered used, even if the
+ transaction that did the <function>nextval</function> later aborts. This means
+ that aborted transactions might leave unused <quote>holes</quote> in the
+ sequence of assigned values. <function>setval</function> operations are never
+ rolled back, either.
+ </para>
+ </important>
+
+ <para>
+ If a sequence object has been created with default parameters,
+ successive <function>nextval</function> calls will return successive values
+ beginning with 1. Other behaviors can be obtained by using
+ special parameters in the <xref linkend="sql-createsequence"> command;
+ see its command reference page for more information.
+ </para>
+
</listitem>
</varlistentry>
@@ -9883,31 +9904,17 @@ SELECT setval('foo', 42, false); <lineannotation>Next <function>nextval</> wi
The result returned by <function>setval</function> is just the value of its
second argument.
</para>
+ <important>
+ <para>
+ Changes to sequences made by setval() are not undone if the transaction
+ rolls back. See the note on nextval().
+ </para>
+ </important>
</listitem>
</varlistentry>
</variablelist>
</para>
- <para>
- If a sequence object has been created with default parameters,
- successive <function>nextval</function> calls will return successive values
- beginning with 1. Other behaviors can be obtained by using
- special parameters in the <xref linkend="sql-createsequence"> command;
- see its command reference page for more information.
- </para>
-
- <important>
- <para>
- To avoid blocking concurrent transactions that obtain numbers from the
- same sequence, a <function>nextval</function> operation is never rolled back;
- that is, once a value has been fetched it is considered used, even if the
- transaction that did the <function>nextval</function> later aborts. This means
- that aborted transactions might leave unused <quote>holes</quote> in the
- sequence of assigned values. <function>setval</function> operations are never
- rolled back, either.
- </para>
- </important>
-
</sect1>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers