Thread: SERIAL error
I'm using v. 7.4.2, and am encountering a problem with serial types. On an insert into a table that results in failure for any number of reasone (mostly violation of contraints) I've found that the Sequence for the column of type serial will still bump the value up once. This can be seen when entering the next value with no problems, the value is 2 greater than the last value. Noticed this when experimenting with a serial column with a unique constriant, and then manually raising the serial value of a row to the next number due in the sequence, the first attempt caused an error, but on a second attempt it worked fine but the number was one higher than what i expected. It also occurs for errors pertaining to other columns, when using a two column table, one column with a not null constraint, and the other the serial column, entering in a null value for the first column causes the sequence to fire, and then following that with a valid entry you'll see the value for the serial column is +2 of the last value. This is still the case in a transaction. Anyway, was wondering if this needed to be reported as a bug? Seems to cause problems for certain generic uses for the serial type. Matthew Story
mstory@uchicago.edu wrote: > I'm using v. 7.4.2, and am encountering a problem with serial types. On an > insert into a table that results in failure for any number of reasone (mostly > violation of contraints) I've found that the Sequence for the column of type > serial will still bump the value up once. This is FAQ item number 4.15.4: http://www.postgresql.org/docs/faqs/FAQ.html#4.15.4 HTH, Mike Mascari
On Sat, Nov 27, 2004 at 10:45:54PM -0600, mstory@uchicago.edu wrote: > > I'm using v. 7.4.2, and am encountering a problem with serial types. On an > insert into a table that results in failure for any number of reasone (mostly > violation of contraints) I've found that the Sequence for the column of type > serial will still bump the value up once. This behavior is intentional. See the PostgreSQL documentation and the FAQ: http://www.postgresql.org/docs/7.4/static/functions-sequence.html http://www.postgresql.org/docs/faqs/FAQ.html#4.15.4 > Anyway, was wondering if this needed to be reported as a bug? Seems > to cause problems for certain generic uses for the serial type. What do you mean by "generic uses"? Sequences are for obtaining unique values; problems caused by assuming anything else are almost certainly application bugs. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Sat, 27 Nov 2004 mstory@uchicago.edu wrote: > > I'm using v. 7.4.2, and am encountering a problem with serial types. On an > insert into a table that results in failure for any number of reasone (mostly > violation of contraints) I've found that the Sequence for the column of type > serial will still bump the value up once. This can be seen when entering the > next value with no problems, the value is 2 greater than the last value. > Noticed this when experimenting with a serial column with a unique constriant, > and then manually raising the serial value of a row to the next number due in > the sequence, the first attempt caused an error, but on a second attempt it > worked fine but the number was one higher than what i expected. It also occurs > for errors pertaining to other columns, when using a two column table, one > column with a not null constraint, and the other the serial column, entering in > a null value for the first column causes the sequence to fire, and then > following that with a valid entry you'll see the value for the serial column is > +2 of the last value. This is still the case in a transaction. Anyway, was > wondering if this needed to be reported as a bug? Seems to cause problems for > certain generic uses for the serial type. This is the intended behavior. SERIALS are meant to give non-repeating values but give no guarantees for sequentialness (mostly to avoid having to wait to see if a previous transaction that touched the serial has committed successfully).
Hi,
On Sat, Nov 27, 2004 at 10:45:54PM -0600, mstory@uchicago.edu wrote:
>
> I'm using v. 7.4.2, and am encountering a problem with serial types. On an
> insert into a table that results in failure for any number of reasone (mostly
> violation of contraints) I've found that the Sequence for the column of type
> serial will still bump the value up once. This can be seen when entering the
> next value with no problems, the value is 2 greater than the last value.
> Noticed this when experimenting with a serial column with a unique constriant,
> and then manually raising the serial value of a row to the next number due in
> the sequence, the first attempt caused an error, but on a second attempt it
> worked fine but the number was one higher than what i expected. It also occurs
> for errors pertaining to other columns, when using a two column table, one
> column with a not null constraint, and the other the serial column, entering in
> a null value for the first column causes the sequence to fire, and then
> following that with a valid entry you'll see the value for the serial column is
> +2 of the last value. This is still the case in a transaction. Anyway, was
> wondering if this needed to be reported as a bug?
This is by design. When using a serial datatype, the default value of
the column is set as nextval('sequence_name'). Calls to nextval are
never rolled back.
Each value given out by the nextval function is only ever returned once
(unless it is reset with setval).
See http://www.postgresql.org/docs/7.4/static/functions-sequence.html
> Seems to cause problems for
> certain generic uses for the serial type.
These uses probably need to make use of nextval() and currval() as
appropriate.
Hope this helps,
Joe
My mistake, in the future i will consult the FAQ first, though i'm a bit surprised i missed it in the documentation. Much thanks for the help. By generic uses i guess i meant the uses one might have for creating a sequence as an independant object in the database, and not the sequence in a table, and this behavior surely would not frustrate that sort of use. Many thanks. Matthew Story Quoting Michael Fuhr <mike@fuhr.org>: > On Sat, Nov 27, 2004 at 10:45:54PM -0600, mstory@uchicago.edu wrote: > > > > I'm using v. 7.4.2, and am encountering a problem with serial types. On > an > > insert into a table that results in failure for any number of reasone > (mostly > > violation of contraints) I've found that the Sequence for the column of > type > > serial will still bump the value up once. > > This behavior is intentional. See the PostgreSQL documentation and > the FAQ: > > http://www.postgresql.org/docs/7.4/static/functions-sequence.html > http://www.postgresql.org/docs/faqs/FAQ.html#4.15.4 > > > Anyway, was wondering if this needed to be reported as a bug? Seems > > to cause problems for certain generic uses for the serial type. > > What do you mean by "generic uses"? Sequences are for obtaining > unique values; problems caused by assuming anything else are almost > certainly application bugs. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >