U++ forum: Welcome to the forum

PostgreSQL GetInsertedId() [message #16618]

Wed, 02 July 2008 16:34

captainc
Messages: 278
Registered: December 2006
Location: New Jersey, USA

Experienced Member

I noticed the problem with PostgreSQL's GetInsertedId(). I found this post on the web:

Quote:

I would always suggest that people do a select nextval('seq') followed by a suitable insert. It requires no locking, is definitely multi user safe and will *always* correctly identify the row. The only downside is that it requires a quick select before the insert, but if this minor speed sacrifice was that much of an issue"

The definition of nextval() from postgres site:
"Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value."
This means that you would have to use the value returned in the insert statement.
Also, currval() should be session-safe, and is probably easier to use.

Like the mysql package, maybe we can use a 'lastid' variable and update the id for each insert statement made.

Otherwise, we might need a 'lastsequence' variable and a String GetLastSequence() function, and as the source comments suggest, a GetInsertedId(const char* sequence) function to go along with it.
Then we could make the call sqlGetInsertedId(sql.GetLastSequence()).

Thoughts?

[Updated on: Wed, 02 July 2008 17:02]

Report message to a moderator