Received: from symcom.com ([209.11.41.82]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f33DOjK66834 for ; Tue, 3 Apr 2001 09:24:45 -0400 (EDT) (envelope-from pgsql@symcom.com) Message-Id: <200104031324.f33DOjK66834@postgresql.org> Received: from WorldClient [209.11.41.82] by symcom.com [209.11.41.82] with SMTP (MDaemon.v3.5.0.R) for ; Tue, 03 Apr 2001 09:33:34 -0400 Date: Tue, 03 Apr 2001 09:33:34 -0400 From: "postgresql" To: "Michael Fork" , "Bruce Momjian" , "PgSQL-SQL" Subject: Re: serial type; race conditions X-Mailer: WorldClient Pro 2.2.1 In-Reply-To: X-MDRemoteIP: 209.11.41.82 X-Return-Path: pgsql@symcom.com X-MDaemon-Deliver-To: pgsql-sql@postgresql.org X-Archive-Number: 200104/27 X-Sequence-Number: 2366 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 To: postgresql 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 > > 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 > > > >