Hi, On Sat, Nov 27, 2004 at 10:45:54PM -0600, [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster