[gdal-dev] Coding for appending to a database in OGR

[Peter J Halls]

Thanks, Frank ...

Frank Warmerdam wrote:
> Peter J Halls wrote:
>> Frank and team,
>>
>>    having reviewed the code again and the comments made by others, for 
>> which thanks, it seems to me that there is a possible solution that 
>> might resolve all the concerns.  I've asked my DBA for performance 
>> expectation comments, but have not yet heard back from him.
>>
>>    Instead of trying to track the current FID and where the caller has 
>> not specified a value  'SELECT max(OGR_FID) + 1 FROM <tablename>' 
>> could be used instead.  This will always return the maximum value plus 
>> one and consigns the work to Oracle.  This will be 'safe' for multiple 
>> updaters and saves the overhead / risk of enquiring the value and 
>> updating it in the code.
>>
>>    However, one side effect of this is that the value is not readily 
>> known to OGR.  As this is only when writing a new feature to the 
>> database, does this matter?  Is there any reliance on the FID within a 
>> newly created / written feature having the destination FID value on 
>> return - I suspect that, in the majority of instances, the next call 
>> is to destroy the feature handle?  As this would be a change in 
>> behaviour, at least it will be if I am correct that only the 
>> BoundCreateFeature method is actually used, then this may be significant.
>>
>> How do others regard this?  Is it a big issue or a non-issue?
> 
> Peter,
> 
> I am quite concerned about the performance impact of doing the MAX()
> operation for every feature insert.   I also would consider it a major
> disadvantage to not return the FID properly set on the feature.

Now I've talked it over with my DBA, my view is that a Sequence would give 
better performance.  Use of NextVal from the Sequence will return a unique value 
for the FID, which can then be written back to the feature as well as to the 
dbms and maintain the unique values - provided all updaters use the same Sequence.

We worked out that setting the Primary Key or Unique constraint on the column 
would prevent multiple updaters using the same FID value: the lack of this 
constraint is probably a separate issue.  The constraint would also be essential 
to prevent multiple updaters using different methods of obtaining a value for 
the FID as even the Sequence could then get out of step.

However, to support multiple updaters using different methods would require that 
the OGR insert code be defensive and expect to fail (through violation of the 
constraint) and be prepared to try new values until success is achieved.  We 
think that this overhead might exceed the use of SELECT max(OGR_FID) + 1.

This then comes back to the question of providing support for multiple updaters.

Best wishes,

Peter

--------------------------------------------------------------------------------
Peter J Halls, GIS Advisor, University of York
Telephone: 01904 323806     Fax: 01904 323740
Snail mail: IT Services, University of York, Heslington, York YO10 5DD
This message has the status of a private and personal communication
--------------------------------------------------------------------------------