Обсуждение: 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>