pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: problem with sequence.....
Date: Wed, 16 Aug 2006 10:17:35 -0400
Message-ID: <20060816141735.GB25228@phlogiston.dyndns.org> (raw)
In-Reply-To: <A70AE049E81A6A40879D5DC0AC8C38C906EA6244@venus.infics.com>
References: <A70AE049E81A6A40879D5DC0AC8C38C906EA6244@venus.infics.com>
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
view thread (2+ messages)
Message-ID: <20060816141735.GB25228@phlogiston.dyndns.org> Permalink: ../20060816141735.GB25228@phlogiston.dyndns.org/ Also on: postgresql.org/message-id/20060816141735.GB25228@phlogiston.dyndns.org ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-sql@postgresql.org Cc: ajs@crankycanuck.ca Subject: Re: problem with sequence..... In-Reply-To: <20060816141735.GB25228@phlogiston.dyndns.org> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox