|
|
|
Home » U++ Library support » U++ SQL » PostgreSQL GetInsertedId()
| 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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: PostgreSQL GetInsertedId() [message #16690 is a reply to message #16686] |
Mon, 07 July 2008 22:15  |
|
|
| captainc wrote on Mon, 07 July 2008 13:45 |
I understand, but from the perspective of a new Upp user who sees this GetInsertedId() function while using the PostgreSQL package (with assist++ or however) and ends up smacking his head against a wall trying to get it to work correctly, when it is only there as a placeholder and always returns Null... It's just not nice.
|
I think we need to add GetInsertedId(const char* seqence) to session interface. Of course one can use currval from sqlexp but I prefer getting id through session object. If I won't find any better solution I will add this new GetInsertedId version.
|
|
|
|
|
|
| Re: PostgreSQL GetInsertedId() [message #16696 is a reply to message #16692] |
Mon, 07 July 2008 22:46 |
|
|
| Quote: |
Does not help, MySQL does not have sequences.
Frankly, this is troublesome problem with many dead ends...
|
I said add not replace  :
int GetInsertedId() - for database with autoincrement fields
int GetInsertedId(const char* s) - for database with sequences
or just current GetInsertedId could be replaced with
int GetInsertedId(const char* s = NULL)
if sequence name is null it returns last valid inserted id (of course for databases which supports this way).
This do not solve assist problem - you have to know what getinsertedid to use for your database.
[Updated on: Mon, 07 July 2008 22:48] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: PostgreSQL GetInsertedId() [message #16734 is a reply to message #16723] |
Tue, 08 July 2008 21:58 |
captainc
Messages: 278
Registered: December 2006
Location: New Jersey, USA
|
Experienced Member |
|
|
| zsolt wrote on Tue, 08 July 2008 10:56 |
| luzr wrote on Tue, 08 July 2008 15:57 |
What if other client inserts a record between your INSERT and SELECT?
|
In PostgreSQL, curval returns the value after last "nextval" in current session.
If some other clients call "nextval" after yours, you will see your original value got from your last "nextval".
|
Also, curval and nextval are session specific (session safe). So unless you inserted another item in your session, you will get back the id expected. Other sessions will not interfere with this even if they add information to the database in between.
|
|
|
|
Goto Forum:
Current Time: Tue Apr 29 14:57:37 CEST 2025
Total time taken to generate the page: 0.03953 seconds
|
|
|
Members
Pages
Help
Register
Login
Home
