Thread: GetLastInsertID ?
Happy new year to all pgsql fans ! Sorry for this trivial question but I couldn't find an answer in the archives : I use SERIAL type in tables for id columns in order to auto increment them. Is there a way to get the last inserted id in the table like we do with MySQL using the GetLastInsertID ? My need is to get back the new id just after inserting. Is nextval can respond to this need if I call it just before inserting ? Thank you very much. Stéphane
On Thu, 1 Jan 2004, Stephane Pinel wrote: > the last inserted id in the table like we do with MySQL using the > GetLastInsertID ? > > My need is to get back the new id just after inserting. Use currval() after you have inserted. http://www.postgresql.org/docs/current/static/functions-sequence.html -- /Dennis
If you use nextval before the insert, you should use the value in the insert itself. Afterwards you can use currval to get the just inserted value. I'm surprised you can't find this in the archives, it's asked on an almost weekly basis. It's also question 4.15.2 of the FAQ: http://www.postgresql.org/docs/faqs/FAQ.html#4.15.2 Hope this helps, On Thu, Jan 01, 2004 at 04:10:56PM +0100, Stephane Pinel wrote: > Happy new year to all pgsql fans ! > > Sorry for this trivial question but I couldn't find an answer in the > archives : > > I use SERIAL type in tables for id columns in order to auto increment > them. Is there a way to get > the last inserted id in the table like we do with MySQL using the > GetLastInsertID ? > > My need is to get back the new id just after inserting. Is nextval can > respond to this need if I call it > just before inserting ? > > Thank you very much. > > Stéphane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > (... have gone from d-i being barely usable even by its developers > anywhere, to being about 20% done. Sweet. And the last 80% usually takes > 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce
Attachment
You should use curval after inserting and this can be done in one step insert into foo (c1,c2,...) values ( v1, v2...); select curval( sequence ); Dave On Thu, 2004-01-01 at 10:10, Stephane Pinel wrote: > Happy new year to all pgsql fans ! > > Sorry for this trivial question but I couldn't find an answer in the > archives : > > I use SERIAL type in tables for id columns in order to auto increment > them. Is there a way to get > the last inserted id in the table like we do with MySQL using the > GetLastInsertID ? > > My need is to get back the new id just after inserting. Is nextval can > respond to this need if I call it > just before inserting ? > > Thank you very much. > > Stéphane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Dave Cramer 519 939 0336 ICQ # 1467551
On Thu, 2004-01-01 at 15:10, Stephane Pinel wrote: > Happy new year to all pgsql fans ! > > Sorry for this trivial question but I couldn't find an answer in the > archives : > > I use SERIAL type in tables for id columns in order to auto increment > them. Is there a way to get > the last inserted id in the table like we do with MySQL using the > GetLastInsertID ? > > My need is to get back the new id just after inserting. Is nextval can > respond to this need if I call it > just before inserting ? You can use currval() after the insert (specifying DEFAULT for the SERIAL field), or nextval() to get an id to use in the insert. -- 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 ======================================== "The LORD shall preserve thy going out and thy coming in from this time forth, and even for evermore." Psalms 121:8
> > the last inserted id in the table like we do with MySQL using the > > GetLastInsertID ? > > > > My need is to get back the new id just after inserting. > > Use currval() after you have inserted. That will not necessarily tell you the last insertion, depending upon how many other users there are doing inserts. For that matter, I'm not sure that the MySQL method works with multiple users either. -- Mike Nolan
Le 1 janv. 04, à 22:37, Mike Nolan a écrit : > That will not necessarily tell you the last insertion, depending > upon how many other users there are doing inserts. > > For that matter, I'm not sure that the MySQL method works with multiple > users either. > Exactly. MySQl GetLastInsertI works the same way. But this way is sufficient for my needs. It works like I expected. Thank you all. Stéphane
Yes, it will curval is the curval for *this* connection, not the current value for the sequence! Dave On Thu, 2004-01-01 at 16:37, Mike Nolan wrote: > > > the last inserted id in the table like we do with MySQL using the > > > GetLastInsertID ? > > > > > > My need is to get back the new id just after inserting. > > > > Use currval() after you have inserted. > > That will not necessarily tell you the last insertion, depending > upon how many other users there are doing inserts. > > For that matter, I'm not sure that the MySQL method works with multiple > users either. > -- > Mike Nolan > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Dave Cramer 519 939 0336 ICQ # 1467551
On Thu, Jan 01, 2004 at 03:37:46PM -0600, Mike Nolan wrote: > > Use currval() after you have inserted. > > That will not necessarily tell you the last insertion, depending > upon how many other users there are doing inserts. Where do you get that impression? currval() refers to the last ID for the session you are in. It's also FAQ question 4.15.3. Even better, if you havn't used nextval() in your current session, currval() returns an error, so you can't even get it wrong by accident. http://www.postgresql.org/docs/faqs/FAQ.html#4.15.3 Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > (... have gone from d-i being barely usable even by its developers > anywhere, to being about 20% done. Sweet. And the last 80% usually takes > 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce
Attachment
El Jue 01 Ene 2004 18:51, Dave Cramer escribió:
> Yes, it will
>
> curval is the curval for *this* connection, not the current value for
> the sequence!
Be carefull, it's "currval()" and not "curval()" (see the 2 'r').
--
19:30:01 up 37 days, 1:46, 2 users, load average: 1.45, 0.94, 0.70
-----------------------------------------------------------------
Martín Marqués | select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica | DBA, Programador, Administrador
Universidad Nacional
del Litoral
-----------------------------------------------------------------
> Where do you get that impression? currval() refers to the last ID for the > session you are in. It's also FAQ question 4.15.3. Even better, if you > havn't used nextval() in your current session, currval() returns an error, > so you can't even get it wrong by accident. I stand corrected. I was doing some testing of a PHP module that is called from a web form a while back and got inconsistent results with currval, I probably had a script error of some kind. -- Mike Nolan
Mike Nolan <nolan@gw.tssi.com> writes:
>> Where do you get that impression? currval() refers to the last ID for the
>> session you are in. It's also FAQ question 4.15.3. Even better, if you
>> havn't used nextval() in your current session, currval() returns an error,
>> so you can't even get it wrong by accident.
> I stand corrected. I was doing some testing of a PHP module that is
> called from a web form a while back and got inconsistent results with
> currval, I probably had a script error of some kind.
If you are using persistent connections in PHP you have to be very
careful, because independent bits of PHP script may re-use the same
database connection, and thereby will see a common currval value.
After doing a nextval, you have to be sure to fetch currval before
releasing the connection back to the pool.
regards, tom lane
> If you are using persistent connections in PHP you have to be very > careful, because independent bits of PHP script may re-use the same > database connection, and thereby will see a common currval value. > After doing a nextval, you have to be sure to fetch currval before > releasing the connection back to the pool. I don't think I was using persistent connections at the time, as I have security concerns about them. It sound to me like there's the potential for a second web program sharing the persistent connection to do something in between the nextval and the currval. Is currval for each process maintained at the front end (e.g., the PHP or psql connection) or at the back end? If the latter, isn't there always the potential for memory overflows or other high-demand situations causing the pairing of nextval/currval to get corrupted or lost, in which case RELYING upon it for the last key value could be a source of problems. -- Mike Nolan
Mike Nolan <nolan@gw.tssi.com> writes: > It sound to me like there's the potential for a second web program sharing > the persistent connection to do something in between the nextval and > the currval. "Don't do that then." Your code should hang on to the same connection for as long as it needs to rely on the correct value of currval(). > Is currval for each process maintained at the front end (e.g., the PHP or > psql connection) or at the back end? If the latter, isn't there always > the potential for memory overflows or other high-demand situations causing > the pairing of nextval/currval to get corrupted or lost, in which case > RELYING upon it for the last key value could be a source of problems. How, exactly, would this happen? Is this worry based on an analysis of the source code, or just speculation? -Doug
> How, exactly, would this happen? Is this worry based on an analysis > of the source code, or just speculation? No, I haven't examined that part of the PG source code. However, I've beta tested software for several decades, and I'm wary of any promises like those proferred for nextval/currval. Besides, Tom has already pointed out one flaw in it, involving persistent connections. (And I could easily see how in a large project team the person writing the nextval/currval code might not know whether or not the connection was persistent.) Could there be others? I'm not willing to bet my application's consistency and data integrity against it. Assuming that there aren't risks or problems with accepted techniques is how most large software projects create flaws. If hackers have done anything positive for software development, it is that they have demonstrated that nearly all memory-based schemes can have overflow problems. -- Mike Nolan
On Sun, Jan 04, 2004 at 05:41:37PM -0600, Mike Nolan wrote: > > How, exactly, would this happen? Is this worry based on an analysis > > of the source code, or just speculation? > > No, I haven't examined that part of the PG source code. However, I've > beta tested software for several decades, and I'm wary of any promises > like those proferred for nextval/currval. Besides, Tom has already pointed > out one flaw in it, involving persistent connections. (And I could > easily see how in a large project team the person writing the nextval/currval > code might not know whether or not the connection was persistent.) If you can't guarentee which connection you're using, you can't use transactions at all. If you can't guarentee that then currval/nextval is the least of your problems. You must hold a connection exclusively if you want to guarentee anything. If PHP can't do that, don't use it but use something you can rely on (like non-persistant connections). > Could there be others? I'm not willing to bet my application's consistency > and data integrity against it. Assuming that there aren't risks or > problems with accepted techniques is how most large software projects > create flaws. We rely on it all the time, but we guarentee each process gets its own connection so it's fine. currval/nextval is maintained by the server. If the backend dies, the transaction rolls back and everything is still consistant. If the frontend dies, the transaction rolls back too. > If hackers have done anything positive for software development, it is > that they have demonstrated that nearly all memory-based schemes can > have overflow problems. Again, if the backend dies, the transaction rolls back and you're fine. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > (... have gone from d-i being barely usable even by its developers > anywhere, to being about 20% done. Sweet. And the last 80% usually takes > 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce
Attachment
Doug McNaught <doug@mcnaught.org> writes:
> Mike Nolan <nolan@gw.tssi.com> writes:
>> Is currval for each process maintained at the front end (e.g., the PHP or
>> psql connection) or at the back end?
The latter.
>> If the latter, isn't there always
>> the potential for memory overflows or other high-demand situations causing
>> the pairing of nextval/currval to get corrupted or lost, in which case
>> RELYING upon it for the last key value could be a source of problems.
> How, exactly, would this happen? Is this worry based on an analysis
> of the source code, or just speculation?
Perhaps more to the point, what in the world makes you think that
storage at the front end would be safer? The backend we have some
control over.
regards, tom lane