Thread: return column id from insert
I asked a question about this earlier, and didn't get a response I understood! The issue is this: I need to make a database insert and then make an image with the resulting column id number, which is provided by a sequence. This has to be a single action, otherwise I could fall into a race condition with another insert going on concurrently. Any help much appreciated. Thanks Rory -- Rory Campbell-Lange <rory@campbell-lange.net> <www.campbell-lange.net>
On Tue, 2002-11-12 at 11:50, Rory Campbell-Lange wrote: > I asked a question about this earlier, and didn't get a response I > understood! > > The issue is this: > I need to make a database insert and then make an image with the > resulting column id number, which is provided by a sequence. > > This has to be a single action, otherwise I could fall into a race > condition with another insert going on concurrently. Use the function currval('sequence_name') to get the last value generated by that sequence in your current session. You must have previously called nextval('sequence_name') in the same session, either explicitly or through an insert to a table with a SERIAL type field. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "He that loveth father of mother more than me is not worthy of me; and he that loveth son or daughter more than me is not worthy of me. And he that taketh not his cross, and followeth after me, is not worthy of me." Matthew 10:37,38
Rory, > The issue is this: > I need to make a database insert and then make an image with the > resulting column id number, which is provided by a sequence. I think you mean "primary key" or "row id", not "column id". There is no "column id". > This has to be a single action, otherwise I could fall into a race > condition with another insert going on concurrently. No, you can't. PostgreSQL sequences are guarenteed 100% unique regardless of concurrent updates. Simply put: BEGIN TRANSACTION; INSERT INTO some_table ( columns ) VALUES ( values); SELECT CURRVAL('some_table_seq'); COMMIT TRANSACTION; Easy, no? Now please go read the online docs, or better yet, buy an introductory PostgreSQL book. There's a lot to learn. -Josh Berkus
Hi Josh Thanks very much for your email. Sorry about the column id faux pas. On 12/11/02, Josh Berkus (josh@agliodbs.com) wrote: > > The issue is this: > > I need to make a database insert and then make an image with the > > resulting column id number, which is provided by a sequence. > > I think you mean "primary key" or "row id", not "column id". There is > no "column id". Primary key is what I meant. > > > This has to be a single action, otherwise I could fall into a race > > condition with another insert going on concurrently. > > No, you can't. PostgreSQL sequences are guarenteed 100% unique > regardless of concurrent updates. Simply put: > > BEGIN TRANSACTION; > INSERT INTO some_table ( columns ) > VALUES ( values); > SELECT CURRVAL('some_table_seq'); > COMMIT TRANSACTION; > > Easy, no? Yeah. I guess what I meant to say was that if the cgi program grabbed an id and then tried to use that to make an explicit record or expected that to be the value of a subsequently inserted record there is no guarantee of that id being accurate. Using transactions eliminates this problem. > > Now please go read the online docs, or better yet, buy an introductory > PostgreSQL book. There's a lot to learn. Thanks. I'll have a good look at my Momjian and DBI books again tonight. Cheers Rory -- Rory Campbell-Lange <rory@campbell-lange.net> <www.campbell-lange.net>