From: Bruce Momjian <pgman@candle.pha.pa.us>
To: postgresql <pgsql@symcom.com>
Cc: PgSQL-SQL <pgsql-sql@postgresql.org>
Subject: Re: serial type; race conditions
Date: Thu, 29 Mar 2001 09:36:39 -0500 (EST)
Message-ID: <200103291436.JAA00463@candle.pha.pa.us> (raw)
In-Reply-To: <200103291431.JAA00250@candle.pha.pa.us>
> How does currval work if you are not inside a transaction. I have
> been experimenting with inserting into a table that has a sequence.
> If the insert fails (not using a transaction) because of bad client input
> then the next insert gets the proper next number in the sequence.
If you are in a transaction, and the INSERT succeeds but the transaction
rolls back, the sequence does not get reused. Each backend has a local
variable that holds the most recent sequence assigned. That is how
currval works.
>
> given sequence 1,2,3,4,5 exists
> insert into table date 1/111/01 (obviously wrong) insert fails...
> try again with good data, insert succeeds and gets number 6 in the
> sequence.
>
> i'm getting what I want. A sequence number that does not increment
> on a failed insert. However, how do I get the assigned sequence
> number with currval when I am not using a transaction? What
> happens when multiple users are inserting at the same time?
>
> I am trying to create a sequence with out any "missing" numbers. If
> there is a failure to insert, and a sequence number is "taken". I want
> the empty row.
>
> Thanks, .... it is getting clearer....
You really can't use sequences with no gaps. Sequence numbers are not
_held_ until commit because it would block other backends trying to get
sequence numbers.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: pgman@candle.pha.pa.us, pgsql@symcom.com
Subject: Re: serial type; race conditions
In-Reply-To: <200103291436.JAA00463@candle.pha.pa.us>
References: <200103291431.JAA00250@candle.pha.pa.us> <200103291436.JAA00463@candle.pha.pa.us>
* 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