Thread: 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
Attachment
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
Attachment
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