Обсуждение: Looking for help regarding getting the latest inserted sequence value.

Hello All,

We are looking for your help.The scenarion which we need to address
is,There are 2 threads and both of them are in separate transction and
insert the value to a same table and also sequence number field gets
incremented automotically for each of them.The problem we are facing
is,We will need to get back the appropriate id inserted for that
particualr record as it is used in some other places.

Right now we are doing it in 2 steps.inserting the record to table.And
getting the max(id) from the table.Now the problem is assume there is
another thread also does the insertion and commits that transction both
of the thread return the same id which is not desirable in our case.

It would be really very much helpful to know the form of a query which
inserts record and also returns the latest inserted ID for that record
in a single query.

Thanks,
Prasanna.

On Thursday 28 June 2007 01:31:33 Mavinakuli, Prasanna (STSD) wrote:
> .And getting the max(id) from the table.

Instead of that, use select currval('sequence'). currval will

"
Return the value most recently obtained by nextval for this sequence in the
current session. (An error is reported if nextval has never been called for
this sequence in this session.) Notice that because this is returning a
session-local value, it gives a predictable answer whether or not other
sessions have executed nextval since the current session did.
"

(or so the docs tell me:
http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html).

jan

--
--------------------------------------------------------------
Jan de Visser                     jdevisser@digitalfairway.com

                Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------

Mavinakuli, Prasanna (STSD) wrote:
> Hello All,
>
> We are looking for your help.The scenarion which we need to address
> is,There are 2 threads and both of them are in separate transction and
> insert the value to a same table and also sequence number field gets
> incremented automotically for each of them.The problem we are facing
> is,We will need to get back the appropriate id inserted for that
> particualr record as it is used in some other places.
>
> Right now we are doing it in 2 steps.inserting the record to table.And
> getting the max(id) from the table.Now the problem is assume there is
> another thread also does the insertion and commits that transction both
> of the thread return the same id which is not desirable in our case.
>
> It would be really very much helpful to know the form of a query which
> inserts record and also returns the latest inserted ID for that record
> in a single query.

If you're on 8.2 the easiest way is to use INSERT RETURNING. For example:
INSERT INTO t (somefield) VALUES ('somevalue') RETURNING pkey

with pkey being the SERIAL field.

You can also do it with currval() on the sequence, but that requires two
queries.

//Magnus

--- Magnus Hagander <magnus@hagander.net> wrote:

> If you're on 8.2 the easiest way is to use INSERT RETURNING. For example:
> INSERT INTO t (somefield) VALUES ('somevalue') RETURNING pkey
>
> with pkey being the SERIAL field.

I don't want to derail the thread too much, but would it be nice if the returning could be used in
a insert sub-query?

INSERT INTO Childtable ( parentfkey, childname )
     VALUES ( ( INSERT INTO Parenttable ( parentname )
                     VALUES ( 'FRED' ) RETURNING pkey ), 'Jed');

This with one statement, you could insert into two or more tables.

Regards,
Richard Broersma Jr.

On Sat, Jun 30, 2007 at 11:21:59AM -0700, Richard Broersma Jr wrote:
> I don't want to derail the thread too much, but would it be nice if the returning could be used in
> a insert sub-query?

Absolutly, however the semantics are not so simple. I remember
something about when to invoke triggers? And what view should they get?
Does the trigger on the outer table get to see the effect of the nested
insert, for example.

I'm sure it will get done eventually, once the details have been sorted
out.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.