Re: Return key from query - Mailing list pgsql-general

On 11/03/2010 02:08 AM, Szymon Guz wrote:
>
>
> On 3 November 2010 00:41, Rob Sargent <robjsargent@gmail.com
> <mailto:robjsargent@gmail.com>> wrote:
>
>
>
>     On 11/02/2010 03:03 PM, Szymon Guz wrote:
>     >
>     >
>     > On 2 November 2010 21:59, Rob Sargent <robjsargent@gmail.com
>     <mailto:robjsargent@gmail.com>
>     > <mailto:robjsargent@gmail.com <mailto:robjsargent@gmail.com>>> wrote:
>     >
>     >
>     >
>     >     On 11/02/2010 02:43 PM, Jonathan Tripathy wrote:
>     >     > Hi everyone,
>     >     >
>     >     > When adding a new record, we run an insert query which
>     auto-increments
>     >     > the primary key for the table. However the method (in java)
>     which
>     >     calls
>     >     > this query must return the newly created key.
>     >     >
>     >     > Any ideas on how to do this, preferably using a single
>     transaction?
>     >     >
>     >     > Thanks
>     >     >
>     >
>     >     Ah yes have your cake and eat it too.
>     >
>     >     If you app code (java) is making new instances and wants to
>     have an id,
>     >     then the table cannot have an auto-id.
>     >
>     >
>     > why?
>
>     Well I admit you could have a set-up wherein you retrieve an id-only
>     record from the db as part of the java constuctor but keeping that tx
>     open while the app decides whether or not it will "save" the record seem
>     a nightmare to me
>
>
> Sorry, I don't get it. I usually have an application that knows if it
> wants to write some data to database, or not. So it writes the data, and
> just gets from database the id that was set by database. No need of
> getting the id earlier in a transaction, although the simple insert that
> saves the data runs in a transaction of course.
> Another approach could be just getting the id from database, and saving
> the data using that id. If someone puts there any complicated logic
> between getting id and saving data, it is just a very bad software
> design, that has nothing common with the id/uuid problem.
>


If the client application wants to generate new instances and manipulate
them in lists and so forth, having the id before the instances actually
persist is really handy.  I'm of the opinion that the application makes
the instances (completely, with ids and all) not the database.


>
>     >
>     >
>     >     I'm big on java/class id generation (usually use UUID) but you
>     do have
>     >     to take complete responsibility for it.  The hibernate folks
>     frown on it
>     >     but their retort is imho woeful.  If you're not using uuid's
>     you will
>     >     need a generator.  You can back it up with a persistent store
>     so you
>     >     know where you left off, but you will want to (auto-)increment
>     that id
>     >     table with a large value (say 5000) and have you generate dole
>     them out
>     >     as needed at the app level.  When it has spent 5000 ids, it
>     will go to
>     >     the server are ask for another 5000 (separate tx).  Please
>     don't get
>     >     hung up on loosing some portion of the 5000 id when you restart or
>     >     whatever.
>     >
>     >
>     > I'm not fan of UUID, though I have to use it in many projects...
>     nothing
>     > special, just another id, not better, not worse than id, maybe except
>     > for the chance of collisions :)
>     >
>     http://en.wikipedia.org/wiki/Universally_unique_identifier#Random_UUID_probability_of_duplicates
>
>
> Yes... indeed, that all depends on the method of generating UUID, and
> the number of concurrent UUID numbers you want to generate. In the
> systems that I use, sometimes there is a really big number of new data
> per second, duplicates can occur, but I could just throw away the data
> with duplicated ids, something that I cannot do e.g. in a bank system.
>
>
>     I'll take those odds over a monotonically increasing id with concomitant
>     index rebuilds.
>
>
> If you have index on the UUID field (which of course you have, I
> suppose), and the index is sorted in the file, than you have a really
> great bloat in the index file. I will have to check that out how big,
> but I suppose UUIDs could be much slower due to that.
>
> Could someone enlighten me if this occurs?

It's only two words wide on a 64bit machine.
>
>
>
> regards
> Szymon


PS. I think you may have sent your reply to me only.