[Gdal-dev] Improving postgresql performance by using COPY

[Frank Warmerdam]

On 10/14/05, Charlie Savage <cfis at interserv.com> wrote:
> 1.  Start/End Copy
>
> You have to tell Postgresql you are running a copy command before you
> start, then you copy each feature, and then you tell Postgresql that you
> are done.  Right now, the ogr2ogr translate feature doesn't provide
> hooks for doing that.  ogr2ogr does do something similar though - it
> call startransaction / endtransaction from the translate method when
> starting / ending a data load.
>
> What I did for my prototype was implement two new methods onOGRLayer -
> StartTranslation and EndTranslation.  In the general case they do
> nothing, in the Postgresql they setup the COPY commands if a user has
> requested that (see discussion below).

Charlie,

How important do you think it is to be able to use COPY mode to
add features to an existing table, as opposed to just using it to create
a new table?   I ask because in the Oracle world when I ran into
similar issues I used a "layer creation option" to control the loading
mode.

My preference would be to do the same for this change, as it requires
substantially less disruption to the OGR API as a whole.  The downside
is there is no way of controlling load-mode when adding features to
an existing table.

> 2.  Transactions
>
> COPY runs in one big transaction.  While running COPY you cannot run
> other SQL commands (like begin/commit).  However, OGR automatically
> commits every 200 transactions unless you specify otherwise using the
> undocumented ogr2ogr -tg parameter and set it to -1.  However, that's
> obviously an ugly hack.

As long as COPY is not the default load mode (I think INSERT should
remain the default for CreateFeature()) then I don't mind that there are
special restrictions on transactions while COPY loading features.

> 3.  EWKB PostGIS Format
>
> The last issue is that loading data via COPY requires putting geometry
> in the HEX encoded EWKB format that PostGis uses.  Unfortunately that
> EWKB format is different than what OGR uses.  The PostGIS format
> includes support for embedding a SRS and a  M Value.  Thus I had to
> create a OGR EWKB format, then pick it apart to add the SRS, then HEX
> encode it.  All doable, but it would be nice if OGR and PostGIS were in
> line with each other.  Any chance of that happening?

I am considering adding EWKB format on the OGRGeometryFactory
and related classes at some point for another client.    In the meantime
your EWKB method should be fine.

So, would you be prepared to rework your patch to just use layer
creation options to put things into the special COPY mode?   If so,
you can go ahead and commit the changes.   If not, we can discuss
the StartTranslation() / EndTranslation() and other options further.

Best regards,
--
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent