Обсуждение: mysql's last_insert_id

hi,

how to implement mysql's last_insert_id() ?

i know that seqences and oids are great.
the one thing i miss is that they are not session specific.

i mean selecting last_value from seqence can give me a value which was
inserted by a different user.

regards
Michal

I'm doing this in an app right now by using stored procedures and sequences.
The stored procedure for a table will get the next value of the sequence and
use that in the insert statement.  It then returns that id as the result so
the application can use it elsewhere.

Eric Johnson

----- Original Message -----
From: "Michal Adamczakk" <pokryfka@artland.com.pl>
To: <pgsql-general@postgresql.org>
Sent: Friday, August 22, 2003 3:03 PM
Subject: [GENERAL] mysql's last_insert_id


> hi,
>
> how to implement mysql's last_insert_id() ?
>
> i know that seqences and oids are great.
> the one thing i miss is that they are not session specific.
>
> i mean selecting last_value from seqence can give me a value which was
> inserted by a different user.
>
> regards
> Michal
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

Hello,

> how to implement mysql's last_insert_id() ?
>
> i know that seqences and oids are great.
> the one thing i miss is that they are not session specific.
> i mean selecting last_value from seqence can give me a value
> which was inserted by a different user.

Am I wrong or do you misunderstand the documentation about sequences ?

If you read the documentation about the sequences functions
(http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functio
ns-sequence.html), you can see that the currval functions 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 even if other
sessions are executing nextval meanwhile.

So, if you need to do two inserts where you have to add the new added
sequence value, you can do something like :

INSERT INTO authors (pk_author_id,lastname,firstname) VALUES
(NEXTVAL('seq_author_id'),'Baguette','Bruno');
INSERT INTO books (fk_author_id,title) VALUES
(CURRVAL('seq_author_id'),'Deafness related bibliography');

Hope this helps :-)

Regards,

---------------------------------------
Bruno BAGUETTE - pgsql-ml@baguette.net

Michal Adamczakk wrote:

> hi,
>
> how to implement mysql's last_insert_id() ?
>
> i know that seqences and oids are great.
> the one thing i miss is that they are not session specific.
>
> i mean selecting last_value from seqence can give me a value which was
> inserted by a different user.
>
> regards
> Michal
>

The currval('seq_name') returns the last for the specific connection,
not the last returned of any
hth,
- Stuart

On Fri, 2003-08-22 at 22:03, Michal Adamczakk wrote:

> how to implement mysql's last_insert_id() ?
You will be able to use the "PQoidValue" function that returns the last
inserted row oid, just after an insert command.

Then if you want the newly inserted row you do something like this
"SELECT * FROM table_name WHERE oid=42", where 42 is the oid number
returned from the "PQoidValue".

Now ... I know that its possible to omit the oid's from large tables,
and then this method will not work anymore. What to do in this case
still eludes me (Anyone ?) :-)

/BL

On 28 Aug 2003, Bo Lorentsen wrote:

> Now ... I know that its possible to omit the oid's from large tables,
> and then this method will not work anymore. What to do in this case
> still eludes me (Anyone ?) :-)

Yes, never use the oid at all is my suggestion.

If you need a unique id for a row then add a serial column that will
provide that, and use currval('the_sequence') to get the last inserted
value in that column for a session.

--
/Dennis

On Thu, 2003-08-28 at 10:02, Dennis Björklund wrote:

> Yes, never use the oid at all is my suggestion.
Hmm, will oid's not change so that they are unique regardless, or will
oid be removed.

> If you need a unique id for a row then add a serial column that will
> provide that, and use currval('the_sequence') to get the last inserted
> value in that column for a session.
This is just not a general solution to this problem, and will need
awareness of the id type in the application layer.

/BL

On Thu, Aug 28, 2003 at 02:52:57PM +0200, Bo Lorentsen wrote:
> On Thu, 2003-08-28 at 10:02, Dennis Björklund wrote:
>
> > Yes, never use the oid at all is my suggestion.
> Hmm, will oid's not change so that they are unique regardless, or will
> oid be removed.

OIDs have never beebn unique, it's just that most databases never get big
enough to experience wraparound. They are also now optional per table and
may soon no longer be available by default.

> > If you need a unique id for a row then add a serial column that will
> > provide that, and use currval('the_sequence') to get the last inserted
> > value in that column for a session.
> This is just not a general solution to this problem, and will need
> awareness of the id type in the application layer.

There are various solutions. Some people use functions to do inserts, I just
use the string "currval(whatever)" in the application layer which the
database replaces with the appropriate value.

Whatever works for you.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato

On Fri, 2003-08-29 at 04:11, Martijn van Oosterhout wrote:

> OIDs have never beebn unique, it's just that most databases never get big
> enough to experience wraparound. They are also now optional per table and
> may soon no longer be available by default.
Ok, that may break some of my code :-( But as long as oid's are enabled
and are unique for the current table, I'm happy.

I made a general insert (executeUpdate) function to my general DB layer,
that returned the newly inserted row (to be able to use the default
initealized elements). This code will stop working when oid is disabled,
very sad thing.

I will have to push this row awareness to a higher level in the future.

But, how does PG refer to rows internally, and why can't we get this
reference in the application layer. I don't care if its an integer, long
or even a string, but I like to know the unique id of the currently and
newly inserted row, without knowing the datamodel (table layout).

> There are various solutions. Some people use functions to do inserts, I just
> use the string "currval(whatever)" in the application layer which the
> database replaces with the appropriate value.
Hmm, but what happends if more than one connection does this at the same
time ? Then, only one of the concurrent connections will have a insert
on the returned value, and the rest will get a wrong row reference
returned.

The only safe methode would be to do a "select nextval(whatever)", and
aply this number by "hand" to the insert, but that remove the
possibility to make general code even more, but it will be safe.

/BL

On 29 Aug 2003, Bo Lorentsen wrote:

> > There are various solutions. Some people use functions to do inserts,
> > I just use the string "currval(whatever)" in the application layer
> > which the database replaces with the appropriate value.
>
> Hmm, but what happends if more than one connection does this at the same
> time ? Then, only one of the concurrent connections will have a insert
> on the returned value, and the rest will get a wrong row reference
> returned.

That is not a problem, it's perfectly safe. It's all described in

http://www.postgresql.org/docs/7.3/static/functions-sequence.html

Basicly, currval() gives the last id for that sequence in that session. So
other sessions does not break anything.

> The only safe methode would be to do a "select nextval(whatever)", and
> aply this number by "hand" to the insert, but that remove the
> possibility to make general code even more, but it will be safe.

It's not needed. The following works fine (if the tables exists of course)
and has no problems with concurrency:

INSERT INTO foo(id,x) VALUES (DEFAULT, 'value');
INSERT INTO bar(id,foo_ref) VALUES (DEFAULT, currval('foo_id_seq'));

--
/Dennis

On Fri, Aug 29, 2003 at 08:36:50AM +0200, Bo Lorentsen wrote:
> I made a general insert (executeUpdate) function to my general DB layer,
> that returned the newly inserted row (to be able to use the default
> initealized elements). This code will stop working when oid is disabled,
> very sad thing.
>
> I will have to push this row awareness to a higher level in the future.

Not really, the sequences have very predicatble names. For tables X with
primary key Y, the sequence is usually called X_Y_seq.

> But, how does PG refer to rows internally, and why can't we get this
> reference in the application layer. I don't care if its an integer, long
> or even a string, but I like to know the unique id of the currently and
> newly inserted row, without knowing the datamodel (table layout).

Oh, there's a CTID but that doesn't keep over an update and isn't
transaction safe anyway. Mind, you could always just call your sequences
seq_<TABLENAME>, then you wouldn't have to guess. This is how DCL does it.

> > There are various solutions. Some people use functions to do inserts, I just
> > use the string "currval(whatever)" in the application layer which the
> > database replaces with the appropriate value.
> Hmm, but what happends if more than one connection does this at the same
> time ? Then, only one of the concurrent connections will have a insert
> on the returned value, and the rest will get a wrong row reference
> returned.

Wrong. What gave you that impression? nextval() and currval() work fine with
transactions, look through the docs.

> The only safe methode would be to do a "select nextval(whatever)", and
> aply this number by "hand" to the insert, but that remove the
> possibility to make general code even more, but it will be safe.

Like I said, currval() will do what you want. Make predicatable names for
your sequences and you won't need to worry about it anymore.

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato

On Fri, 2003-08-29 at 09:05, Dennis Björklund wrote:

> That is not a problem, it's perfectly safe. It's all described in
>
> http://www.postgresql.org/docs/7.3/static/functions-sequence.html
Ups, nice that ...

currval will generate an error if nextval is not called in current
session, insuring :-)

But, I still need to be aware of the datamodel.

/BL

On 29 Aug 2003, Bo Lorentsen wrote:

> currval will generate an error if nextval is not called in current
> session, insuring :-)

Yea, that is a good thing. It gives you the last generated id in your
session. If you have never generated an id with nextval() there is no id
to give you. It can't just give you an id from another session, now can
it.

--
/Dennis

Dennis Björklund wrote:

>It's not needed. The following works fine (if the tables exists of course)
>and has no problems with concurrency:
>
>INSERT INTO foo(id,x) VALUES (DEFAULT, 'value');
>INSERT INTO bar(id,foo_ref) VALUES (DEFAULT, currval('foo_id_seq'));
>
>
>
You don't even need the 'DEFAULT' word as I remember, but it makes for
easier code to follow.

On Fri, 2003-08-29 at 04:11, Martijn van Oosterhout wrote:

> OIDs have never beebn unique, it's just that most databases never get big
> enough to experience wraparound. They are also now optional per table and
> may soon no longer be available by default.
It would be a god idea to remove the oid reference from the FAQ
4.15.2, to make sure no one in the futhere makes my mistake again :-)

/BL

Bo Lorentsen wrote:
> On Fri, 2003-08-29 at 04:11, Martijn van Oosterhout wrote:
>
> > OIDs have never beebn unique, it's just that most databases never get big
> > enough to experience wraparound. They are also now optional per table and
> > may soon no longer be available by default.
> It would be a god idea to remove the oid reference from the FAQ
> 4.15.2, to make sure no one in the futhere makes my mistake again :-)

I have added the last clause to the FAQ:

    Finally, you could use the <A href="#4.16"><SMALL>OID</SMALL></A>
    returned from the <SMALL>INSERT</SMALL> statement to look up the
    default value, though this is probably the least portable approach,
    and the oid value will wrap around when it reaches 4 billion.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Mon, 2003-09-01 at 18:47, Bruce Momjian wrote:

> I have added the last clause to the FAQ:
Are there not a case where a table does not relate to a oid anymore ? In
that case, it does not work at all :-)

/BL

Bo Lorentsen wrote:
> On Mon, 2003-09-01 at 18:47, Bruce Momjian wrote:
>
> > I have added the last clause to the FAQ:
> Are there not a case where a table does not relate to a oid anymore ? In
> that case, it does not work at all :-)

Yes, if you create a table without oids, yes, it isn't going to work,
but if you did that, I assume you already would know that the oid will
not be there.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073