Skip site navigation (1) Skip section navigation (2)

Re: Retrieving last InsertedID : INSERT... RETURNING safe ?

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
Cc: "Paul Tomblin" <ptomblin(at)gmail(dot)com>, <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Retrieving last InsertedID : INSERT... RETURNING safe ?
Date: 2008-02-20 13:32:42
Message-ID: DB6FA671-F2D8-4F53-B621-43560A2DA0CF@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Heikki


On 20-Feb-08, at 8:14 AM, Heikki Linnakangas wrote:

> Dave Cramer wrote:
>> On 20-Feb-08, at 7:19 AM, Paul Tomblin wrote:
>>> Dave Cramer wrote:
>>>>> Well, that other solution is dangerous in case multiple inserts
>>>>> to that table are done concurrently; a quite common usage pattern
>>>>> with java web applications handling multiple HTTP requests with
>>>>> concurrent java threads..
>>>>>
>>>> No it is not dangerous. It is the right way to do it. There is  
>>>> absolutely no danger in using currval in this manner.
>>>
>>> Unless you have autocommit on.
>>>
>> I was going to say there are absolutely no situations where this is  
>> not true, however in your case autocommit or not it doesn't matter.
>> You have a single connection for the entire application and  
>> asynchronous events using that connection. Autocommit or not it  
>> will not work with currval.
>> In your case you must use nextval before doing the insert.
>
> Now you lost me. By asynchronous events, do you mean NOTIFY/LISTEN?  
> What exactly is the scenario you're talking about?
>
> One problematic scenario for nextval+currval is an INSERT trigger  
> that calls nextval() behind your back, but you can fool any method  
> with a trigger if you really want to.
>
As far as I can tall Paul has inherited an application which uses a  
single connection for all database operations, and is a swing app  
which has callbacks which do the following

Callback code

	grab the global connection object
	create a statement
	do something	
	close statement

in this scenario, since currval has connection scope if two callbacks  
are called at the same time, only one will have the right answer .

Paul am I correct in my assumptions above ?


Dave
>

In response to

Responses

pgsql-jdbc by date

Next:From: Paul TomblinDate: 2008-02-20 13:36:32
Subject: Re: Retrieving last InsertedID : INSERT... RETURNING safe ?
Previous:From: Heikki LinnakangasDate: 2008-02-20 13:14:00
Subject: Re: Retrieving last InsertedID : INSERT... RETURNING safe ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group