Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » PostgreSQL GetInsertedId()
PostgreSQL GetInsertedId() [message #16618] Wed, 02 July 2008 16:34 Go to next message
captainc is currently offline  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 #16619 is a reply to message #16618] Wed, 02 July 2008 16:46 Go to previous messageGo to next message
captainc is currently offline  captainc
Messages: 278
Registered: December 2006
Location: New Jersey, USA
Experienced Member
Idea 2: Have a mapping of sequences to table names from the .sch file.

Something like:
String seq = m_session.GetPKSequence(TABLENAME)

and we could use it like this:

...insert statement...
sql.GetInsertedId(seq)

and/or

sql.GetNextId(seq)
...insert statement...

Idea 3: Create a separate insert function called InsertSelect which will return 0 (or -1) for a failed insert and otherwise return 1..N which would be the last inserted id using the method described in the above post. So this would mean we would have 1 insert with no overhead and 1 with the overhead of getting the inserted id and returning it.
Re: PostgreSQL GetInsertedId() [message #16623 is a reply to message #16619] Wed, 02 July 2008 23:17 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
Well, I know that the difference between RDBMS is disturbing, but I think neither solution is good enough.

In reality, this seems to be much more problem in theory. In practice, in most cases, you are designing the application for the specific database anyway.

Mirek
Re: PostgreSQL GetInsertedId() [message #16627 is a reply to message #16623] Thu, 03 July 2008 13:48 Go to previous messageGo to next message
captainc is currently offline  captainc
Messages: 278
Registered: December 2006
Location: New Jersey, USA
Experienced Member
Well we have it in there, but it doesn't do anything, so something has to change.
Re: PostgreSQL GetInsertedId() [message #16636 is a reply to message #16627] Fri, 04 July 2008 13:49 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
captainc wrote on Thu, 03 July 2008 07:48

Well we have it in there, but it doesn't do anything, so something has to change.


It is there because it represent the only way how to manage this problem in MySQL and other "autoincrement" databases without sequences.

OTOH, in Oracle, this is nearly impossible.

Mirek
Re: PostgreSQL GetInsertedId() [message #16686 is a reply to message #16636] Mon, 07 July 2008 19:45 Go to previous messageGo to next message
captainc is currently offline  captainc
Messages: 278
Registered: December 2006
Location: New Jersey, USA
Experienced Member
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.
Re: PostgreSQL GetInsertedId() [message #16690 is a reply to message #16686] Mon, 07 July 2008 22:15 Go to previous messageGo to next message
unodgs is currently offline  unodgs
Messages: 1366
Registered: November 2005
Location: Poland
Ultimate Contributor

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 #16692 is a reply to message #16690] Mon, 07 July 2008 22:17 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
unodgs wrote on Mon, 07 July 2008 16: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.


Does not help, MySQL does not have sequences.

Frankly, this is troublesome problem with many dead ends...

Mirek
Re: PostgreSQL GetInsertedId() [message #16696 is a reply to message #16692] Mon, 07 July 2008 22:46 Go to previous messageGo to next message
unodgs is currently offline  unodgs
Messages: 1366
Registered: November 2005
Location: Poland
Ultimate Contributor

Quote:


Does not help, MySQL does not have sequences.
Frankly, this is troublesome problem with many dead ends...


I said add not replace Smile :
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 #16697 is a reply to message #16692] Mon, 07 July 2008 22:48 Go to previous messageGo to next message
zsolt is currently offline  zsolt
Messages: 693
Registered: December 2005
Location: Budapest, Hungary
Contributor
I think, GetInsertedId(const char* seqence = 0) would be useful.
MySql and SQLite classes would simply neglect this parameter, PostrgreSQL would use parameter as a sequence name to return "SELECT currval('%s')".

This way we could create portable database software.
Re: PostgreSQL GetInsertedId() [message #16698 is a reply to message #16696] Mon, 07 July 2008 22:49 Go to previous messageGo to next message
zsolt is currently offline  zsolt
Messages: 693
Registered: December 2005
Location: Budapest, Hungary
Contributor
Oops, Uno, you were faster Smile
Re: PostgreSQL GetInsertedId() [message #16705 is a reply to message #16698] Tue, 08 July 2008 07:22 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
I am not sure what problem this should solve....

For Oracle, PGSQL the insert looks like: Get a new primary key from the SqlSequence, insert the record with this key. (You can use SqlSequence or PgSequence to get the int).

For MySQL it looks like: Insert a record, get a new primary key using GetInsertedId....

Where does fit GetInsertedId with param?

Mirek
Re: PostgreSQL GetInsertedId() [message #16707 is a reply to message #16705] Tue, 08 July 2008 08:55 Go to previous messageGo to next message
zsolt is currently offline  zsolt
Messages: 693
Registered: December 2005
Location: Budapest, Hungary
Contributor
Yes, but many people are using SERIAL column in PostgreSQL.

Allowing an additional parameter in GetInsertedId, developer would be able to write exactly the same code for PostgreSQL, SQLite and MySql.

I don't know too much about current Oracle features, but I think, there can be some similar solution to "auto incremented" fields.
Re: PostgreSQL GetInsertedId() [message #16720 is a reply to message #16697] Tue, 08 July 2008 15:57 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
zsolt wrote on Mon, 07 July 2008 16:48

I think, GetInsertedId(const char* seqence = 0) would be useful.
MySql and SQLite classes would simply neglect this parameter, PostrgreSQL would use parameter as a sequence name to return "SELECT currval('%s')".

This way we could create portable database software.


Well, I sort of see your argument now, but this is fundamentally broken w.r.t concurency.

What if other client inserts a record between your INSERT and SELECT?

Mirek
Re: PostgreSQL GetInsertedId() [message #16723 is a reply to message #16720] Tue, 08 July 2008 16:56 Go to previous messageGo to next message
zsolt is currently offline  zsolt
Messages: 693
Registered: December 2005
Location: Budapest, Hungary
Contributor
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".
Re: PostgreSQL GetInsertedId() [message #16734 is a reply to message #16723] Tue, 08 July 2008 21:58 Go to previous message
captainc is currently offline  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.
Previous Topic: Quick explanation of function calls for postgresql
Next Topic: db access to MS ACCESS
Goto Forum:
  


Current Time: Thu Mar 28 16:26:54 CET 2024

Total time taken to generate the page: 0.01490 seconds