Skip site navigation (1) Skip section navigation (2)

Re: problem with sequence number using a trigger

From: MITCHELL CIFUENTES <mitchell(dot)cifuentes(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: problem with sequence number using a trigger
Date: 2009-06-18 02:46:24
Message-ID: f3f9e90f0906171946l1593826h8b501f39bd8b1f19@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
>

In response to

pgsql-novice by date

Next:From: Andreas JuniusDate: 2009-06-18 07:24:43
Subject: UUID, performance of primary keys
Previous:From: Tom LaneDate: 2009-06-17 22:40:43
Subject: Re: problem with sequence number using a trigger

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group