Re: serial type; race conditions
От | postgresql |
---|---|
Тема | Re: serial type; race conditions |
Дата | |
Msg-id | 200104031324.f33DOjK66834@postgresql.org обсуждение исходный текст |
Ответ на | Re: serial type; race conditions (Michael Fork <mfork@toledolink.com>) |
Список | pgsql-sql |
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 > > > >
В списке pgsql-sql по дате отправления: