Re: return column id from insert

Hi Josh

Thanks very much for your email. Sorry about the column id faux pas.

On 12/11/02, Josh Berkus (josh(at)agliodbs(dot)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(at)campbell-lange(dot)net>
<www.campbell-lange.net>