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

[Ragi Burhum]

>
> From: Peter J Halls <P.Halls at york.ac.uk>
> Subject: Re: [gdal-dev] Coding for appending to a database in OGR
> To: Frank Warmerdam <warmerdam at pobox.com>
> Cc: gdal-dev at lists.osgeo.org
> Message-ID: <4CCAD1DF.60301 at york.ac.uk>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> 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
>

Hello Peter,

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.

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).

my two cents,

- Ragi
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/gdal-dev/attachments/20101029/06db9cad/attachment.html