Re: problem with sequence number using a trigger

Hello Tom,

Thanks for the answer. I've learning databases and postgresql for a month.
It is good to know that there is nothing wrong with the trigger and that the
gap
in the sequence is something normal.

Thanks again.
regards
Mitchell

2009/6/17 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

> MITCHELL CIFUENTES <mitchell(dot)cifuentes(at)gmail(dot)com> writes:
> > I hope you can help with this. I have a BEFORE trigger that run every
> time
> > an event insert o update occurs
> > on a table called stock. It is working fine inserting the correct data on
> > the table if the conditions are met,
> > and inserting nothing if not.
> > The problem is when it rejects the insert statement, the sequence number
> > stock_id gets incremented
> > even though nothing is inserted on the table. What can i do to stop
> stock_id
> > from incrementing??.
>
> Well, what you *really* ought to do is disabuse yourself of the illusion
> that the stock_id sequence must be gap-free. You are not going to be
> able to prevent insertion failures from leaving gaps. (Or at least not
> without a whole lot more pain than it's probably worth --- try searching
> the archives for "gap-free sequence" if you insist on that.)
>
> What you could do in this particular case is not have the sequence value
> come from the column's default expression, but let the trigger fill it
> in, after it's decided everything is okay. That will fix the problem
> as you describe it. What it won't fix is gaps caused by insertions that
> fail somewhere past the trigger stage (for example, due to a
> unique-index violation, or an error in a later trigger, or running out
> of disk space).
>
> regards, tom lane
>