Date: Wed, 16 Aug 2006 10:17:35 -0400
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: problem with sequence.....
Message-ID: <20060816141735.GB25228@phlogiston.dyndns.org>
References: <A70AE049E81A6A40879D5DC0AC8C38C906EA6244@venus.infics.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <A70AE049E81A6A40879D5DC0AC8C38C906EA6244@venus.infics.com>
User-Agent: Mutt/1.5.9i

On Wed, Aug 16, 2006 at 05:01:09PM +0530, Penchalaiah P. wrote:
> If again I pass correct values to that function that values can see in
> view data with increment value of sequence
> 
> 
> Any one can tell me how to stop that sequence value when ever I was
> passing wrong values to that function....

I'm not entirely sure I understood you, but if what you're asking is
for the sequence not to increment on transaction rollback, then you
can't have it.  The sequence system guarantees that the numbers will
be in increasing order (subject to rollover), but it does not
guarantee that there will be no gaps.  This is to avoid some
unpleasant concurrency side-effects from the no-gaps approach.  If
you really need that, then you have two choices:

1.	Roll your own, using some sort of interlock table.  This will
not play nicely with a lot of concurrent writes, however (which is
the disadvantage the current implementation is designed to avoid).

2.	In recent PostgreSQL releases, you could use a savepoint. So,
get the nextval() of your serial number.  Set a savepoint.  Try your
insert.  If that fails, roll back and save the currval() as VOIDed. 
(This isn't completely safe.  You can make it completely safe by
doing it in two transactions, but that's best left as an exercise for
the reader.)

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
If they don't do anything, we don't need their acronym.
		--Josh Hamilton, on the US FEMA