Thread: serial type; race conditions
Hi, I'm using serial fields to generate IDs for almost all object in my database. I insert an empty row, get the CURRVAL() of the sequence and then update to that value. I had understood (and now, I can't find the reference to back this up) that serial is implemented in such a way that race conditions between DB connections can't happen. Is this true? Thanks, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. jkakar@expressus.com 708-1641 Lonsdale Avenue V: (604) 903-6994 North Vancouver, BC, V7M 2J5
> Hi, > > I'm using serial fields to generate IDs for almost all object in my > database. I insert an empty row, get the CURRVAL() of the sequence > and then update to that value. > > I had understood (and now, I can't find the reference to back this up) > that serial is implemented in such a way that race conditions between > DB connections can't happen. > > Is this true? Safe. See FAQ item. currval is for your backend only. -- 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, Pennsylvania19026
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. 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.... Ted -----Original Message----- From: Bruce Momjian <pgman@candle.pha.pa.us> To: jkakar@expressus.com Date: Mon, 26 Mar 2001 16:47:37 -0500 (EST) Subject: Re: [SQL] serial type; race conditions > > Hi, > > > > I'm using serial fields to generate IDs for almost all object in my > > database. I insert an empty row, get the CURRVAL() of the sequence > > and then update to that value. > > > > I had understood (and now, I can't find the reference to back this > up) > > that serial is implemented in such a way that race conditions between > > DB connections can't happen. > > > > Is this true? > > Safe. See FAQ item. currval is for your backend only. > > -- > 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 > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org)
> 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, Pennsylvania19026
I ditto what Bruce said - trying to get a true sequence without gaps is a
losing battle. Why don't you, instead, use a serial column as the real
sequence, and then a trigger that simply inserts max(foo) + 1 in a
different column? Then when you need to know the column, do something
like:
SELECT number_i_care_about FROM table WHERE serial_number =
currval('serial_number_seq');
ap
----------------------------------------------------------------------
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
(Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
andrew_perrin@unc.edu - http://www.unc.edu/~aperrin
On Thu, 29 Mar 2001, Bruce Momjian wrote:
> > 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
If you are looking to have every number accounted for, something like this
will work:
INSERT INTO table (serial_col) SELECT nextval('seq_serial_col');
UPDATE table SET foo = 'bar' , ... WHERE serial_col = (SELECT
currval('seq_serial_col'));
then, if the update fails, the number will be accounted for in the
table (Note that you could not use not null on any of the columns).
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Thu, 29 Mar 2001, postgresql wrote:
> 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.
>
> 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....
>
> Ted
>
>
> -----Original Message-----
> From: Bruce Momjian <pgman@candle.pha.pa.us>
> To: jkakar@expressus.com
> Date: Mon, 26 Mar 2001 16:47:37 -0500 (EST)
> Subject: Re: [SQL] serial type; race conditions
>
> > > Hi,
> > >
> > > I'm using serial fields to generate IDs for almost all object in my
> > > database. I insert an empty row, get the CURRVAL() of the
> sequence
> > > and then update to that value.
> > >
> > > I had understood (and now, I can't find the reference to back this
> > up)
> > > that serial is implemented in such a way that race conditions
> between
> > > DB connections can't happen.
> > >
> > > Is this true?
> >
> > Safe. See FAQ item. currval is for your backend only.
> >
> > --
> > 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
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to
> > majordomo@postgresql.org)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
Probably just me: but I don't see the point. Consider:
* User 1 commences insert transaction: grabs nextval(sequence),
max(foo)
* User 2 commences insert transaction: grabs nextval(sequence),
max(foo)
* User 1 commits
* User 2 commits (insert has sequence value one higher than for User
1, but same value for max(foo) + 1), or
* If foo has a unique constraint, transaction 2 will roll back.
Either way, I don't see what has been gained. All of the messages I have
read on this subject conclude with the same point: choice is to:
* accept unique sequence with holes
* accept loss of concurrency (as in the example above).
Or am I just missing the point?
> -----Original Message-----
> From: Andrew Perrin [SMTP:aperrin@socrates.berkeley.edu]
> Sent: Thursday, March 29, 2001 8:46 AM
> To: pgsql@symcom.com
> Cc: PgSQL-SQL
> Subject: Re: serial type; race conditions
>
> I ditto what Bruce said - trying to get a true sequence without gaps is a
> losing battle. Why don't you, instead, use a serial column as the real
> sequence, and then a trigger that simply inserts max(foo) + 1 in a
> different column? Then when you need to know the column, do something
> like:
>
> SELECT number_i_care_about FROM table WHERE serial_number =
> currval('serial_number_seq');
>
> ap
>
> ----------------------------------------------------------------------
> Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
> (Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
> andrew_perrin@unc.edu - http://www.unc.edu/~aperrin
>
> On Thu, 29 Mar 2001, Bruce Momjian wrote:
>
> > > 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
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Thus spake postgresql
> 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.
>
> 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.
Why is it a requirement to not use transactions? That's the proper way
to maintain data consistency in a relational database.
I have suggested a method before to create a sequence with out any missing
numbers but let me explain it further. I have never used this method with
PostgreSQL but I am sure it has the features needed to make it work.
Create a table called numbers with two fields, seq_name and seq_num. Pick a
maximum number of concurrent users for a specific serial number. Seed the
table with the sequence name and number for each sequence name and number
from 1 to the maximum you picked. So, if you had 3 sequences and 10 as
the max the table should have 30 entries in it.
To get the next number you start a transaction. You then search for the
lowest number in the table for the required sequence name that is not locked
by another process (this is the part I am fuzzy on for PostgreSQL - how do
you exclude locked records from your search just based on them being locked?)
and lock it. You then use that number in your transaction and add your max
to it. In the above example of 10 then the first time you use it you will get
1 and the number will be increased to 11. If your transaction is rolled back
then you may have a hole if someone else used a number but it will be filled
the next time that someone requests a number from that sequence. They aren't
strictly speaking consecutive but they are complete.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
Micheal,
Thanks, I was thinking that something like this should work.
However, I am having a problem with it. here is what I am doing.
begin;
INSERT INTO table (serial_col) (SELECT nextval('seq_serial_col'));
commit;
first I needed to add parens around the select statement. The
results are not what I expected. If I executed this a few times, when I
looked at the table what I saw was:
serial_col | seq_serial_col
1 | 2
3 | 4
5 | 6
etc.
I had thought I would do the insert, grab the currval of transaction
passing it back to my app. commit, then do an update. I can not
seem to get the seq to work.
Ted
-----Original Message-----
From: Michael Fork <mfork@toledolink.com>
To: postgresql <pgsql@symcom.com>
Date: Thu, 29 Mar 2001 10:04:46 -0500 (EST)
Subject: Re: [SQL] serial type; race conditions
> If you are looking to have every number accounted for, something
like
> this
> will work:
>
> INSERT INTO table (serial_col) SELECT nextval('seq_serial_col');
>
> UPDATE table SET foo = 'bar' , ... WHERE serial_col = (SELECT
> currval('seq_serial_col'));
>
> then, if the update fails, the number will be accounted for in the
> table (Note that you could not use not null on any of the columns).
>
> Michael Fork - CCNA - MCP - A+
> Network Support - Toledo Internet Access - Toledo Ohio
>
> On Thu, 29 Mar 2001, postgresql wrote:
>
> > 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.
> >
> > 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....
> >
> > Ted
> >
> >
> > -----Original Message-----
> > From: Bruce Momjian <pgman@candle.pha.pa.us>
> > To: jkakar@expressus.com
> > Date: Mon, 26 Mar 2001 16:47:37 -0500 (EST)
> > Subject: Re: [SQL] serial type; race conditions
> >
> > > > Hi,
> > > >
> > > > I'm using serial fields to generate IDs for almost all object in
> my
> > > > database. I insert an empty row, get the CURRVAL() of the
> > sequence
> > > > and then update to that value.
> > > >
> > > > I had understood (and now, I can't find the reference to back
> this
> > > up)
> > > > that serial is implemented in such a way that race conditions
> > between
> > > > DB connections can't happen.
> > > >
> > > > Is this true?
> > >
> > > Safe. See FAQ item. currval is for your backend only.
> > >
> > > --
> > > 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
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister
> command
> > > (send "unregister YourEmailAddressHere" to
> > > majordomo@postgresql.org)
> >
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an
appropriate
> > subscribe-nomail command to majordomo@postgresql.org so
that your
> > message can get through to the mailing list cleanly
> >
>
>
It seems to just feel like conflicting requirements, so it's a tug-of-war.
I've always done it by doing all the processing I can and then, from inside
a transaction, do
update seed from seed_table set seed=seed+1 where id='abc';
insert into some_table values ((select seed from seed_table where id='abc'),
other_stuff);
The processing would be concurrent and only the update & insert would be
"serialized". It would be portable and shouldn't contain holes, but is
slower than sequences.
Gerald.
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Jeff Eckermann
Sent: Thursday, March 29, 2001 10:48 AM
To: 'Andrew Perrin'
Cc: PgSQL-SQL
Subject: [SQL] RE: serial type; race conditions
Probably just me: but I don't see the point. Consider:
* User 1 commences insert transaction: grabs nextval(sequence),
max(foo)
* User 2 commences insert transaction: grabs nextval(sequence),
max(foo)
* User 1 commits
* User 2 commits (insert has sequence value one higher than for User
1, but same value for max(foo) + 1), or
* If foo has a unique constraint, transaction 2 will roll back.
Either way, I don't see what has been gained. All of the messages I have
read on this subject conclude with the same point: choice is to:
* accept unique sequence with holes
* accept loss of concurrency (as in the example above).
Or am I just missing the point?
> -----Original Message-----
> From: Andrew Perrin [SMTP:aperrin@socrates.berkeley.edu]
> Sent: Thursday, March 29, 2001 8:46 AM
> To: pgsql@symcom.com
> Cc: PgSQL-SQL
> Subject: Re: serial type; race conditions
>
> I ditto what Bruce said - trying to get a true sequence without gaps is a
> losing battle. Why don't you, instead, use a serial column as the real
> sequence, and then a trigger that simply inserts max(foo) + 1 in a
> different column? Then when you need to know the column, do something
> like:
>
> SELECT number_i_care_about FROM table WHERE serial_number =
> currval('serial_number_seq');
>
> ap
>
> ----------------------------------------------------------------------
> Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
> (Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
> andrew_perrin@unc.edu - http://www.unc.edu/~aperrin
>
> On Thu, 29 Mar 2001, Bruce Momjian wrote:
>
> > > 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
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
OK:
* Transaction 1 commences, sets seed to seed + 1
* Transaction 2 commences, sets seed to seed + 1
* Transaction 1 inserts into some_table, selects seed (sequence now
has a hole)
* Transaction 2 inserts into some_table, selects seed (same value as
just used by transaction 1)
* The second transaction to commit will either create a duplicate
"seed" value, or roll back because of a unique constraint, still leaving a
hole in the sequence.
> -----Original Message-----
> From: Gerald Gutierrez [SMTP:gutz@kalador.com]
> Sent: Wednesday, April 04, 2001 7:24 PM
> To: Jeff Eckermann; 'Andrew Perrin'
> Cc: PgSQL-SQL
> Subject: RE: [SQL] RE: serial type; race conditions
>
>
> It seems to just feel like conflicting requirements, so it's a tug-of-war.
>
> I've always done it by doing all the processing I can and then, from
> inside
> a transaction, do
>
> update seed from seed_table set seed=seed+1 where id='abc';
> insert into some_table values ((select seed from seed_table where
> id='abc'),
> other_stuff);
>
> The processing would be concurrent and only the update & insert would be
> "serialized". It would be portable and shouldn't contain holes, but is
> slower than sequences.
>
>
> Gerald.
>
>
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Jeff Eckermann
> Sent: Thursday, March 29, 2001 10:48 AM
> To: 'Andrew Perrin'
> Cc: PgSQL-SQL
> Subject: [SQL] RE: serial type; race conditions
>
>
> Probably just me: but I don't see the point. Consider:
> * User 1 commences insert transaction: grabs nextval(sequence),
> max(foo)
> * User 2 commences insert transaction: grabs nextval(sequence),
> max(foo)
> * User 1 commits
> * User 2 commits (insert has sequence value one higher than for User
> 1, but same value for max(foo) + 1), or
> * If foo has a unique constraint, transaction 2 will roll back.
>
> Either way, I don't see what has been gained. All of the messages I have
> read on this subject conclude with the same point: choice is to:
> * accept unique sequence with holes
> * accept loss of concurrency (as in the example above).
>
> Or am I just missing the point?
>
> > -----Original Message-----
> > From: Andrew Perrin [SMTP:aperrin@socrates.berkeley.edu]
> > Sent: Thursday, March 29, 2001 8:46 AM
> > To: pgsql@symcom.com
> > Cc: PgSQL-SQL
> > Subject: Re: serial type; race conditions
> >
> > I ditto what Bruce said - trying to get a true sequence without gaps is
> a
> > losing battle. Why don't you, instead, use a serial column as the real
> > sequence, and then a trigger that simply inserts max(foo) + 1 in a
> > different column? Then when you need to know the column, do something
> > like:
> >
> > SELECT number_i_care_about FROM table WHERE serial_number =
> > currval('serial_number_seq');
> >
> > ap
> >
> > ----------------------------------------------------------------------
> > Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
> > (Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
> > andrew_perrin@unc.edu - http://www.unc.edu/~aperrin
> >
> > On Thu, 29 Mar 2001, Bruce Momjian wrote:
> >
> > > > 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
> > >
> > > ---------------------------(end of
> broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly