GetLastInsertID ?

[Stephane Pinel]

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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Dennis Bjorklund]

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/curre...-sequence.html

--
/Dennis
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Martijn van Oosterhout]

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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
--
Martijn van Oosterhout <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/9EdzY5Twig3Ge+YRAuG6AJ4iMN3RQW0pjFzDNxmjyqxCbGr3xA Cg1tm0
o7j0V61Oi1uCan0LzRxn3RY=
=O3tr
-----END PGP SIGNATURE-----

[Dave Cramer]

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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

--
Dave Cramer
519 939 0336
ICQ # 1467551
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Oliver Elphick]

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 Ol************@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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Mike Nolan]

> > 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

[Stephane Pinel]

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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Dave Cramer]

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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Martijn van Oosterhout]

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 <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/9J1YY5Twig3Ge+YRAtwtAKDXKLxvDXLsuEGv/Su0MjdYfQvnwwCfeaLL
MaPu4rxniyhHW794by6JfJM=
=diwh
-----END PGP SIGNATURE-----

[Martin Marques]

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
-----------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Mike Nolan]

> 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Mike Nolan <no***@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Mike Nolan]

> 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Doug McNaught]

Mike Nolan <no***@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Mike Nolan]

> 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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Martijn van Oosterhout]

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 <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/+KfoY5Twig3Ge+YRAkaoAJ9it34snjtnWWEhY3pc1aCrbDchxA Cgqp+X
uuxJnbSQEzkcaKb3MaqFbPI=
=k1bI
-----END PGP SIGNATURE-----

[Tom Lane]

Doug McNaught <do**@mcnaught.org> writes:

Mike Nolan <no***@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings