<div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">From: Peter J Halls <<a href="mailto:P.Halls@york.ac.uk">P.Halls@york.ac.uk</a>><br>
Subject: Re: [gdal-dev] Coding for appending to a database in OGR<br>
To: Frank Warmerdam <<a href="mailto:warmerdam@pobox.com">warmerdam@pobox.com</a>><br>
Cc: <a href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a><br>
Message-ID: <<a href="mailto:4CCAD1DF.60301@york.ac.uk">4CCAD1DF.60301@york.ac.uk</a>><br>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed<br>
<br>
Thanks, Frank ...<br>
<br>
Frank Warmerdam wrote:<br>
> Peter J Halls wrote:<br>
>> Frank and team,<br>
>><br>
>> having reviewed the code again and the comments made by others, for<br>
>> which thanks, it seems to me that there is a possible solution that<br>
>> might resolve all the concerns. I've asked my DBA for performance<br>
>> expectation comments, but have not yet heard back from him.<br>
>><br>
>> Instead of trying to track the current FID and where the caller has<br>
>> not specified a value 'SELECT max(OGR_FID) + 1 FROM <tablename>'<br>
>> could be used instead. This will always return the maximum value plus<br>
>> one and consigns the work to Oracle. This will be 'safe' for multiple<br>
>> updaters and saves the overhead / risk of enquiring the value and<br>
>> updating it in the code.<br>
>><br>
>> However, one side effect of this is that the value is not readily<br>
>> known to OGR. As this is only when writing a new feature to the<br>
>> database, does this matter? Is there any reliance on the FID within a<br>
>> newly created / written feature having the destination FID value on<br>
>> return - I suspect that, in the majority of instances, the next call<br>
>> is to destroy the feature handle? As this would be a change in<br>
>> behaviour, at least it will be if I am correct that only the<br>
>> BoundCreateFeature method is actually used, then this may be significant.<br>
>><br>
>> How do others regard this? Is it a big issue or a non-issue?<br>
><br>
> Peter,<br>
><br>
> I am quite concerned about the performance impact of doing the MAX()<br>
> operation for every feature insert. I also would consider it a major<br>
> disadvantage to not return the FID properly set on the feature.<br>
<br>
Now I've talked it over with my DBA, my view is that a Sequence would give<br>
better performance. Use of NextVal from the Sequence will return a unique value<br>
for the FID, which can then be written back to the feature as well as to the<br>
dbms and maintain the unique values - provided all updaters use the same Sequence.<br>
<br>
We worked out that setting the Primary Key or Unique constraint on the column<br>
would prevent multiple updaters using the same FID value: the lack of this<br>
constraint is probably a separate issue. The constraint would also be essential<br>
to prevent multiple updaters using different methods of obtaining a value for<br>
the FID as even the Sequence could then get out of step.<br>
<br>
However, to support multiple updaters using different methods would require that<br>
the OGR insert code be defensive and expect to fail (through violation of the<br>
constraint) and be prepared to try new values until success is achieved. We<br>
think that this overhead might exceed the use of SELECT max(OGR_FID) + 1.<br>
<br>
This then comes back to the question of providing support for multiple updaters.<br>
<br>
Best wishes,<br>
<br>
Peter<br></blockquote><div><br></div><div>Hello Peter,</div><div><br></div><div>Actually, unless I am missing something very obvious, the select max()+1 approach would not work since there are race-condition-like situations where one of the statements from one of the connections would fail. Why not just used the underlying database's AUTOINCREMENT features? Most dbs support that and I think in Oracle it is done with a sequence combined with a trigger or specifying the sequence.nextval() in the insert statement itself.</div>
<div><br></div><div>Just to point out, ArcObjects has the concept of a RowBuffer - a row that is used for insertion that behaves just like regular row except that it doesn't have identity (the ID hasn't been assigned yet) until after it has been inserted (after db insertion, the id is looked up and assigned to the client object).</div>
<div><br></div><div>my two cents,</div><div><br></div><div>- Ragi</div></div>